【MySQL】死锁案例

---

前言

记录一个死锁案例

---

案例重现

--mysql5.7版本
create table lock_test(
    id int not null primary key,
    a int,
    b int,
    unique key(a),
    index(b));
insert into lock_test values(5,5,5),(10,10,10),(15,15,15);

-- transaction1
begin;
select * from lock_test where id=5 for update;
--transaction2
begin;
select * from lock_test where id=5 for update;
--transaction1
select * from lock_test where b>=5 for update;
--dead lock

分析原因

show engine innodb status得到死锁日志如下

*** (1) TRANSACTION:
TRANSACTION 1305, ACTIVE 24 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 541, OS thread handle 140117470873344, query id 54 localhost root statistics
select * from lock_test where id=5 for update
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 23 page no 3 n bits 72 index PRIMARY of table `test_db`.`lock_test` trx id 1305 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 80000005; asc     ;;
 1: len 6; hex 00000000050f; asc       ;;
 2: len 7; hex ad000001210110; asc     !  ;;
 3: len 4; hex 80000005; asc     ;;
 4: len 4; hex 80000005; asc     ;;

*** (2) TRANSACTION:
TRANSACTION 1304, ACTIVE 29 sec starting index read
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1136, 5 row lock(s)
MySQL thread id 540, OS thread handle 140117604472576, query id 56 localhost root Sending data
select * from lock_test where b>=5 for update
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 23 page no 3 n bits 72 index PRIMARY of table `test_db`.`lock_test` trx id 1304 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 80000005; asc     ;;
 1: len 6; hex 00000000050f; asc       ;;
 2: len 7; hex ad000001210110; asc     !  ;;
 3: len 4; hex 80000005; asc     ;;
 4: len 4; hex 80000005; asc     ;;

Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 8000000a; asc     ;;
 1: len 6; hex 000000000510; asc       ;;
 2: len 7; hex ae000001220110; asc     "  ;;
 3: len 4; hex 8000000a; asc     ;;
 4: len 4; hex 8000000a; asc     ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 23 page no 3 n bits 72 index PRIMARY of table `test_db`.`lock_test` trx id 1304 lock_mode X waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 80000005; asc     ;;
 1: len 6; hex 00000000050f; asc       ;;
 2: len 7; hex ad000001210110; asc     !  ;;
 3: len 4; hex 80000005; asc     ;;
 4: len 4; hex 80000005; asc     ;;

*** WE ROLL BACK TRANSACTION (1)

坑1：select * from lock_test where b>=5 for update索引失效走了全表扫描，全表扫描会给整个表加next-key lock， lock_mode X waiting表示加next-key lock，主键索引上有另一个事务的锁等待，加next-key lock这个操作进入等待队列造成死锁。
坑2：从结果往回推的话是因为next-key lock和记录锁不兼容，不兼容就是指这个事务本身已经持有主键索引上id=5的记录锁了，但是加next-key lock的时候竟然不能插队（即使间隙上没有锁。这里做了个实验把select * from lock_test where b>=5 for update改成select * from lock_test where id<5 for update给(-00,5]加next-key lock同样造成死锁，看源码发现next-key lock能兼容记录锁，反过来不行）。

源码

innoDB是怎么加锁的
1、innoDB中加锁都是对B+树索引加锁的，而且加的锁都是加到某条记录上，例如记录锁就是锁住这行记录，间隙锁就是锁住这行记录前面的间隙，next-key lock就相当于记录锁+间隙锁
2、对上界例如正无穷加锁（索引的两个边界会有一个标记），加的是间隙锁

innoDB的加锁流程：加锁入口在storage > innobase > lock > lock0lock.cc > lock_rec_lock函数中
1、innoDB的锁是用哈希表存起来的，其中key通过block（可以理解为内存块）计算得到。block通过space（表空间id）和page_no（页号）计算哈希值。也就是同一块内存中的记录，它们的锁都放在哈希表的同一个槽里面。
2、通过记录的space和page_no可以找到哈希表对应的槽，然后遍历槽的链表，通过锁的heap_no得到这条记录对应的锁
3、如果记录上没有锁，那直接加锁
4、如果记录上有锁，需要判断已有的锁是否比当前要加的锁更强
4.1、如果有那就do nothing，因为当前事务已经持有了锁，而且锁都是事务结束后才释放
4.2、如果该事务在这个记录上没有比当前要加的锁更强的锁，且这个记录上有别的事务等待加锁，把该事务要加的锁加入队列。没有别的事务等待就返回成功（好像没看到加锁的过程，需要挖个坑再仔细看看）

如何判断锁是否比当前要加的锁更强：
lock_rec_has_expl ：Checks if a transaction has a GRANTED explicit lock on rec stronger or equal
to precise_mode.

这里要那一长串条件都满足才算有更强的锁，感觉只有next-key lock能满足要求了。。。

回到最初的死锁问题：事务1持有记录锁，事务2等待记录锁，当事务1要在这个记录上加next-key lock时，因为记录锁比next-key lock弱，不能兼容，又发现这条记录有别的事务等待加锁，所以事务1的next-key lock要进入等待队列，造成死锁。