死锁案例（十一）

一 前言

死锁，其实是一个很有意思也很有挑战的技术问题，大概每个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 deadlock

2.4 死锁日志

2018-12-01 23:58:04 0x7f1295f3b700
*** (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 解决方法

1. 使用read commited 事务隔离级别。

2. 降低事务中insert 次数。

3. 使用队列，降低并发，比如单线程执行insert。

4. 回退版本到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

漫谈死锁

如何阅读死锁日志

死锁案例之一

死锁案例之二

死锁案例之三

死锁案例之四

死锁案例之五

死锁案例之六

死锁案例之七

死锁案例之八

死锁案例之九

死锁案例之十

insert 语句加锁机制

MySQL 各种SQL语句加锁分析

如果看官没有给打赏，可以打开支付宝首页搜索 8350119 立即领花呗红包，最高可得27元红包。