一、前言
死锁,其实是一个很有意思也很有挑战的技术问题,大概每个 DBA 和部分开发同学都会在工作过程中遇见 。关于死锁我会持续写一个系列的案例分析,希望能够对想了解死锁的朋友有所帮助。
二、案例分析
2.1 业务场景
业务开发同学想同步数据,他们的逻辑是通过 update 更新操作,如果更新记录返回的 affect_rows为0,然后就调用 insert 语句进行插入初始化。如果插入失败则再进行更新操作,多个会话并发操作的情况下就出现死锁。
2.2 环境说明
MySQL 5.6.24 事务隔离级别为 RR
-
create table ty (
-
id int not null primary key auto_increment ,
-
c1 int not null default 0,
-
c2 int not null default 0,
-
c3 int not null default 0,
-
unique key uc1(c1),
-
unique key uc2(c2)
-
) engine=innodb ;
-
insert into ty(c1,c2,c3)
-
values(1,3,4),(6,6,10),(9,9,14);
2.3 测试用例
![](https://img.laitimes.com/img/_0nNw4CM6IyYiwiM6ICdiwiI0gTMx81dsQWZ4lmZf1GLlpXazVmcvwFciV2dsQXYtJ3bm9CX9s2RkBnVHFmb1clWvB3MaVnRtp1XlBXe0xCMy81dvRWYoNHLwEzX5xCMx8FesU2cfdGLwMzX0xiRGZkRGZ0Xy9GbvNGLpZTY1EmMZVDUSFTU4VFRR9Fd4VGdsYTMfVmepNHLrJXYtJXZ0F2dvwVZnFWbp1zczV2YvJHctM3cv1Ce-cGcq5CO2MzN4QGMiFWOiFTYzADOzYzX3IDN0ETM1IzLcFTMyIDMy8CXn9Gbi9CXzV2Zh1WavwVbvNmLvR3YxUjLyM3Lc9CX6MHc0RHaiojIsJye.jpg)
2.4 死锁日志
1. 2018-03-27 17:59:23 0x7f75bf39d700
2. *** (1) TRANSACTION:
3. TRANSACTION 1863, ACTIVE 76 sec inserting
4. mysql tables in use 1, locked 1
5. LOCK WAIT 4 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 1
6. MySQL thread id 382150, OS thread handle 56640, query id 28 localhost root update
7. insert into ty (c1,c2,c3) values(3,4,2)
8. *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
9. RECORD LOCKS space id 28 page no 5 n bits 72 index uc2 of table `test`.`ty` trx id 1863 lock_mode X locks gap before rec insert intention waiting
10. *** (2) TRANSACTION:
11. TRANSACTION 1864, ACTIVE 65 sec inserting, thread declared inside InnoDB 5000
12. mysql tables in use 1, locked 1
13. 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
14. MySQL thread id 382125, OS thread handle 40032, query id 62 localhost root update
15. insert into ty (c1,c2,c3) values(3,4,2)
16. *** (2) HOLDS THE LOCK(S):
17. RECORD LOCKS space id 28 page no 5 n bits 72 index uc2 of table `test`.`ty` trx id 1864 lock_mode X locks gap before rec
18. *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
19. RECORD LOCKS space id 28 page no 4 n bits 72 index uc1 of table `test`.`ty` trx id 1864 lock mode S waiting
20. *** WE ROLL BACK TRANSACTION (2)
2.5 分析死锁日志
首先我们要再次强调 insert 插入操作的加锁逻辑。
第一阶段: 唯一性约束检查,先申请 LOCK_S + LOCK_ORDINARY
第二阶段: 获取阶段一的锁并且 insert 成功之后,插入的位置有 GAP 锁:LOCK_INSERT_INTENTION,为了防止其他 insert 唯一键冲突。
新数据插入完成之后:LOCK_X + LOCK_REC_NOT_GAP
对于 insert 操作来说,若发生唯一约束冲突,则需要对冲突的唯一索引加上 S Next-key Lock。从这里会发现,即使是 RC 事务隔离级别,也同样会存在 Next-Key Lock 锁,从而阻塞并发。然而,文档没有说明的是,对于检测到冲突的唯一索引,等待线程在获得 S Lock 之后,还需要对下一个记录进行加锁,在源码中由函数row_ins_scan_sec_index_for_duplicate 进行判断.
其次 我们需要了解锁的兼容性矩阵。
从兼容性矩阵我们可以得到如下结论:
INSERT 操作之间不会有冲突。
GAP,Next-Key 会阻止 Insert。
GAP 和 Record,Next-Key 不会冲突。
Record 和 Record、Next-Key 之间相互冲突。
已有的 Insert 锁不阻止任何准备加的锁。
已经持有的 GAP 锁会阻塞插入意向锁 INSERT_INTENTION。
另外 对于通过唯一索引更新或者删除不存在的记录,会申请加上 GAP 锁。
分析
了解上面的基础知识,我们开始对死锁日志进行分析:
T1: sess1 通过唯一键更新数据,由于 c2=4 不存在,返回 affect row 为 0,MySQL 会申请(3,6)之间的 GAP 锁。
T2: sess2 的情况和 sess1 类似,也会申请(3,6)之间的 GAP 锁,从上面的兼容性矩阵来看两个 GAP 锁并不会冲突。
T3: sess1 根据 update 语句返回 affect row 为 0,执行 insert 操作,此时需要申请插入意向锁,sess2 会话持有的 GAP 锁和 sess1 申请的插入意向锁冲突,出现等待。
index uc2 of table test.ty trx id 1863 lock_mode X locks gap before rec insert intention waiting
T4:sess2 与 sess1类似,根据 update 语句返回 affect row 为 0,执行 insert 操作。申请的插入意向锁与sess1 的 update 语句持有的 GAP 锁冲突。sess1(持有 GAP 锁),sess2(持有 GAP 锁),sess1(插入意向锁等待 sess2 的 GAP 锁释放) sess2(插入意向锁等待 sess1 的 GAP 锁释放) 构成循环等待,进而导致死锁。
2.6 解决方法
从业务场景的处理逻辑上看,业务需要发送两次请求一次 update,一次 insert 才能完成业务逻辑,不够友好和优化。
其实我们可以和开发同学沟通好,确认业务的幂等性,使用 insert on duplicate key的方式,没有就插入,存在就更新,一次调用即可完成之前 2 次操作的功能,提高性能。