MySQL死锁分析(一)

在针对存在唯一索引的表中并发插入数据时，可能会触发死锁问题，今天我们就一起来探讨该死锁产生的原因。先准备测试案例，创建带有唯一索引的表A

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15


mysql> desc a;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | YES  | UNI | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.01 sec)

mysql> select * from a;
+------+
| id   |
+------+
|    1 |
|    5 |
+------+

接下来开启三个事务，执行下列操作

SESSION 1 SESSION 2 SESSION 3

1 begin;
insert into a select 4;

2 begin;
insert into a select 4; begin;
insert into a select 4;

3 rollback; Deadlock found when trying to get lock

SESSION 1	SESSION 2	SESSION 3
1	begin; insert into a select 4;
2		begin; insert into a select 4;	begin; insert into a select 4;
3	rollback;		Deadlock found when trying to get lock

可以发现在SESSION 1执行rollback的同时，SESSION 3立刻出现死锁并返回。接下来我们结合show engine innodb status中的锁信息来对这个死锁问题进行分析。先把参数innodb_status_output_locks打开，这样才能在show enginge innodb status中查看详细的锁信息。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10


mysql> show variables like '%output_locks';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| innodb_status_output_locks | OFF   |
+----------------------------+-------+
1 row in set (0.01 sec)

mysql> set global innodb_status_output_locks=ON;
Query OK, 0 rows affected (0.00 sec)

三个事务的锁信息如下，其中SESSION 1在插入后占用了ID=4这条记录的X锁，SESSION 2和SESSION 3都在等待ID=4这条记录的S锁，S锁之间不冲突，目前看上去没有什么问题

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44


---TRANSACTION 7714, ACTIVE 2 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1
MySQL thread id 12, OS thread handle 140057782679296, query id 94 localhost root executing
insert into a select 4
------- TRX HAS BEEN WAITING 2 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 70 page no 4 n bits 72 index id of table `test`.`a` trx id 7714 lock mode S waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000004; asc     ;;
 1: len 6; hex 000000000302; asc       ;;

------------------
TABLE LOCK table `test`.`a` trx id 7714 lock mode IX
RECORD LOCKS space id 70 page no 4 n bits 72 index id of table `test`.`a` trx id 7714 lock mode S waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000004; asc     ;;
 1: len 6; hex 000000000302; asc       ;;

---TRANSACTION 7713, ACTIVE 83 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 11, OS thread handle 140057782945536, query id 93 localhost root executing
insert into a select 4
------- TRX HAS BEEN WAITING 6 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 70 page no 4 n bits 72 index id of table `test`.`a` trx id 7713 lock mode S waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000004; asc     ;;
 1: len 6; hex 000000000302; asc       ;;

------------------
TABLE LOCK table `test`.`a` trx id 7713 lock mode IX
RECORD LOCKS space id 70 page no 4 n bits 72 index id of table `test`.`a` trx id 7713 lock mode S waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000004; asc     ;;
 1: len 6; hex 000000000302; asc       ;;

---TRANSACTION 7712, ACTIVE 454 sec
2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1
MySQL thread id 7, OS thread handle 140057783478016, query id 45 localhost root
TABLE LOCK table `test`.`a` trx id 7712 lock mode IX
RECORD LOCKS space id 70 page no 4 n bits 72 index id of table `test`.`a` trx id 7712 lock_mode X locks rec but not gap
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000004; asc     ;;
 1: len 6; hex 000000000302; asc       ;;

但是当SESSION 1执行rollback回退后，SESSION 3出现了死锁情况，我们再查看一下死锁信息

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33


------------------------
LATEST DETECTED DEADLOCK
------------------------
2020-05-14 14:50:49 0x7f61be668700
*** (1) TRANSACTION:
TRANSACTION 7715, ACTIVE 4 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 12, OS thread handle 140057782679296, query id 98 localhost root executing
insert into a select 4
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 70 page no 4 n bits 72 index id of table `test`.`a` trx id 7715 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 80000005; asc     ;;
 1: len 6; hex 000000000301; asc       ;;

*** (2) TRANSACTION:
TRANSACTION 7713, ACTIVE 129 sec inserting
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 1
MySQL thread id 11, OS thread handle 140057782945536, query id 96 localhost root executing
insert into a select 4
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 70 page no 4 n bits 72 index id of table `test`.`a` trx id 7713 lock mode S locks gap before rec
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000005; asc     ;;
 1: len 6; hex 000000000301; asc       ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 70 page no 4 n bits 72 index id of table `test`.`a` trx id 7713 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 80000005; asc     ;;
 1: len 6; hex 000000000301; asc       ;;

我们可以看出来两个事务都因为等待同一行记录的insert intention lock从而导致的死锁，问题在于这里的锁住的记录变成5了，跟预想的不太一样。这一点是因为ID=4的记录回退后，该行记录标记为deleted_mark状态，purge线程从页上清理该记录时，触发了锁继承，next-key继承了它的锁。

因此，这个死锁的产生逻辑就是这样的：

启动SESSION 1并执行insert操作，因为是唯一索引，在ID=4的记录上加了隐藏记录锁
启动SESSION 2和SESSION 3也执行相同的插入操作，发现唯一键冲突，都加上S锁
SESSION 1回退，SESSION 2和SESSION 3尝试进行插入，都要加上insert intention lock，发现两个SESSION都需要等待对方的行锁，所以出现了死锁现象

deadlock