一个mysql死锁场景实例分析
作者:hbprotoss 发布时间:2024-01-16 21:59:47
前言
最近遇到一个mysql在RR级别下的死锁问题,感觉有点意思,研究了一下,做个记录。
涉及知识点:共享锁、排他锁、意向锁、间隙锁、插入意向锁、锁等待队列
场景
隔离级别:Repeatable-Read
表结构如下
create table t (
id int not null primary key AUTO_INCREMENT,
a int not null default 0,
b varchar(10) not null default '',
c varchar(10) not null default '',
unique key uniq_a_b(a,b),
unique key uniq_c(c)
);
初始化数据
insert into t(a,b,c) values(1,'1','1');
有A/B两个session,按如下顺序执行两个事务
结果是
B执行完4之后还是一切正常
A执行5的时候,被block
B接着执行6,B报死锁,B回滚,A插入数据
show engine innodb status
中可以看到死锁信息,这里先不贴,先解释几种锁的概念,再来理解死锁过程
共享(S)锁/互斥(X)锁
共享锁允许事务读取记录
互斥锁允许事务读写记录
这两种其实是锁的模式可以和行锁、间隙锁混搭,多个事务可以同时持有S锁,但是只有一个事务能持有X锁
意向锁
一种表锁(也是一种锁模式),表明有事务即将给对应表的记录加S或者X锁。SELECT ... LOCK IN SHARE MODE会在给记录加S锁之前先给表加IS锁,SELECT ... FOR UPDATE会在给记录加X锁之前给表加IX锁。
这是一种mysql的锁优化策略,并不是很清楚意向锁的优化点在哪里,求大佬指教
两种锁的兼容情况如下
行锁
很简单,给对应行加锁。比如update、select for update、delete等都会给涉及到的行加上行锁,防止其他事务的操作
间隙锁
在RR隔离级别下,为了防止幻读现象,除了给记录本身,还需要为记录两边的间隙加上间隙锁。
比如列a上有一个普通索引,已经有了1、5、10三条记录,select * from t where a=5 for update
除了会给5这条记录加行锁,还会给间隙(1,5)和(5,10)加上间隙锁,防止其他事务插入值为5的数据造成幻读。
当a上的普通索引变成唯一索引时,不需要间隙锁,因为值唯一,select * from t where a=5 for update
不可能读出两条记录来。
间隙锁相互兼容,因为如果互斥,事务A持有左半段(1,5),事务B持有右半段(1,10),那么当前面那个例子中a=5的记录被删除时,理论上左右两个间隙锁得合并成一个新锁(1,10),那么这个新的大范围锁属于谁呢?所以间隙锁相互兼容,不管是S间隙锁还是X间隙锁
插入意向锁
插入意向锁其实是一种特殊的间隙锁,从前面对间隙锁的描述中可以得知,两个事务在真正insert之前可以同时持有一段间隙的间隙锁,锁不住真正insert的这个动作。真正insert之前,mysql还会尝试获取对应记录的插入意向锁,表明有在间隙中插入一个值的意向。
插入意向锁和间隙锁互斥,比如事务1锁了(1,5)这个间隙,事务2就不能获取到a=3的插入意向锁,所以需要锁等待。
死锁过程分析
接下来就可以来分析前面那个例子中的死锁过程了,先看show engine innodb status
*** (1) TRANSACTION:
TRANSACTION 5967, ACTIVE 8 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 9, OS thread handle 140528848688896, query id 537 192.168.128.1 root update
insert into t(a,b) values(0,'0')
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 64 page no 4 n bits 72 index uniq_a_b of table `t2`.`t` trx id 5967 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 80000001; asc ;;
1: len 1; hex 31; asc 1;;
2: len 4; hex 80000001; asc ;;
*** (2) TRANSACTION:
TRANSACTION 5968, ACTIVE 7 sec inserting
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 8, OS thread handle 140528848484096, query id 538 192.168.128.1 root update
insert into t(a,b) values(0,'0')
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 64 page no 4 n bits 72 index uniq_a_b of table `t2`.`t` trx id 5968 lock_mode X locks gap before rec
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 80000001; asc ;;
1: len 1; hex 31; asc 1;;
2: len 4; hex 80000001; asc ;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 64 page no 4 n bits 72 index uniq_a_b of table `t2`.`t` trx id 5968 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 80000001; asc ;;
1: len 1; hex 31; asc 1;;
2: len 4; hex 80000001; asc ;;
*** WE ROLL BACK TRANSACTION (2)
session A(即TRANSACTION 5967)正在等待记录(a=1,b='1')之前的插入意向锁,session B(即TRANSACTION 5968)持有记录(a=1,b='1')之前的间隙锁,却也在等待那个插入意向锁。这说的什么玩意儿,是不是很诡异?
从头开始分析过程
A、B分别begin,开始事务
A先执行
select * from t where a=0 and b='0' for update;
,先加了IX锁,然后原本意图为给(0, '0')这条记录加排他行锁,但是记录不存在,所以变成了排他间隙锁(-∞,1)B再执行
select * from t where a=0 and b='0' for update;
,也是先加了IX锁,因为记录不存在,所以加上了排他间隙锁(-∞,1),但是由于间隙锁相互兼容,所以没有blockA执行
insert into t(a,b) values(0,'0');
,这时候,要开始真正insert了,A需要获得(0,'0')上的插入意向锁,由于和B持有的(-∞,1)排他间隙锁冲突,所以锁等待,进入记录(0,'0')的锁等待队列(虽然记录并不存在)B执行
insert into t(a,b) values(0,'0');
,要获取插入意向锁,发现虽然B自己是持有(-∞,1)的排他间隙锁,但是A也有,所以进入等待队列,等待A释放叮,死锁发生
死锁信息解读
事务1(TRANSACTION 5967),等待获得锁index uniq_a_b of table t2.t trx id 5967 lock_mode X locks gap before rec insert intention waiting,即在唯一索引uniq_a_b上的插入意向锁(lock_mode X locks gap before rec insert intention)
锁的边界为
0: len 4; hex 80000001; asc ;;
1: len 1; hex 31; asc 1;;
2: len 4; hex 80000001; asc ;;
表明两行记录
0和1表示uniq_a_b上的值,a=1,b=0x31(即'1'的ascii码)
a=1,b='1'对应的主键id=1,因为innodb的索引结构决定的,二级索引(非主键索引)指向主键索引,主键索引再指向数据,所以需要给主键加索引
至于int值按位或上的0x80000000就不是很清楚为什么了,需要大佬解读
事务2(TRANSACTION 5968),持有间隙锁index uniq_a_b of table t2.t trx id 5968 lock_mode X locks gap before rec,等待插入意向锁index uniq_a_b of table t2.t trx id 5968 lock_mode X locks gap before rec insert intention,所以死锁发生。
原则上是innodb引擎判断哪个事务回滚代价小就回滚哪个事务,但是具体评判标准不是很清楚(再一次需要大佬),这里innodb选择了回滚事务2。至此,死锁过程分析完毕
One More Thing
还没完。。。有个神奇的现象是,如果表结构变成
create table t (
id int not null primary key AUTO_INCREMENT,
a int not null default 0,
b varchar(10) not null default '',
c varchar(10) not null default '',
unique key uniq_c(c),
unique key uniq_a_b(a,b)
);
insert into t(a,b,c) values(1,1,1);
只是把c上的唯一索引uniq_c放到了uniq_a_b前面,那么最后的死锁信息就变了!
*** (1) TRANSACTION:
TRANSACTION 5801, ACTIVE 5 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 1
MySQL thread id 5, OS thread handle 140528848688896, query id 380 192.168.128.1 root update
insert into t2(a,b) values(0,'0')
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 56 page no 5 n bits 72 index uniq_a_b of table `t2`.`t2` trx id 5801 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 80000001; asc ;;
1: len 1; hex 31; asc 1;;
2: len 4; hex 80000001; asc ;;
*** (2) TRANSACTION:
TRANSACTION 5802, ACTIVE 4 sec inserting
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 6, OS thread handle 140528848484096, query id 381 192.168.128.1 root update
insert into t2(a,b) values(0,'0')
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 56 page no 5 n bits 72 index uniq_a_b of table `t2`.`t2` trx id 5802 lock_mode X locks gap before rec
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 80000001; asc ;;
1: len 1; hex 31; asc 1;;
2: len 4; hex 80000001; asc ;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 56 page no 4 n bits 72 index uniq_c of table `t2`.`t2` trx id 5802 lock mode S waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 0; hex ; asc ;;
1: len 4; hex 80000002; asc ;;
*** WE ROLL BACK TRANSACTION (2)
事务2等待的锁由前面的插入意向锁变成了共享锁。什么鬼?
由于没看过源码,只能根据现象倒推:因为表结构上c的唯一索引在(a,b)前面,而插入的时候没指定c的值,用的默认值0,innodb需要先去查一下有没有0这条记录,有的话就要报唯一键冲突了,所以先要加S锁,但是在(0,'0')这条记录上已经有了IX锁,看一下前面的兼容性矩阵,S锁和IX锁互斥,所以也只能锁等待
总结
看似一句简单的select和insert,底下设计非常复杂的锁机制,理解这些锁机制有利于写出高效的SQL(至少是正确的😂)
遗留问题:
意向锁的优化点是哪
锁信息里,行记录按位或上的0x80000000是啥
锁互斥的判定顺序,场景1中,(0,'0')上有兼容的间隙锁,也有等待队列中的锁,先判定哪个?
innodb计算事务回滚代价的算法
参考资料
http://hedengcheng.com/?p=771
https://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html#innodb-insert-intention-locks
https://dev.mysql.com/doc/refman/5.7/en/innodb-next-key-locking.html
https://dev.mysql.com/doc/refman/5.7/en/innodb-information-schema-understanding-innodb-locking.html
来源:https://segmentfault.com/a/1190000019221604
猜你喜欢
- 一、分工和流程在土豆网,以项目开发为核心,谁都可以带项目,担任项目经理。一个典型的土豆网项目中,当进入正式开发阶段,通常参与者包括:1名设计
- selenium主要是用来做自动化测试,支持多种浏览器,爬虫中主要用来解决JavaScript渲染问题。模拟浏览器进行网页加载,当reque
- Python 爬虫图片简单实现经常在逛知乎,有时候希望把一些问题的图片集中保存起来。于是就有了这个程序。这是一个非常简单的图片爬虫程序,只能
- 目录模板的用处模板语法继承和super函数利用jinja2 进行渲染模板的用处jinja2 可以用来修改配置文件,把配置文件的关键地方可以用
- ---- ORACLE数据库由数据文件,控制文件和联机日志文件三种文件组成。由于磁盘空间的变化,或者基于数据库磁盘I/O性能的调
- 作业备份,不是备份数据库,是备份作业。 我的方法是把作业导出成文件备份起来,因为当你服务器维护的多了的时候很多你的作业 就很成问题,很麻烦。
- 今年国庆节也是中秋节,首先祝大家节日快乐,本文我们使用 Python 来给自己的头像加一个小国旗或小月饼。国旗对于国旗,我们可以使用 Pyt
- 本文实例讲述了原生JS实现Ajax通过POST方式与PHP进行交互的方法。分享给大家供大家参考,具体如下:一、代码conn.php<?
- 前言1.装饰器本质是一个语法糖,是对被装饰方法或类进行的功能扩充,是一种面向切面的实现方法2.装饰器可以分成方法装饰器和类装饰器,他们的区别
- 之前画图一直在用matlibplot、pyecharts,最近学习了一个新的可视化库–cufflinks,用了两天我已经深深爱上它了主要是因
- 前言飞桨(PaddlePaddle)是集深度学习核心框架、工具组件和服务平台为一体的技术先进、功能完备的开源深度学习平台1. 任务描述乘坐出
- 目录:分析和设计组件编码实现和算法用 Ant 构建组件测试 JavaScript 组件我们走到哪儿了?前两期思考了太多东西,你是否已有倦意?
- 一、在线程中获取时间,判断当前时间三面之后,触发“事件”对象。 运行结果:二、在另一个线程
- 本文实例讲述了Django rest framework工具包简单用法。分享给大家供大家参考,具体如下:Django rest framew
- <?phphighlight_file(__FILE__);error_reporting(0);$content = $_POST[
- 一、简单优化1.图片纹理大小尽量为2的幂次方(比如128,512,1024等等),以便unity对其进行优化。2.将多个小图片纹理合并一个大
- 本文实例为大家分享了Python实现俄罗斯方块游戏的具体代码,供大家参考,具体内容如下玩法:童年经典,普通模式没啥意思,小时候我们都是玩加速
- 级联查询在ORACLE 数据库中有一种方法可以实现级联查询select * //要查询的字段from table
- 写在最前面:我在研究机器学习的过程中,给的数据集是手写数字图片被处理后的由0,1表达的txt文件,今天写一写关于图片转化为二进制txt文件的
- 来看看javascript怎么实现自动点击超级链接吧,主要使用了js中的onclick事件。这里推荐大家看看这篇文章js鼠标事件大全。看了这