死锁案例之九

来源：公众号yangyidba
一 前言死锁，其实是一个很有意思也很有挑战的技术问题，大概每个DBA和部分开发同学都会在工作过程中遇见 。关于死锁我会持续写一个系列的案例分析，希望能够对想了解死锁的朋友有所帮助。
二 案例分析2.1 业务场景业务开发同学要初始化数据，他们的逻辑是批量执行insert values(x,x,x),(x,x,x); 该表有唯一键，批量插入的唯一键相邻，出现大量死锁。
2.2 环境说明MySQL 5.6.24 事务隔离级别为RR
CREATE TABLE `tc` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT ‘自增ID’, `c1` bigint(20) unsigned NOT NULL DEFAULT ‘0’, `c2` bigint(20) unsigned NOT NULL DEFAULT ‘0’, `c3` bigint(20) unsigned NOT NULL DEFAULT ‘0’, `c4` tinyint(4) NOT NULL DEFAULT ‘0’, `c5` tinyint(4) NOT NULL DEFAULT ‘0’, `created_at` datetime NOT NULL DEFAULT ‘1970-01-01 08:00:00’, `deleted_at` datetime NOT NULL DEFAULT ‘1970-01-01 08:00:00’, PRIMARY KEY (`id`), UNIQUE KEY `uniq_cid_bid_dt_tid` (`c1`,`c2`,`deleted_at`,`c3`)) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=utf8mb42.3 测试用例
2.4 死锁日志2018-04-01 21:41:34 0x7f75c8bff700*** (1) TRANSACTION:TRANSACTION 2004, ACTIVE 6 sec insertingmysql tables in use 1, locked 1LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 2MySQL thread id 517219, OS thread handle 40, query id 79 127.0.0.1 root updateINSERT IGNORE INTO tc (c2, c1, c3, created_at, c4, c5) VALUES(95529, 4083702165, 3549685, now(), 1, 4),(95529, 4083702165, 3549694, now(), 1, 4)*** (1) WAITING FOR THIS LOCK TO BE GRANTED:RECORD LOCKS space id 29 page no 4 n bits 72 index uniq_cid_bid_dt_tid of table `test`.`tc` trx id 2004 lock mode S waiting*** (2) TRANSACTION:TRANSACTION 1999, ACTIVE 16 sec inserting, thread declared inside InnoDB 5000mysql tables in use 1, locked 13 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 4MySQL thread id 517587, OS thread handle 92, query id 84 127.0.0.1 root updateINSERT IGNORE INTO tc (c2, c1, c3, created_at, c4, c5) VALUES(95529, 4083702165, 3549691, now(), 1, 5)*** (2) HOLDS THE LOCK(S):RECORD LOCKS space id 29 page no 4 n bits 72 index uniq_cid_bid_dt_tid of table `test`.`tc` trx id 1999 lock_mode X locks rec but not gap*** (2) WAITING FOR THIS LOCK TO BE GRANTED:RECORD LOCKS space id 29 page no 4 n bits 72 index uniq_cid_bid_dt_tid of table `test`.`tc` trx id 1999 lock_mode X locks gap before rec insert intention waiting*** WE ROLL BACK TRANSACTION (1)2.5 分析死锁日志首先我们要再次强调insert 插入操作的加锁逻辑。
第一阶段: 唯一性约束检查，先申请LOCK_S + LOCK_ORDINARY
第二阶段: 获取阶段一的锁并且insert成功之后,插入的位置有Gap锁:LOCK_INSERT_INTENTION,为了防止其他insert唯一键冲突。
新数据插入完成之后:LOCK_X + LOCK_REC_NOT_GAP
对于insert操作来说，若发生唯一约束冲突，需要对冲突的唯一索引申请加上S Next-key Lock。如果其他会话中包含已经插入记录的事务没有提交，则申请加锁出现等待，show engine innodb status中的事务列表中会提示lock mode S waiting。
从这里会发现，即使是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: sess2 执行批量insert 4条记录，先插入的记录构成唯一键(95529, 4083702165,now(),3549694),该记录在插入完成之后获取到的锁:LOCK_X + LOCK_REC_NOT_GAP。
T2: sess1 insert两条记录 (95529, 4083702165, 3549694, now(), 1, 4),和sess2中的唯一键冲突，于是申请S Next-key Lock,但是和sess2 的LOCK_REC_NOT_GAP 冲突(共享锁和已经持有的排他锁冲突),系统提示RECORD LOCKS space id 29 page no 4 n bits 72 index uniqcidbiddttid of table test.tc trx id 2004 lock mode S waiting
T3: sess2 insert 记录(95529, 4083702165, 3549691, now(), 1, 5),会申请锁LOCK_INSERT_INTENTION，其中3549691与sess1 中的3549694相邻,sess1申请S Next-key Lock会阻塞记录3549691插入。
T1时刻sess2(持有LOCK_REC_NOT_GAP)，T2时刻 sess1(申请S Next-key Lock）被sess2 阻塞，T3时刻sess2(插入意向锁等待sess1的gap锁释放)构成循环等待，进而导致死锁。
注意,这里对insert 唯一键的加锁逻辑自己可能表述不准确，望读者朋友多讨论。
2.6 解决方法其实针对此类并发insert导致的死锁，并没有好的解决方法，至少在sql层面没有行之有效的方法。之前的还可以调整sql的执行顺序，简化业务sql逻辑。但是对于此类情况只能调整唯一索引，或者尽量将初始化的数据打散，调整唯一索引要调整整体的业务层面的逻辑了，需要开发深度介入。
三 小结本案例的死锁要素是 1 并发insert 2 并发插入的记录唯一键相邻，GAP,Next-Key会阻止Insert。
扫码关注作者微信公众号
扩展阅读
死锁案例之八
死锁案例之七
死锁案例之六
死锁案例之五
死锁案例之四
死锁案例之三
死锁案例之二
死锁案例之一
漫谈死锁
如何阅读死锁日志
全文完。Enjoy MySQL:)叶老师的「MySQL核心优化」大课已升级到MySQL 8.0，扫码开启MySQL 8.0修行之旅吧