一个mysql死锁场景分析

共计 6143 个字符，预计需要花费 16 分钟才能阅读完成。

最近遇到一个 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 锁。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)，但是由于间隙锁相互兼容，所以没有 block
A 执行 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。至此，死锁过程分析完毕

还没完。。。有个神奇的现象是，如果表结构变成

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

场景

共享 (S) 锁 / 互斥 (X) 锁

意向锁

行锁

间隙锁

插入意向锁

死锁过程分析

One More Thing

总结

参考资料

Just My Socks（注册教程内含优惠码）