一 前言
死锁，其实是一个很有意思也很有挑战的技术问题，大概每个DBA和部分开发同学都会在工作过程中遇见 。关于死锁我会持续写一个系列的案例分析，希望能够对想了解死锁的朋友有所帮助。
二 案例分析
2.1 业务场景
业务方的目的是使用insert on duplicate key update对重复存在的记录进行更新，没有则插入最新的记录。
另外需要特别注明的是我们最近对数据库进行升级，将数据库版本从Percona的5.6.24升级到5.7.22,业务在老版并没有死锁出现，但是升级到5.7.22版本的RR模式之后出现死锁。
小插曲我们的数据库架构是 app-->rds proxy(lvs) --> DB模式的,一开始排查问题的时候以为DB实例设置RC模式，业务就是利用RC默认的事务隔离级别，但是万万没有想到rds proxy自己在连接初始化的时候设置了RR模式，导致之前基于RC模式的推断完全错误而且没有头绪。 说明我对整体技术框架的把控还不足。
2.2 环境说明
MySQL 5.7.22 事务隔离级别为RR模式。
CREATE TABLE `t` (
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 t(num,val) values(10,'1'),(20,'2'),(30,'30'),
(50,'50'),(60,'60');2.3 测试用例
sess1
begin;
insert into t(num,val) values(45,'45') on duplicate key update val='45';
sess2
begin;
insert into t(num,val) values(40,'40') on duplicate key update val='40';
sess1
T3 insert into t(num,val) values(38,'38') on duplicate key update val='38';
T4 sess2 deadlock2.4 死锁日志
*** (1) TRANSACTION:
TRANSACTION 24064444, ACTIVE 17 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 3603293, OS thread handle 139715684976384, query id 109761111 127.0.0.1 root update
insert into t(num,val) values(40,'40') on duplicate key update val='40'
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 219 page no 4 n bits 80 index num_index of table `test`.`t` trx id 24064444 lock_mode X locks gap before rec insert intention waiting
*** (2) TRANSACTION:
TRANSACTION 24064443, ACTIVE 31 sec inserting, thread declared inside InnoDB 5000
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 2
MySQL thread id 3603303, OS thread handle 139717801916160, query id 109761440 127.0.0.1 root update
insert into t(num,val) values(38,'38') on duplicate key update val='38'
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 219 page no 4 n bits 72 index num_index of table `test`.`t` trx id 24064443 lock_mode X locks gap before rec
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 219 page no 4 n bits 80 index num_index of table `test`.`t` trx id 24064443 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死锁问题深入剖析 )。
划重点 上面的表述在针对5.6的时候没有问题。但是在5.7版本中就有疑问了,RR模式下insert on duplicate key update模式插入成功之后不仅仅是持有record lock 而且还持有一个Lock X GAP锁.
其次 我们需要了解锁的兼容性矩阵。
从兼容性矩阵我们可以得到如下结论:
INSERT操作之间不会有冲突。GAP,Next-Key会阻止Insert。GAP和Record,Next-Key不会冲突Record和Record、Next-Key之间相互冲突。已有的Insert锁不阻止任何准备加的锁。已经持有的gap 锁会阻塞插入意向锁INSERT_INTENTION
另外 对于通过唯一索引更新或者删除不存在的记录，会申请加上 gap锁。
了解上面的基础知识，我们开始对死锁日志进行分析:
T1 insert values(45,'45'),我们查看innodb_locks
并没有显示锁相关内容,事情就这么简单吗？继续查询show engine innodb status 查看事务列表的信息:
---TRANSACTION 24064455, ACTIVE 63 sec
2 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
TABLE LOCK table `test`.`t` trx id 24064449 lock mode IX
RECORD LOCKS space id 219 page no 4 n bits 80 index num_index of table `test`.`t` trx id 24064449 lock_mode X locks gap before rec
--------从结果来看T1时刻，sess1 持有表级别的意向锁LOCK IX 和2个X GAP LOCK，区间为 (30,45],(45,50]。
T2时刻 insert values(40,'40') ,我们查看 innodb_locks 信息
从事务列表来看T1 sess1 3 row lock(s) 持有表的意向锁，两个X locks gap before rec (30,45],(45,50] 以及 num=45的record lock(lock_mode X locks rec but not gap)
T2 sess2 insert values(40,'40') 持有表级别的锁意向锁IX ,2个locks gap before rec (30, 40],(40,45]的gap锁，准备插入的值40在(30,45)之间与sess1持有的X GAP lock冲突，发生等待locks gap before rec insert intention waiting
T3 sess1 insert values(38,'38')和T2 sess2 类似，要插入38需要申请insert intention lock 与sess2持有的(30, 40) X GAP lock 冲突，于是等待sess2释放，至此我们可以理清楚死锁的顺序:
sess1(持有(30,45]的gap锁)，sess2(持有(30, 40]的gap锁)，sess1(T3 写入值38申请插入意向锁等待T2 sess2的gap锁释放) sess2(插入意向锁等待T1 sess1的gap锁释放) 构成循环等待，进而导致死锁。
2.6 解决方法
使用read commited 事务隔离级别。
降低事务中insert 次数。
使用队列，降低并发，比如单线程执行insert。
回退版本到5.6，显然对于我们而言不太现实。 5.6版本中 insert into t(num,val) values(45,'45') on duplicate key update val='45';对已经插入的记录num=45只会加上record lock，不会有额外的 gap lock。
三 小结
本次死锁的核心因素是5.7版本之后对INSERT INTO .. ON DUPLICATE KEY 等语句的锁模式做了加强，除了行锁之外还有GAP lock。参见：
https://bugs.mysql.com/bug.php?id=38046
[3 Aug 2012 15:34] Paul Dubois
Noted in 5.7.0 changelog.
INSERT INTO ... SELECT ... ON DUPLICATE KEY UPDATE and LOAD DATA
CONCURRENT REPLACE took too weak a lock, leading to the possibility
of concurrent SELECT statements returning inconsistent results.其他相关bug列表
https://bugs.mysql.com/bug.php?id=38046
https://bugs.mysql.com/bug.php?id=52020
四 推荐阅读
参考文章 https://zhuanlan.zhihu.com/p/29349080
