MySQL 并发insert 导致的死锁

一 前言

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

二 案例分析

2.1 业务场景

正常的业务逻辑是用户触发行为系统发消息然后写入2条记录。如果遇到处理失败或者消息超时则会重试。

2.2 环境说明

MySQL 5.7.22 事务隔离级别为RC模式。

CREATE TABLE `dl` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `num` int(10) unsigned DEFAULT NULL,
  `val` varchar(30) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  UNIQUE KEY `num_index` (`num`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
insert into dl(num,val) values(10,'1'),(20,'2'),(30,'30'),
(50,'50'),(60,'60');

2.3 测试用例

T1 sess1 insert into dl(num,val) values(102,'sess1');

T2 sess2 insert into dl(num,val) values(102,'sess2');

T3 sess1 insert into dl(num,val) values(101,'sess1');

T4 sess2 deadlock 

2.4 死锁日志

2018-12-30 11:45:10 0x7f61a5db9700
*** (1) TRANSACTION:
TRANSACTION 47631331, ACTIVE 7 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1
MySQL thread id 239396, OS thread handle 140057402881792, query id 82403444 127.0.0.1 root update
insert into dl(num,val) values(102,'sess2')
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 28 page no 4 n bits 80 index num_index of table `test`.`dl` trx id 47631331 lock mode S waiting
*** (2) TRANSACTION:
TRANSACTION 47631326, ACTIVE 22 sec inserting, thread declared inside InnoDB 5000
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 2
MySQL thread id 239358, OS thread handle 140057371186944, query id 82403598 127.0.0.1 root update
insert into dl(num,val) values(101,'sess1')
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 28 page no 4 n bits 80 index num_index of table `test`.`dl` trx id 47631326 lock_mode X locks rec but not gap
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 28 page no 4 n bits 80 index num_index of table `test`.`dl` trx id 47631326 lock_mode X locks gap before rec insert intention waiting
*** WE ROLL BACK TRANSACTION (1)

2.5 背景知识

首先在分析死锁案例之前，我们先学习一下背景知识 insert 语句的加锁策略,来看看官方定义:

"INSERT sets an exclusive lock on the inserted row. This lock is an index-record lock, not a next-key lock (that is, there is no gap lock) and does not prevent other sessions from inserting into the gap before the inserted row.Prior to inserting the row, a type of gap lock called an insert intention gap lock is set. This lock signals the intent to insert in such a way that multiple transactions inserting into the same index gap need not wait for each other if they are not inserting at the same position within the gap. "

相信大部分的DBA同行都知道在事务执行insert的时候会申请一把插入意向锁(Insert Intention Lock)。在多个会话并发写入不同数据记录至同一索引间隙的时候，并不需要等待其他事务完成，不会发生锁等待。

假设有一个索引记录包含键值4和7，不同的会话分别插入5和6，每个事务都会产生一个加在4-7之间的插入意向锁，获取在插入行上的排它锁，但是不会被互相锁住，因为数据行并不冲突。 但是如果遇到唯一键呢？ 

"If a duplicate-key error occurs, a shared lock on the duplicate index record is set."

对于insert操作来说，若发生唯一约束冲突，则需要对冲突的唯一索引加上S Next-key Lock。即使是RC事务隔离级别，也同样会存在Next-Key Lock锁，从而阻塞并发。然而，文档没有说明的是，对于检测到冲突的唯一索引，等待线程在获得S Lock之后，还需要对下一个记录进行加锁，在源码中由函数row_ins_scan_sec_index_for_duplicate进行判断。via (MySQL REPLACE死锁问题深入剖析 )。

本案例中涉及到一个知识点 有唯一键约束，insert之前，其他事务且对即将插入的记录的next-record加了Gap-Lock 比如，程序执行insert 101之前,其他会话对102 加上了 Gap Lock 会是什么样呢？因为RC模式下 select for update 不能直接加gap lock，测试用例采用唯一键冲突的方式构造S 

sess1 insert num=28

sess1 11:10:04 >insert into dl(num,val) values(28,'23');
Query OK, 1 row affected (0.00 sec)

sess2 insert num=28 ,构造唯一键冲突,sess2 会申请在num=28记录上申请GAP Lock (记录之前还是之后呢？)

sess2 11:11:50 >insert into dl(num,val) values(28,'sess1');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

sess3 insert num=29 ，num=27 两条记录，29可以成功插入，但是num=27 则需要等待sess2的lock mode S waiting

sess3 11:12:34 >insert into dl(num,val) values(29,'sess3');
Query OK, 1 row affected (0.00 sec)

sess3 11:12:48 >insert into dl(num,val) values(27,'sess3');
Query OK, 1 row affected (11.88 sec)

三个事务相关的锁等待日志

---sess3, ACTIVE 60 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 6 lock struct(s), heap size 1136, 5 row lock(s), undo log entries 4
MySQL thread id 270616, OS thread handle 140051476178688, query id 83929304 127.0.0.1 root update
insert into dl(num,val) values(27,'sess3')
-- TRX HAS BEEN WAITING 4 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 28 page no 4 n bits 80 index num_index of table `test`.`dl` trx id 47631351 lock mode S waiting

---sess1 47631350, ACTIVE 142 sec
2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1
MySQL thread id 270559, OS thread handle 140057337198336, query id 83929372 127.0.0.1 root starting
show engine innodb status
---sess2, ACTIVE 218 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 5 row lock(s), undo log entries 1
MySQL thread id 270543, OS thread handle 140057370986240, query id 83928936 127.0.0.1 root update
insert into dl(num,val) values(28,'sess1')
TRX HAS BEEN WAITING 23 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 28 page no 4 n bits 80 index num_index of table `test`.`dl` trx id 47631349 lock mode S waiting

划重点  insert 的流程(有唯一索引的情况): 比如insert N

1. 找到大于N的第一条记录M，以及前一条记录P

2. 如果M上面没有gap/next-key lock,进入第三步骤,否则等待(对其next-rec加insert intension lock，由于有gap锁，所以等待)

3. 检查P： 判断P是否等于N:

 如果不等: 则完成插入（结束）
 如果相等: 再判断P是否有锁，
    a 如果没有锁:报1062错误(duplicate key),说明该记录已经存在，报重复值错误 
    b 加S-lock,说明该记录被标记为删除, 事务已经提交，还没来得及purge
    c 如果有锁: 则加S-lock,说明该记录被标记为删除，事务还未提交.

该结论引自: http://keithlan.github.io/2017/06/21/innodb locks algorithms/

2.6 分析死锁日志

结合上面的理论和案例分析:

T1 sess1 插入num=102,并持有该记录的record lock T2 sess2 插入num=102,由于sess1已经插入但未提交,申请lock mode S waiting等待sess1释放锁。 T3 sess1 插入num=101,由于sess2 在102上申请gap lock锁住(60，102)区间故发生等待,显示lock_mode X locks gap before rec insert intention waiting

至此sess2 对num=102申请S-lock 等待sess1释放，sess1 申请insert intention waiting 等待sess2 释放gap lock,构成循环等待。

2.7 解决方法

  调整insert 101,102的顺序。

三 小结

本次死锁案例的核心点在于有唯一键约束，insert之前，其他事务且对即将插入的记录的next-record加了Gap-Lock 则需要等待。