0
Posted on Monday, January 22, 2018 by 醉·醉·鱼 and labeled under
之前分析过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/
如果你有SQL SERVER的背景知识,简单来说,就是基本的record lock(以及相关的index),加上gap lock。一旦有gap lock,这个范围内是不允许插入数据的。这就增加了死锁发生的几率。这种情况更多是发生在DELETE & INSERT 组合情况下。
在上面的例子里面,两个delete statement所加的gap lock是不会相互冲突的。但是会阻止后续的插入。