0%

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执行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继承了它的锁。

因此,这个死锁的产生逻辑就是这样的:

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

deadlock