HoneyHoney

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

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

至于分析锁，首先执行

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)

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

1. http://keithlan.github.io/2017/06/21/innodb_locks_algorithms/
2. http://keithlan.github.io/2017/06/05/innodb_locks_1/