当前业务出现死锁情况，其操作流程为批量DELETE再批量INSERT，经过排查发现是由于二级唯一索引在DELETE之后未完全被purge回收，再批量插入时互相等待造成的死锁。这里需要说明的是，当记录被DELETE删除后该行记录会被标识为DELETE_MARK状态，此时并没有真正地被回收，只有等purge线程回收后才是真正意义的被删除。
接下来，我们就在测试环境模拟并分析其中的原因，为了准确复现当时的情形，我们需要以mysqld_debug启动并关闭purge线程
1
2
|
$ mysqld-debug --user=mysql&
root@(none) 16:36: set global innodb_purge_stop_now=1;
|
数据库中存在如下数据表，A列为主键，B列为唯一索引，隔离级别为RC
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
root@test 16:25: desc z;
+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| a | int | NO | PRI | NULL | |
| b | int | YES | UNI | NULL | |
+-------+------+------+-----+---------+-------+
root@test 16:25: select * from z;
+---+------+
| a | b |
+---+------+
| 1 | 10 |
| 2 | 20 |
| 3 | 30 |
| 4 | 40 |
| 5 | 50 |
| 6 | 60 |
| 7 | 70 |
+---+------+
|
先delete删除b=20和b=50的记录，由于已经关闭了purge线程，这两条记录并未真正删除
1
2
3
4
5
|
root@test 16:39: delete from z where b=20;
Query OK, 1 row affected (0.01 sec)
root@test 16:40: delete from z where b=40;
Query OK, 1 row affected (0.01 sec)
|
开启两个事务对表插入下列记录
| TIME |
TX1 |
TX2 |
| 1 |
begin; |
begin; |
| 2 |
insert into z values(8,20); |
insert into z values(9,40); |
| 3 |
insert into z values(10,45); |
insert into z values(11,25); |
通过上诉测试结果，我们可以看到TX2最后一个操作会报死锁退出，但我们这插入的数据并不重复，为什么会出现这么多锁呢。一起来看看show engine innodb status的信息
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
|
LATEST DETECTED DEADLOCK
------------------------
2020-05-31 16:53:04 0x7f4af9c67700
*** (1) TRANSACTION:
TRANSACTION 6303, ACTIVE 77 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1160, 4 row lock(s), undo log entries 2
MySQL thread id 81, OS thread handle 139959995107072, query id 150 localhost root update
insert into z values(10,45)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 31 page no 4 n bits 80 index b of table `test`.`z` trx id 6303 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 6 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 80000032; asc 2;;
1: len 4; hex 80000005; asc ;;
*** (2) TRANSACTION:
TRANSACTION 6308, ACTIVE 69 sec inserting, thread declared inside InnoDB 5000
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1160, 5 row lock(s), undo log entries 2
MySQL thread id 88, OS thread handle 139959994840832, query id 152 localhost root update
insert into z values(11,25)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 31 page no 4 n bits 80 index b of table `test`.`z` trx id 6308 lock mode S
Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
0: len 4; hex 80000028; asc (;;
1: len 4; hex 80000004; asc ;;
Record lock, heap no 6 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 80000032; asc 2;;
1: len 4; hex 80000005; asc ;;
Record lock, heap no 7 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 8000003c; asc <;;
1: len 4; hex 80000006; asc ;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 31 page no 4 n bits 80 index b of table `test`.`z` trx id 6308 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 8000001e; asc ;;
1: len 4; hex 80000003; asc ;;
*** WE ROLL BACK TRANSACTION (2)
|
TX1在等待b=60上的gap lock，而TX2中info bits 32则是我们已经删除的b=40那条记录，但并没有真正回收该记录，重新插入时会在该记录上添加S-Lock，并且对b=50和b=60的记录也添加了S-Lock，TX2则在等待B=30上的gap lock，而TX1持有该锁。因此，出现了互相等待对方的锁，从而导致死锁发生。
这里业务采用并发插入，插入是根据主键顺序组织的插入的，容易造成这种死锁情况，可以针对B列进行排序，按照B列索引顺序插入。