数据库

一个mysql死锁场景实例分析
数据库 2024/12/27 佚名
3 2

前言

最近遇到一个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，按如下顺序执行两个事务

一个mysql死锁场景实例分析

结果是

  • 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的锁优化策略，并不是很清楚意向锁的优化点在哪里，求大佬指教

两种锁的兼容情况如下

一个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')之前的间隙锁，却也在等待那个插入意向锁。这说的什么玩意儿，是不是很诡异？

从头开始分析过程

  1. A、B分别begin，开始事务
  2. A先执行select * from t where a=0 and b='0' for update; ，先加了IX锁，然后原本意图为给(0, '0')这条记录加排他行锁，但是记录不存在，所以变成了排他间隙锁(-∞,1)
  3. B再执行select * from t where a=0 and b='0' for update; ，也是先加了IX锁，因为记录不存在，所以加上了排他间隙锁(-∞,1)，但是由于间隙锁相互兼容，所以没有block
  4. A执行insert into t(a,b) values(0,'0'); ，这时候，要开始真正insert了，A需要获得(0,'0')上的插入意向锁，由于和B持有的(-∞,1)排他间隙锁冲突，所以锁等待，进入记录(0,'0')的锁等待队列（虽然记录并不存在）
  5. B执行insert into t(a,b) values(0,'0'); ，要获取插入意向锁，发现虽然B自己是持有(-∞,1)的排他间隙锁，但是A也有，所以进入等待队列，等待A释放
  6. 叮，死锁发生

死锁信息解读

事务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(至少是正确的"color: #ff0000">总结

以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作具有一定的参考学习价值，谢谢大家对的支持。

标签：
mysql死锁,mysql死锁产生原因,mysql死锁例子
免责声明：本站文章均来自网站采集或用户投稿，网站不提供任何软件下载或自行开发的软件！ 如有用户或公司发现本站内容信息存在侵权行为，请邮件告知！ 858582#qq.com
无争山庄资源网 Copyright www.whwtcm.com

评论“一个mysql死锁场景实例分析”

暂无“一个mysql死锁场景实例分析”评论...
www.whwtcm.com 无争山庄资源网
129,905影音资源
244,626技术资源
111,817软件资源
578,645站长资源

更新日志

友情链接

杰晶网络 DDR爱好者之家 桃源资源网 杰网资源 富贵资源网 南强小屋 铁雪资源网 幽灵资源网 万梅资源网 狼山资源网 白云岛资源网 昆仑资源网 相思资源网 明霞山资源网 内蒙古资源网 黑松山资源网 茶园资源网 饿虎岗资源网 大旗谷资源网 常春岛资源网 岱庙资源网 兴国资源网 快活林资源网 蝙蝠岛资源网 帝王谷资源网 白云城资源网 伏龙阁资源网 清风细雨楼 天枫庄资源网 圆月山庄资源网 无争山庄资源网 神水资源网 移花宫资源网 神剑山庄资源网 无为清净楼资源网 金钱帮资源网 丐帮资源网 华山资源网 极乐门资源网 小李飞刀资源网 凤求凰客栈 风云阁资源网 金狮镖局 鸳鸯亭资源网 千金楼资源网 更多链接
无争山庄资源网 Copyright www.whwtcm.com