mysql死锁场景整理

2021-03-01 本文已影响0人挡不住的柳Willow

简述

本文死锁场景皆为工作中遇到（或同事遇到）并解决的死锁场景，写这篇文章的目的是整理和分享，欢迎指正和补充，本文死锁场景包括：

行锁导致死锁
gap lock/next keys lock导致死锁
index merge 导致死锁
唯一索引冲突导致死锁

注：以下场景隔离级别均为默认的Repeatable Read；

行锁导致死锁

前提：表 t_user 的 uid 字段创建了唯一索引，并拥有可更新字段age。
场景复现：

行锁导致死锁
死锁原因详解：

两个事务执行过程时间上有交集，并且过程发生在两者提交之前
事务1更新uid=1的记录，事务2更新uid=2的记录，在RR级别，由于uid是唯一索引，因此两个事务将分别持有uid=1和2所在行的独占锁
事务1执行到第二条更新语句时，发现uid=2的行被锁住，进入阻塞等待锁释放；
事务2执行到第二条语句时发现uid=1的行被锁，同样进入阻塞
两个事务互相等待，死锁产生。

相应业务案例和解决方案：
该场景常见于事务中存在for循环更新某条记录的情况，死锁日志显示lock_mode X locks rec but not gap waiting（即行锁而非间隙锁），解决方案：

避免循环更新，优化为一条where锁定要更新的记录批量更新
如果非要循环更新，尝试取消事务（能接受的话），即每一条更新为一个独立的事务

gap lock/next keys lock导致死锁

表结构:

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;

场景复现：
首先查询表中目前存在的记录：

image.png

执行两个事务的操作：

image.png

死锁原因分析：

事务1执行delete age = 27，务2执行delete age = 31，在RR级别，操作条件不是唯一索引时，行锁会升级为next keys lock（可以理解为间隙锁），因此事务1锁住了25到27和27到29的区间，事务2锁住了29到31的区间
事务1执行insert age = 30，等待事务2释放锁
事务2执行insert age = 28，等待事务1释放锁
死锁产生，死锁日志显示lock_mode X locks gap before rec insert intention waiting

解决方案：

降低事务隔离级别到Read Committed，该隔离级别下间隙锁降级为行锁，可以减少死锁发生的概率
避免这种场景- -

index merge导致死锁

t_user结构改造为：

CREATE TABLE `t_user` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `uid` int(11) DEFAULT NULL,
  `zone_id` bigint(20) DEFAULT NULL,
  `username` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_uid` (`uid`),
  KEY `idx_zone_id` (`zone_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

场景复现操作（几率不高）：

image.png

假设存在以下数据：

id	zone_id	uid	username
1	1	1	""
2	1	2	""
3	2	1	""
4	2	2	""

死锁分析：

在符合场景前提的情况下（即表数据量较大，index_merge未关闭），通过explain分析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]

如果两个事务上锁的顺序相反，则有一定的概率出现死锁。另外，index_merge的形式锁住了很多不符合条件的行，浪费了资源。一般死锁日志打印的信息为：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
死锁分析：

三个事务分别尝试插入uid=1的数据，其中事务1先于后两个事务
由于是唯一索引，所以后两个事务会出现唯一键冲突，但是事务1并未立即提交，因此不会报错，而是将事务一insert的隐式锁升级为显式锁
事务二和事务三为了判断是否出现唯一键冲突，必须进行一次当前读（select...lock in share mode)，加的锁是GAP S锁，所以进入阻塞，等待事务一释放锁
事务一回滚，此时事务二和事务三成功获取记录上的GAP S锁，并继续执行插入操作
插入则需要依次请求插入意向锁，而插入意向锁和GAP S锁冲突，因此两个事务相互等待，形成死锁

解决办法：尽量避免这种插入又回滚的场景。

总结

避免死锁的原则：

建立合适的索引，减小锁的粒度
选择合适的事务隔离级别
大事务拆成小事务，一个事务中的锁尽量少

mysql死锁场景整理

简述

行锁导致死锁

gap lock/next keys lock导致死锁

index merge导致死锁

唯一索引冲突导致死锁

总结

猜你喜欢

热点阅读