0%

MySQL死锁分析(二)

当前业务出现死锁情况,其操作流程为批量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列索引顺序插入。