内容简介:死锁,其实是一个很有意思也很有挑战的技术问题,大概每个DBA和部分开发同学都会在工作过程中遇见 。关于死锁我会持续写一个系列的案例分析,希望能够对想了解死锁的朋友有所帮助。业务方的目的是使用另外需要特别注明的是我们最近对数据库进行升级,将数据库版本从Percona的5.6.24升级到5.7.22,业务在老版并没有死锁出现,但是
一 前言
死锁,其实是一个很有意思也很有挑战的技术问题,大概每个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 解决方法
-
使用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
如果看官没有给打赏,可以打开支付宝首页搜索 8350119 立即领花呗红包,最高可得27元红包。
以上就是本文的全部内容,希望对大家的学习有所帮助,也希望大家多多支持 码农网
猜你喜欢:- iOS混合开发库(GICXMLLayout)布局案例分析(1)今日头条案例
- 17个云计算开源案例入围第三届中国优秀云计算开源案例评选
- Spring Boot 2.0 基础案例(十二):基于转账案例,演示事务管理操作
- 基于MNIST数据集实现2层神经网络案例实战-大数据ML样本集案例实战
- Nginx相关实战案例
- SSIS 开发案例
本站部分资源来源于网络,本站转载出于传递更多信息之目的,版权归原作者或者来源机构所有,如转载稿涉及版权问题,请联系我们。