死锁案例 五

一、前言

死锁其实是一个很有意思也很有挑战的技术问题，大概每个 DBA 和部分开发朋友都会在工作过程中遇见。关于死锁我会持续写一个系列的案例分析，希望能够对想了解死锁的朋友有所帮助。本文是源于生产过程中一个死锁案例。

二、背景知识

官方文档[1]中表述:

"REPLACE is done like an INSERT if there is no collision on a unique key. Otherwise, an exclusive next-key lock is placed on the row to be replaced."

"如果没有唯一键冲突的时候，replace 操作和insert的加锁方式是一样的。但是如果有唯一键冲突的话，replace语句执行时，系统会在记录上加上 LOCK X next-key lock。"

如果觉得上面翻译比较简单，就看看下面的介绍[2]

1. create table t1(

2. a int auto_increment primary key,

3. b int,

4. c int,

5. unique key (b));

6.  

7. replace into t1(b,c) values (2,3)

Step 1 正常的插入逻辑

首先插入聚集索引，在上例中 a 列为自增列，由于未显式指定，每次 Insert 前都会生成一个不冲突的新值.

随后插入二级索引 b，由于其是唯一索引，在检查 duplicate key 时，加上记录锁，类型为 LOCK_X

对于普通的 INSERT 操作，当需要检查duplicate key 时，加 LOCK_S 锁，而对于 Replace into 或者 INSERT..ON DUPLICATE 操作，则加 LOCK_X 记录锁。当记录已存在，返回错误 DB_DUPLICATE_KEY。

Step 2 处理错误

由于上一步检测到 duplicate key，因此第一步插入的聚集索引记录需要回滚。

Step 3 转换操作

从 InnoDB 层失败返回到 Server 层后，收到 duplicate key 错误，首先检索唯一键冲突的索引，并对冲突的索引记录（及聚集索引记录）加锁

随后确认转换模式以解决冲突：

如果发生 uk 冲突的索引是最后一个唯一索引、没有外键引用、且不存在 delete trigger 时，使用 UPDATE ROW 的方式来解决冲突

否则，使用 DELETE ROW + INSERT ROW 的方式解决冲突, 如果是主键冲突，则会先删除在插入。

Step 4 更新记录

在该例中 a 是主键，对聚集索引和二级索引的更新，都是采用标记删除+插入新记录的方式。对于聚集索引，由于PK列发生变化，采用 delete + insert 聚集索引记录的方式更新。对于二级唯一键索引，同样采用标记删除 + 插入的方式。

三、案例分析

3.1 准备测试环境

事务隔离级别 REPEATABLE READ

数据准备

1. create table ix(id int not null auto_increment,

2. a int not null ,

3. b int not null ,

4. primary key(id),

5. idxa(a)

6. ) engine=innodb default charset=utf8;

7. insert into ix(a,b) valuses(1,1),(5,10),(15,12);

死锁场景

3.2 过程分析

在每次执行一条语句之后都执行 show innodb engine status 查看事务的状态，

执行 replace into ix(a,b) values(5,8)的事务日志如下

1. ---TRANSACTION 1872, ACTIVE 46 sec

2. 4 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 2

3. MySQL thread id 1156, OS thread handle 0672, query id 114 localhost msandbox

分析

replace into ix(a,b) values(5,8)，因为记录 a=5 已经存在，则会对记录进行更新操作，对记录加 Next Key 锁 RECORD lock，GAP lock，

该事务产生 2 条 undo，持有 4 把锁 一把 IX 锁，1 个 a = 5 的行的行锁，2 个间隙锁 a 在 1-5,5-15 之间的间隙。

执行replace into ix(a,b) values(8,10)的事务日志如下

1. ---TRANSACTION 1873, ACTIVE 3 sec inserting

2. mysql tables in use 1, locked 1

3. LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s),

4. undo log entries 1

5. MySQL thread id 1155, OS thread handle 3008,

