MySQL的一次死锁分析

之前分析过SQL SERVER的死锁，但基本都是基于READ COMMITTED下的死锁。玩得高级点的，就是key lookup lock。最近不幸玩了MySQL，拿原来的理解去尝试分析，结果不对，然后才发现，MySQL的默认隔离级别是REPEATABLE READ。

在RR级别下，除了常规的RECORD LOCK，还有一个GAP LOCK。即两条记录之前的间隙。这样的话，就不会允许在范围内插入数据了。这里有个很好的例子去模拟死锁。

至于分析锁，首先执行

set global innodb_status_output_locks=on;

然后，再执行

SHOW ENGINE INNODB STATUS \G

就可以拿到所有session的锁了。

-- session 1
mysql> start transaction;
mysql> delete from game_summaries where game_id = 2;

-- session 2
mysql> start transaction;
mysql> delete from game_summaries where game_id = 3;

-- session 1
mysql> insert into game_summaries(game_id, score) values (2, 0);
-- waiting

-- session 2
mysql> insert into game_summaries(game_id, score) values(3, 0);
-- deadlock occurs

Deadlock info

------------------------
LATEST DETECTED DEADLOCK
------------------------
2018-02-11 02:19:51 0x7ff5b7b83700
*** (1) TRANSACTION:
TRANSACTION 365986, ACTIVE 59 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 16, OS thread handle 140693325752064, query id 1184 172.18.0.1 root update
insert into game_summaries(game_id, score) values (2, 0)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 3445 page no 4 n bits 72 index index_game_summaries_on_game_id of table `TEST`.`game_summaries` trx id 365986 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000009; asc     ;;
 1: len 4; hex 80000002; asc     ;;

*** (2) TRANSACTION:
TRANSACTION 365987, ACTIVE 45 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 17, OS thread handle 140693326018304, query id 1186 172.18.0.1 root update
insert into game_summaries(game_id, score) values(3, 0)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 3445 page no 4 n bits 72 index index_game_summaries_on_game_id of table `TEST`.`game_summaries` trx id 365987 lock_mode X locks gap before rec
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000009; asc     ;;
 1: len 4; hex 80000002; asc     ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 3445 page no 4 n bits 72 index index_game_summaries_on_game_id of table `TEST`.`game_summaries` trx id 365987 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000009; asc     ;;
 1: len 4; hex 80000002; asc     ;;

*** WE ROLL BACK TRANSACTION (2)

然后就按照下面两篇文章去分析锁就行了。

如果你有SQL SERVER的背景知识，简单来说，就是基本的record lock（以及相关的index），加上gap lock。一旦有gap lock，这个范围内是不允许插入数据的。gap lock是shared的，也就是说，不同transaction可以在同样的范围内加多个gap lock。

It is also worth noting here that conflicting locks can be held on a gap by different transactions. For example, transaction A can hold a shared gap lock (gap S-lock) on a gap while transaction B holds an exclusive gap lock (gap X-lock) on the same gap. The reason conflicting gap locks are allowed is that if a record is purged from an index, the gap locks held on the record by different transactions must be merged.

所以前两个delete语句在index_game_summaries_on_game_id上加了gap lock，后面的insert都被block住了。

这就增加了死锁发生的几率。这种情况更多是发生在DELETE & INSERT 组合情况下。在上面的例子里面，两个delete statement在非聚集索引index_game_summaries_on_game_id所加的gap lock是不会相互冲突的。但是会阻止后续的插入。