当前位置 博文首页 > A_art_xiang的博客:mysql死锁场景汇总整理
目录
简述
行锁导致死锁
gap lock/next keys lock导致死锁
index merge导致死锁
唯一索引冲突导致死锁
总结
本文死锁场景皆为工作中遇到(或同事遇到)并解决的死锁场景,写这篇文章的目的是整理和分享,欢迎指正和补充,本文死锁场景包括:
注:以下场景隔离级别均为默认的Repeatable Read;
前提:表 t_user 的 uid 字段创建了唯一索引,并拥有可更新字段age。
场景复现:
行锁导致死锁
死锁原因详解:
相应业务案例和解决方案:
该场景常见于事务中存在for循环更新某条记录的情况,死锁日志显示lock_mode X locks rec but not gap waiting
(即行锁而非间隙锁),解决方案:
表结构:
CREATE TABLE `t_user` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`age` int(3) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `udx_age` (`age`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4;
场景复现:
首先查询表中目前存在的记录:
执行两个事务的操作:
死锁原因分析:
lock_mode X locks gap before rec insert intention waiting
解决方案:
t_user结构改造为:
CREATE TABLE `t_user` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`age` int(11) DEFAULT NULL,
`zone_id` bigint(20) DEFAULT NULL,
`username` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_age` (`age`),
KEY `idx_zone_id` (`zone_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
场景复现操作(几率不高):
假设存在以下数据:
id | zone_id | uid | username |
---|---|---|---|
1 | 1 | 1 | "" |
2 | 1 | 2 | "" |
3 | 2 | 1 | "" |
4 | 2 | 2 | "" |
死锁分析:
update t_user where zone_id = 1 and uid = 1
可以发现type是index_merge,即会用到zone_id和uid两个索引事务1:
① 锁住zone_id=1对应的间隙锁: zoneId in (1,2)
② 锁住索引zone_id=1对应的主键索引行锁id = [1,2]
③ 锁住uid=1对应的间隙锁: uid in (1, 2)
④ 锁住uid=1对应的主键索引行锁: id = [1, 3]
事务2:
① 锁住zone_id=2对应的间隙锁: zoneId in (1,2)
② 锁住索引zone_id=2对应的主键索引行锁id = [3,4]
③ 锁住uid=2对应的间隙锁: uid in (1, 2)
④ 锁住uid=2对应的主键索引行锁: id = [2, 4]
lock_mode X locks rec but not gap waiting Record lock
解决方案:创建联合索引,使执行计划只会用到一个索引。
测试表结构:
CREATE TABLE `t_sample` (
`id` bigint(29) NOT NULL AUTO_INCREMENT,
`uid` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `uk_uid` (`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
场景复现操作:
image.png
死锁分析:
select...lock in share mode
),加的锁是GAP S锁,所以进入阻塞,等待事务一释放锁解决办法:尽量避免这种插入又回滚的场景。
避免死锁的原则: