记一次工作中数据库死锁问题定位与解决

最近在工作中遇到了几次类似的数据库死锁的问题，通过比对死锁日志文件和代码，最终分析出了死锁的原因，并将之解决，因此写下此文作简单的经验记录。

一、问题背景

最近在工作中遇到了几次类似的数据库死锁的问题，比如这条告警，提示在更新某张表的时候出现了死锁。

为了定位和解决这个问题，于是找 DBA 要死锁日志进行分析。在此之前，先回顾什么是死锁，死锁是怎么产生的，以及可以怎么解决。

二、快速认识死锁

在操作系统中，如果系统中只有一个进程，当然不会产生死锁。如果每个进程仅需求一种系统资源，也不会产生死锁。不过这只是理想状态，在现实中是可遇不可求的。

死锁的四个条件是：

禁止抢占（no preemption）：系统资源不能被强制从一个进程中退出。
持有和等待（hold and wait）：一个进程可以在等待时持有系统资源。
互斥（mutual exclusion）：资源只能同时分配给一个行程，无法多个行程共享。
循环等待（circular waiting）：一系列进程互相持有其他进程所需要的资源。

死锁只有在四个条件同时满足时发生，预防死锁必须至少破坏其中一项。在数据库中，可以认为产生死锁是由于不同的线程间竞争资源且满足类似上述的四个条件。

三、死锁日志分析

为了不提及公司相关信息，我对场景进行了迁移和还原。在一个仓储自动化系统中，为了提升发货效率，很基本地，出库作业会主要应用集分思想，即多个订单集中在一个作业区域拣货，然后再按照订单或包裹等其他维度进行分发，而多个订单一起作业期间往往会设置一个作业号，用于标记整个作业流程，当然子作业流程也会有相应的标识。另外，作业期间涉及很多作业容器，比如拣集到的商品会放到一个箱子里面去，然后分发时再从这个箱子里面的商品分到多个不同的小箱子里面去，然后再进行后续的打包发货流程，如果想深入了解可以查阅相关资料。我们假设有这么两张表：

 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


