MySQL 并发 replace 导致的死锁

一 前言 

   死锁其实是一个很有意思也很有挑战的技术问题，大概每个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语句执行是，系统会在记录上加上 next-key lock。"

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

create table t1 (a int auto_increment primary key, b int, c int, unique key (b));

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记录锁) 。 由于uk记录已存在，返回错误DB_DUPLICATE_KEY。

Step 2. 处理错误

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

Step 3. 转换操作

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

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

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

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

Step 4. 更新记录

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

三 案例分析

3.1 准备测试环境

事务隔离级别 REPEATABLE READ

数据准备

3.2 过程分析 

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

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

---TRANSACTION 1872, ACTIVE 46 sec4 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 2MySQL thread id 1156, OS thread handle 139645480060672, 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);

事务日志如下

---TRANSACTION 1873, ACTIVE 3 sec insertingmysql tables in use 1, locked 1LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1MySQL thread id 1155, OS thread handle 139646312843008, query id 117 localhost msandbox updatereplace into ix(a,b) values(8,10)------- TRX HAS BEEN WAITING 3 SEC FOR THIS LOCK TO BE GRANTED:RECORD LOCKS space id 24 page no 4 n bits 80 index idx_a of table `test`.`ix` trx id 1873 lock_mode X locks gap before rec insert intention waiting---------------------TRANSACTION 1872, ACTIVE 69 sec4 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 2

分析

replace into ix(a,b) values(8,10); 表中没有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) TRANSACTION:TRANSACTION 1866, ACTIVE 8 sec insertingmysql tables in use 1, locked 1LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1MySQL thread id 1155, OS thread handle 139646312843008, query id 101 localhost msandbox updatereplace into ix(a,b) values(8,10)*** (1) WAITING FOR THIS LOCK TO BE GRANTED:RECORD LOCKS space id 24 page no 4 n bits 80 index idx_a of table `test`.`ix` trx id 1866 lock_mode X locks gap before rec insert intention waiting*** (2) TRANSACTION:TRANSACTION 1865, ACTIVE 19 sec insertingmysql tables in use 1, locked 15 lock struct(s), heap size 1136, 5 row lock(s), undo log entries 3MySQL thread id 1156, OS thread handle 139645480060672, query id 102 localhost msandbox updatereplace into ix(a,b) values(9,12)*** (2) HOLDS THE LOCK(S):RECORD LOCKS space id 24 page no 4 n bits 80 index idx_a of table `test`.`ix` trx id 1865 lock_mode X*** (2) WAITING FOR THIS LOCK TO BE GRANTED:RECORD LOCKS space id 24 page no 4 n bits 80 index idx_a of table `test`.`ix` trx id 1865 lock_mode X locks gap before rec insert intention waiting*** WE ROLL BACK TRANSACTION (1)

日志分析

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

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

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

事务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