死锁案例三

来源：公众号yangyidba
一、前言死锁其实是一个很有意思也很有挑战的技术问题，大概每个 DBA 和部分开发朋友都会在工作过程中遇见。关于死锁我会持续写一个系列的案例分析，希望能够对想了解死锁的朋友有所帮助。
二、背景知识2.1 insert 锁机制在分析死锁案例之前，我们先学习一下背景知识 insert 语句的加锁策略。我们先来看看官方定义:
An insert intention lock is a type of gap lock set by INSERT operations prior to row insertion. 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. Suppose that there are index records with values of 4 and 7. Separate transactions that attempt to insert values of 5 and 6, respectively, each lock the gap between 4 and 7 with insert intention locks prior to obtaining the exclusive lock on the inserted row, but do not block each other because the rows are nonconflicting.
相信大部分的 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 死锁问题深入剖析 )。我们可以通过如下例子进行验证。
2.2 验证准备环境默认事务隔离级别为 RC 模式。
CREATE TABLE t8 (
a int AUTO_INCREMENT PRIMARY KEY,
b int,
c int,
unique key ub(b)
) engine=InnoDB;
insert into t8 values (NULL,1,2)
2.3 过程分析在每次执行一条语句之后都执行 show innodb engine status 查看事务的状态，执行完 delete 语句，事务相关日志显示如下:
—TRANSACTION 462308671, ACTIVE 6 sec
3 lock struct(s), heap size 360, 2 row lock(s), undo log entries 1
MySQL thread id 3796960, OS thread handle 0x7f78eaabe700, query id 781051370 localhost root init
show engine innodb status
TABLE LOCK table `test`.`t8` trx id 462308671 lock mode IX
RECORD LOCKS space id 232 page no 4 n bits 72 index `ub` of table `test`.`t8` trx id 462308671 lock_mode X locks rec but not gap
RECORD LOCKS space id 232 page no 3 n bits 72 index `PRIMARY` of table `test`.`t8` trx id 462308671 lock_mode X locks rec but not gap
从日志中我们可以看到 delete 语句获取了唯一索引 ub 和主键两个行级锁(lock_mode X locks rec but not gap) 。
执行完 insert 之后 再查看 innodb engine status，事务相关日志显示如下:
LIST OF TRANSACTIONS FOR EACH SESSION:
—TRANSACTION 462308676, ACTIVE 4 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s), undo log entries 1
MySQL thread id 3796966, OS thread handle 0x7f78ea5c4700, query id 781051460 localhost root update
insert into t8 values (NULL,1,2)
——- TRX HAS BEEN WAITING 4 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 232 page no 4 n bits 72 index `ub` of table `test`.`t8` trx id 462308676 lock mode S waiting
——————
TABLE LOCK table `test`.`t8` trx id 462308676 lock mode IX
RECORD LOCKS space id 232 page no 4 n bits 72 index `ub` of table `test`.`t8` trx id 462308676 lock mode S waiting
—TRANSACTION 462308671, ACTIVE 70 sec
3 lock struct(s), heap size 360, 2 row lock(s), undo log entries 1
MySQL thread id 3796960, OS thread handle 0x7f78eaabe700, query id 781051465 localhost root init
show engine innodb status
TABLE LOCK table `test`.`t8` trx id 462308671 lock mode IX
RECORD LOCKS space id 232 page no 4 n bits 72 index `ub` of table `test`.`t8` trx id 462308671 lock_mode X locks rec but not gap
RECORD LOCKS space id 232 page no 3 n bits 72 index `PRIMARY` of table `test`.`t8` trx id 462308671 lock_mode X locks rec but not gap
根据官方的介绍，并结合日志，我们可以看到 insert into t8 values (NULL,1,2)在申请一把 S Next-key-Lock , 显示 lock mode S waiting。这里想给大家说明的是在Innodb 日志中如果提示 lock mode S/lock mode X ，其实都是 gap 锁，如果是行记录锁会提示 but not gap,请读者朋友们在自己分析死锁日志的时候注意。
sess1 delete 语句提交之后，sess2 的 insert不要提交，不要提交，不要提交。再次查看 innodb engine status，事务相关日志显示如下:
—————–
TRANSACTIONS
————
Trx id counter 462308678
Purge done for trxs n:o < 462308678 undo n:o < 0 state: running but idle
History list length 1845
LIST OF TRANSACTIONS FOR EACH SESSION:
—TRANSACTION 462308671, not started
MySQL thread id 3796960, OS thread handle 0x7f78eaabe700, query id 781051526 localhost root init
show engine innodb status
—TRANSACTION 462308676, ACTIVE 41 sec
3 lock struct(s), heap size 360, 2 row lock(s), undo log entries 1
MySQL thread id 3796966, OS thread handle 0x7f78ea5c4700, query id 781051460 localhost root cleaning up
TABLE LOCK table `test`.`t8` trx id 462308676 lock mode IX
RECORD LOCKS space id 232 page no 4 n bits 72 index `ub` of table `test`.`t8` trx id 462308676 lock mode S
RECORD LOCKS space id 232 page no 4 n bits 72 index `ub` of table `test`.`t8` trx id 462308676 lock mode S locks gap before rec
sess1 中的事务因为提交已经结束。Innodb 中的事务列表中只剩下sess2 中的 insert 的事务了。从获取锁的状态上看 insert 获取一把 S Next-key Lock 锁和插入行之前的 S GAP 锁。看到这里大家是否有疑惑,官方文档说:
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.
会对 insert 成功的记录加上一把X行锁，为什么看不见呢？我们再在 sess1 中执行*update t8 set c=13 where b=1; *并查看事务日志
————
TRANSACTIONS
————
Trx id counter 462308679
Purge done for trxs n:o < 462308678 undo n:o < 0 state: running but idle
History list length 1845
LIST OF TRANSACTIONS FOR EACH SESSION:
—TRANSACTION 462308678, ACTIVE 12 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s)
MySQL thread id 3796960, OS thread handle 0x7f78eaabe700, query id 781059217 localhost root updating
update c set c=13 where b=1
——- TRX HAS BEEN WAITING 12 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 232 page no 4 n bits 72 index `ub` of table `test`.`t8` trx id 462308678 lock_mode X locks rec but not gap waiting
——————
TABLE LOCK table `test`.`t8` trx id 462308678 lock mode IX
RECORD LOCKS space id 232 page no 4 n bits 72 index `ub` of table `test`.`t8` trx id 462308678 lock_mode X locks rec but not gap waiting
—TRANSACTION 462308676, ACTIVE 5113 sec
4 lock struct(s), heap size 1184, 3 row lock(s), undo log entries 1
MySQL thread id 3796966, OS thread handle 0x7f78ea5c4700, query id 781059230 localhost root init
show engine innodb status
TABLE LOCK table `test`.`t8` trx id 462308676 lock mode IX
RECORD LOCKS space id 232 page no 4 n bits 72 index `ub` of table `test`.`t8` trx id 462308676 lock mode S
RECORD LOCKS space id 232 page no 4 n bits 72 index `ub` of table `test`.`t8` trx id 462308676 lock mode S locks gap before rec
RECORD LOCKS space id 232 page no 4 n bits 72 index `ub` of table `test`.`t8` trx id 462308676 lock_mode X locks rec but not gap
从日志中可以看到 sess2 的事务持有的锁多了一把 lock_mode X locks rec but not gap，也即是 sess2 对 insert 成功的记录加上的 X 行锁。
分析至此，对于并发 insert 造成唯一键冲突的时候 insert 的加锁策略是:
第一阶段唯一性约束检查，先申请 LOCKINSERTINTENTION
第二接入获取阶段一的锁并且 insert 成功之后插入的位置有 Gap 锁：LOCKS + LOCKORDINARY，为了防止其他 insert 唯一键冲突。
插入成功的记录：LOCK_X + LOCK_REC_NOT_GAP
三、案例分析本案例是两个事务并发 insert 唯一键冲突和 gap 锁一起导致的死锁案例。
3.1 环境create table t7(
id int not null primary key auto_increment,
a int not null ,
unique key ua(a)
) engine=innodb;
insert into t7(id,a) values(1,1),(5,4),(20,20),(25,12);
3.2 测试项目
3.3 死锁日志————————
LATEST DETECTED DEADLOCK
————————
2017-09-17 15:15:03 7f78eac15700
*** (1) TRANSACTION:
TRANSACTION 462308661, ACTIVE 6 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s), undo log entries 1
MySQL thread id 3796966, OS thread handle 0x7f78ead9d700, query id 781045166 localhost root update
insert into t7(id,a) values(30,10)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 231 page no 4 n bits 72 index `ua` of table `test`.`t7` trx id 462308661 lock mode S waiting
*** (2) TRANSACTION:
TRANSACTION 462308660, ACTIVE 43 sec inserting, thread declared inside InnoDB 5000
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1184, 3 row lock(s), undo log entries 2
MySQL thread id 3796960, OS thread handle 0x7f78eac15700, query id 781045192 localhost root update
insert into t7(id,a) values(40,9)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 231 page no 4 n bits 72 index `ua` of table `test`.`t7` trx id 462308660 lock_mode X locks rec but not gap
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 231 page no 4 n bits 72 index `ua` of table `test`.`t7` trx id 462308660 lock_mode X locks gap before rec insert intention waiting
*** WE ROLL BACK TRANSACTION (1)
日志分析
我们从时间线维度分析:
事务 T2 insert into t7(id,a) values(26,10)语句 insert 成功，持有 a=10 的 X 行锁(X locks rec but not gap)
事务 T1 insert into t7(id,a) values(30,10),因为 T2 的第一条 insert 已经插入 a=10 的记录，事务 T1 的 insert a=10 则发生唯一约束冲突，需要申请对冲突的唯一索引加上 S Next-key Lock (也即是 lock mode S waiting ) 这是一个间隙锁会申请锁住(,10],(10,20]之间的 gap 区域。从这里会发现，即使是 RC 事务隔离级别，也同样会存在 Next-Key Lock 锁，从而阻塞并发。
事务T2 insert into t7(id,a) values(40,9) 该语句插入的 a=9 的值在事务 T1 申请的 gap 锁[4,10]之间，故需事务 T2 的第二条 insert 语句要等待事务 T1 的 S-Next-key Lock 锁释放，在日志中显示 lock_mode X locks gap before rec insert intention waiting。
四、总结本文案例和知识点一方面从官方文档获取,另一方面是根据何登成和姜承尧两位 MySQL 技术大牛的技术分享整理，算是站在巨人的肩膀上的学习总结。在研究分析死锁案例的过程中，insert 的意向锁和 gap 锁这种类型的锁是比较难分析的，相信通过上面的分析总结大家能够学习到 insert 的锁机制,如何加锁,如何进行 insert 方面死锁分析。
扩展阅读
1.漫谈死锁
2.如何阅读死锁日志
3.死锁案例二
4.死锁案例一
全文完。
Enjoy MySQL:)
叶老师的「MySQL核心优化」大课已升级到MySQL 8.0，扫码开启MySQL 8.0修行之旅吧