CREATE TABLE `pick_box` (
	`id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
	`pick_box_code` varchar(50) NOT NULL DEFAULT '' COMMENT '箱号',
	`pick_box_status` tinyint(4) NOT NULL DEFAULT '0' COMMENT '状态，1拣集中，2拣集完成，3分发中，4分发完成',
	`global_flow_code` varchar(50) NOT NULL DEFAULT '' COMMENT '全局作业流水号',
	`pick_flow_code` varchar(50) NOT NULL DEFAULT '' COMMENT '拣集作业流水号',
	`pick_start_time` datetime DEFAULT NULL COMMENT '拣集开始时间',
  `pick_end_time` datetime DEFAULT NULL COMMENT '拣集结束时间',
	`dispersal_start_time` datetime DEFAULT NULL COMMENT '分发开始时间',
  `dispersal_end_time` datetime DEFAULT NULL COMMENT '分发结束时间',
	`dispersal_num` int(11) NOT NULL DEFAULT '0' COMMENT '分发数',
	`total_goods_num` int(11) NOT NULL DEFAULT '0' COMMENT '总件数',
	`is_done` tinyint(1) NOT NULL DEFAULT '0' COMMENT '是否完成  1 是 0 否',
	`is_delete` tinyint(1) NOT NULL DEFAULT '0' COMMENT '是否删除标记0 未删除 1 已删除',
	PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=COMPACT COMMENT='拣集箱';

CREATE TABLE `dispersal_box` (
	`id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
	`dispersal_box_code` varchar(50) NOT NULL DEFAULT '' COMMENT '箱号',
	`dispersal_box_status` tinyint(4) NOT NULL DEFAULT '0' COMMENT '状态，1分发中，2分发完成',
	`dispersal_num` int(11) NOT NULL DEFAULT '0' COMMENT '分发数',
	`global_flow_code` varchar(50) NOT NULL DEFAULT '' COMMENT '全局作业流水号',
	`dispersal_flow_code` varchar(50) NOT NULL DEFAULT '' COMMENT '分发作业流水号',
	`dispersal_start_time` datetime DEFAULT NULL COMMENT '分发开始时间',
  `dispersal_end_time` datetime DEFAULT NULL COMMENT '分发结束时间',
	`is_delete` tinyint(1) NOT NULL DEFAULT '0' COMMENT '是否删除标记0 未删除 1 已删除',
	PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=COMPACT COMMENT='分发箱';

在这个已知条件下我们开始分析死锁文件，首先看到有这么一个事务，事务 ID 是 24680，它在等待 pick_box 表上的一行记录的释放：

 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


*** (1) TRANSACTION:
TRANSACTION 24680, ACTIVE 24 sec starting index read
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 39, OS thread handle 2580, query id 916 localhost 127.0.0.1 root updating
UPDATE pick_box
SET dispersal_num = dispersal_num + 1
WHERE
	id = 1
AND is_delete = 0
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 135 page no 3 n bits 72 index PRIMARY of table `demo`.`pick_box` trx id 24680 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 15; compact format; info bits 0
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 00000000604f; asc     `O;;
 2: len 7; hex 35000001471f11; asc 5   G  ;;
 3: len 9; hex 504243777475777775; asc PBCwtuwwu;;
 4: len 1; hex 80; asc  ;;
 5: len 13; hex 47464332303231303430353031; asc GFC2021040501;;
 6: len 15; hex 504643323032313034303530313031; asc PFC202104050101;;
 7: len 5; hex 99a94b458d; asc   KE ;;
 8: len 5; hex 99a94b558d; asc   KU ;;
 9: len 5; hex 99a94b658d; asc   Ke ;;
 10: SQL NULL;
 11: len 4; hex 80000003; asc     ;;
 12: len 4; hex 80000004; asc     ;;
 13: len 1; hex 80; asc  ;;
 14: len 1; hex 80; asc  ;;

另外有一个事务，事务 ID 是 24679，它持有了 pick_box 上的主键锁，同时它又尝试通过全局作业流水号去把整个作业流水下的分发箱全部更新成完成，但是发现有记录被锁住了，需要授权（GRANTED）拿到锁才能进行操作。

 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
45
46
47
48


*** (2) TRANSACTION:
TRANSACTION 24679, ACTIVE 30 sec fetching rows, thread declared inside InnoDB 4999
mysql tables in use 1, locked 1
5 lock struct(s), heap size 1136, 3 row lock(s)
MySQL thread id 38, OS thread handle 4516, query id 920 localhost 127.0.0.1 root updating
UPDATE dispersal_box
SET dispersal_box_status = 2, dispersal_end_time = now()
WHERE
	(
		dispersal_box_status = 1
		AND global_flow_code = 'GFC2021040501'
		AND is_delete = 0
	)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 135 page no 3 n bits 72 index PRIMARY of table `demo`.`pick_box` trx id 24679 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 15; compact format; info bits 0
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 00000000604f; asc     `O;;
 2: len 7; hex 35000001471f11; asc 5   G  ;;
 3: len 9; hex 504243777475777775; asc PBCwtuwwu;;
 4: len 1; hex 80; asc  ;;
 5: len 13; hex 47464332303231303430353031; asc GFC2021040501;;
 6: len 15; hex 504643323032313034303530313031; asc PFC202104050101;;
 7: len 5; hex 99a94b458d; asc   KE ;;
 8: len 5; hex 99a94b558d; asc   KU ;;
 9: len 5; hex 99a94b658d; asc   Ke ;;
 10: SQL NULL;
 11: len 4; hex 80000003; asc     ;;
 12: len 4; hex 80000004; asc     ;;
 13: len 1; hex 80; asc  ;;
 14: len 1; hex 80; asc  ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 136 page no 3 n bits 72 index PRIMARY of table `demo`.`dispersal_box` trx id 24679 lock_mode X waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 11; compact format; info bits 0
 0: len 4; hex 80000002; asc     ;;
 1: len 6; hex 000000006068; asc     `h;;
 2: len 7; hex 46000001c70d0c; asc F      ;;
 3: len 10; hex 44424365716571723032; asc DBCeqeqr02;;
 4: len 1; hex 81; asc  ;;
 5: len 4; hex 80000002; asc     ;;
 6: len 13; hex 47464332303231303430353031; asc GFC2021040501;;
 7: len 15; hex 444643323032313034303530313032; asc DFC202104050102;;
 8: len 5; hex 99a94a334e; asc   J3N;;
 9: SQL NULL;
 10: len 1; hex 80; asc  ;;

*** WE ROLL BACK TRANSACTION (2)

现在问题大致清晰了，上述死锁产生的场景很可能如下面的时序图所示：

事务 1（24680）	事务 2（24679）	备注
	（1）？	事务 2 持有 `pick_box` 的主键锁
（2）？		根据产生死锁的四个必要条件，事务 1 必然锁住了 `dispersal_box` 的某些数据
（3）`UPDATE pick_box SET dispersal_num = dispersal_num + 1 WHERE id = 1 AND is_delete = 0;`		事务 1 等待事务 2 加在 `pick_box` 上 id 为 1 的记录的行锁释放
	（4） `UPDATE dispersal_box SET dispersal_box_status = 2, dispersal_end_time = now() WHERE (dispersal_box_status = 1 AND global_flow_code = 'GFC2021040501' AND is_delete = 0);`	事务 2 等待事务 1 在 `dispersal_box` 上加的锁，成环，死锁产生

根据产生死锁的四个必要条件，现在需要确认（1）和（2）处的 SQL 执行了什么操作，初步估计

（1）处事务 2 根据主键对 pick_box表进行更新并拿到锁；
（2）处事务 1 根据主键对 dispersal_box 表进行更新并拿到锁；
（3）处事务 2 尝试通过波次号更新 dispersal_box 的多条记录，但其中某条记录在（2）处被事务 1 锁住，所以事务 2 开始等待事务 1 释放锁；
（4）处事务 1 尝试获取 pick_box的主键锁，但是（1）处已经锁住了其中的某条记录，所以事务 1 开始等待事务 2 释放锁，于是陷入死锁。

四、代码定位

根据上述的分析和告警的日志记录的 trace 信息，我发现下面这两个事务的这两行代码的嫌疑很大，为了不泄露公司代码，只给出大概的伪码，其中一个事务（很可能就是上述的事务 1）根据 pickBoxId 去更新 pick_box 这张表，同时前面有更新 dispersal_box 的操作。

1
2
3
4
5
6
7
8


@Transaction
@Override
public void doDisperse(...) {
    // ...
    increaseDispersalNum();
    pickBoxService.increaseDispersalNum(pickBoxId);
    // ...
}

另外一个事务则先更新 pick_box 的数据，然后根据全局作业流水号去更新 dispersal_box ，这样看这个事务很可能就是事务 2。

1
2
3
4
5
6
7
8


@Transaction
@Override
public void forceDisperse2Finish(...) {
    // ...
    finishPickBox(pickBox);
    dispersalBoxService.finishAllBoxes(globalFlowCode);
    // ...
}

结合代码和死锁文件，现在已经很清晰了，上面的时序图可以填完完整了：

事务 1（24680）	事务 2（24679）	备注
	（1）`UPDATE pick_box SET pick_box_status = 4 AND dispersal_end_time = now() AND is_done = 1 WHERE id = 1 AND is_delete = 0 AND pick_box_status = 3;`	事务 2 持有 `pick_box` 的主键锁
（2）`UPDATE dispersal_box SET dispersal_num = dispersal_num + 1 WHERE id = 2;`		根据产生死锁的四个必要条件，事务 1 必然锁住了 `dispersal_box` 的某些数据
（3）`UPDATE pick_box SET dispersal_num = dispersal_num + 1 WHERE id = 1 AND is_delete = 0;`		事务 1 等待事务 2 加在 `pick_box` 上 id 为 1 的记录的行锁释放
	（4） `UPDATE dispersal_box SET dispersal_box_status = 2, dispersal_end_time = now() WHERE (dispersal_box_status = 1 AND global_flow_code = 'GFC2021040501' AND is_delete = 0);`	事务 2 等待事务 1 在 `dispersal_box` 上加的锁，成环，死锁产生

五、场景复现

尽管如此，上面的场景也仅仅是我的猜测而已，那么实际上这样是不是真的会出现死锁呢？百看不如一干，我们不妨来造个数据复现一下：

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


INSERT INTO `pick_box` (
	`id`, `pick_box_code`, `pick_box_status`, `global_flow_code`, `pick_flow_code`,
	`pick_start_time`, `pick_end_time`, `dispersal_start_time`, `dispersal_end_time`,
	`dispersal_num`, `total_goods_num`, `is_done`, `is_delete`
) VALUES 
( '1', 'PBCwtuwwu', '3', 'GFC2021040501', 'PFC202104050101', '2021-04-05 20:22:13', '2021-04-05 21:22:13',
    '2021-04-05 22:22:13', null, '0', '4', '0', '0');
    
INSERT INTO `dispersal_box` (
	`id`, `dispersal_box_code`, `dispersal_box_status`, `dispersal_num`, `global_flow_code`,
	`dispersal_flow_code`, `dispersal_start_time`, `dispersal_end_time`, `is_delete`
) VALUES 
(1, 'DBCeqeqr01', 2, '2', 'GFC2021040501', 'DFC202104050101', '2021-04-05 02:13:14', '2021-04-05 02:14:14', '0'),
(2, 'DBCeqeqr02', 1, '1', 'GFC2021040501', 'DFC202104050102', '2021-04-05 03:13:14', null, '0');

通过命令 SET autocommit=0; 开启手动提交事务：然后开启两个连接进行测试，连接 1 开启事务，按顺序执行下面的 SQL，执行完第三步，先不提交。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19


START TRANSACTION;
#1
UPDATE pick_box
SET pick_box_status = 4
AND dispersal_end_time = now()
AND is_done = 1
WHERE
	id = 1
AND is_delete = 0
AND pick_box_status = 3;
#4
UPDATE dispersal_box
SET dispersal_box_status = 2, dispersal_end_time = now()
WHERE
	(
		dispersal_box_status = 1
		AND global_flow_code = 'GFC2021040501'
		AND is_delete = 0
	);

连接 2 开启事务，按顺序执行下面的 SQL 语句：

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


START TRANSACTION;
#2
UPDATE dispersal_box
SET dispersal_num = dispersal_num + 1
WHERE
	id = 2;
#3
UPDATE pick_box
SET dispersal_num = dispersal_num + 1
WHERE
	id = 1
AND is_delete = 0;

执行完第四步，死锁复现。

六、问题解决

说到底就是破坏死锁的四个必要条件中的一个，首先想到的是可以对事务中更新数据的顺序进行调整，比如将事务 1 的（2）和（3）处调换顺序，那么如果事务 1 先执行，那么事务 2 的（1）处将需要等待（2）处的锁释放才能进行（4）处的操作；同理如果事务 2 先执行，那么事务 1 也需要先等待事务 2 执行完（1）才能进行（3）处的操作，这就相当于破除了死锁的四个必要条件中的 “持有和等待” 以及 “循环等待” 这两点。

事务 1（24680）	事务 2（24679）	备注
	（1）`UPDATE pick_box SET pick_box_status = 4 AND dispersal_end_time = now() AND is_done = 1 WHERE id = 1 AND is_delete = 0 AND pick_box_status = 3;`	事务 2 持有 `pick_box` 的主键锁
（2）`UPDATE pick_box SET dispersal_num = dispersal_num + 1 WHERE id = 1 AND is_delete = 0;`		根据产生死锁的四个必要条件，事务 1 必然锁住了 `dispersal_box` 的某些数据
（3）`UPDATE dispersal_box SET dispersal_num = dispersal_num + 1 WHERE id = 2;`		事务 1 等待事务 2 加在 `pick_box` 上 id 为 1 的记录的行锁释放
	（4） `UPDATE dispersal_box SET dispersal_box_status = 2, dispersal_end_time = now() WHERE (dispersal_box_status = 1 AND global_flow_code = 'GFC2021040501' AND is_delete = 0);`	事务 2 等待事务 1 在 `dispersal_box` 上加的锁，成环，死锁产生

另外的方法是可以与产品协商是否可以在程序中加分布式锁控制，同一时刻只能允许上述两个业务中的一个进行操作，这样其实相当于在一个事务中把两个资源作为一个进行操作，而且事务不并发。