在线时间:8:00-16:00
迪恩网络APP
随时随地掌握行业动态
扫描二维码
关注迪恩网络微信公众号
前言 最近遇到一个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,按如下顺序执行两个事务 结果是
共享(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),事务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(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 ;; 表明两行记录
至于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(至少是正确的😂) 遗留问题:
参考资料
总结 以上就是这篇文章的全部内容了,希望本文的内容对大家的学习或者工作具有一定的参考学习价值,谢谢大家对极客世界的支持。 |
请发表评论