6. query id 117 localhost msandbox update

7. replace into ix(a,b) values(8,10)

8. ------- TRX HAS BEEN WAITING 3 SEC FOR THIS LOCK TO BE GRANTED:

9. RECORD LOCKS space id 24 page no 4 n bits 80

10. index idx_a of table `test`.`ix` trx id 1873

11. lock_mode X locks gap before rec insert intention waiting

12. ---TRANSACTION 1872, ACTIVE 69 sec

13. 4 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 2

分析

表中没有 a=8 的记录,所以类似 insert into ix(a,b) values(8,10)。但是 a=8 与sess1 持有的 gap lock [5-15] 冲突，于是等待lock_mode X locks gap before rec insert intention waiting，并进入等待队列里面。这把锁是由 sess1 持有。

执行 replace into ix(a,b) values(9,12);事务日志如下执行该语句 sess2 立即报 发生死锁

1. *** (1) TRANSACTION:

2. TRANSACTION 1866, ACTIVE 8 sec inserting

3. mysql tables in use 1, locked 1

4. LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1

5. MySQL thread id 1155, OS thread handle 3008, query id 101 localhost msandbox update

6. replace into ix(a,b) values(8,10)

7. *** (1) WAITING FOR THIS LOCK TO BE GRANTED:

8. RECORD LOCKS space id 24 page no 4 n bits 80 index idx_a of table `test`.`ix` trx id 1866

9. lock_mode X locks gap before rec insert intention waiting

10. *** (2) TRANSACTION:

11. TRANSACTION 1865, ACTIVE 19 sec inserting

12. mysql tables in use 1, locked 1

13. 5 lock struct(s), heap size 1136, 5 row lock(s),

14. undo log entries 3

15. MySQL thread id 1156, OS thread handle 0672,

16. query id 102 localhost msandbox update

17. replace into ix(a,b) values(9,12)

18. *** (2) HOLDS THE LOCK(S):

19. RECORD LOCKS space id 24 page no 4 n bits 80 index idx_a of table `test`.`ix` trx id 1865 lock_mode X

20. *** (2) WAITING FOR THIS LOCK TO BE GRANTED:

21. RECORD LOCKS space id 24 page no 4 n bits 80

22. index idx_a of table `test`.`ix` trx id 1865

23. lock_mode X locks gap before rec insert intention waiting

24. *** WE ROLL BACK TRANSACTION (1)

日志分析

1. replace into ix(a,b) values(9,12); 和插入(8,10) 类似需要申请 lock_mode X locks gap before rec insert intention waiting，并且进入申请锁的队列等待。

2. 事务 T2 replace into ix(a,b) values(5,8); 该语句持有 4 把锁 一把 IX 锁，1 个 a=5 的行的行锁，2 个 a 在 1-5,5-15 之间的 GAP 锁。

3. 事务 T1 replace into ix(a,b) values(8,10); a=8 与sess1 持有的 gap lock [5，15] 冲突，于是等待 lock_mode X locks gap before rec insert intention waiting，并进入等待队列里面。

4. 事务 T2 replace into ix(a,b) values(9,12), a=9 也在[5-15]之间，需要等待 T1 的 insert intention lock 释放，T1 等待 T2(SQL1) ，T2(SQL2)等 T1 进而导致死锁 ,系统选择回滚事务 T1。

四、总结

分析定位到问题，怎么解决？目前给开发的建议是避免使用 replace into 方式，使用单条 select 检查 + insert 的方式 或者如果可以接受一定的死锁，可以减少并发执行改为串行。有兴趣的朋友可以自己复现，有更好的解决方法, 可以相互交流。

五、参考

[1] https://dev.mysql.com/doc/refman/5.7/en/innodb-locks-set.html 中阐述了各种语句的加锁方式，对死锁有兴趣的同学一定不要错过。

[2] http://mysqllover.com/?p=1312