一 前言
死锁,其实是一个很有意思也很有挑战的技术问题,大概每个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 死锁日志
*** (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锁.
其次 我们需要了解锁的兼容性矩阵。
![](https://ucc.alicdn.com/okzvupdqrioa2_20240422_4a643d5b9a734427bb98a1342db9e01e.png?x-oss-process=image/resize,w_1400/format,webp)
从兼容性矩阵我们可以得到如下结论:
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
![](https://ucc.alicdn.com/okzvupdqrioa2_20240422_253b5bccdb0f4b48869a05723692d61c.png?x-oss-process=image/resize,w_1400/format,webp)
并没有显示锁相关内容,事情就这么简单吗?继续查询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 信息
![](https://ucc.alicdn.com/okzvupdqrioa2_20240422_eaabfdacacae4453ac0785feebe889a1.png?x-oss-process=image/resize,w_1400/format,webp)
![](https://ucc.alicdn.com/okzvupdqrioa2_20240422_b3a9d5c47cdf4aac87a7eecdb1d476c1.png?x-oss-process=image/resize,w_1400/format,webp)
从事务列表来看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