MySQL死锁 - 更新插入导致死锁

1. 问题背景

前段时间，业务量大涨，团队需要扩展，招聘过程中经常遇到关于MySQL死锁的问题。许多面试中也涉及类似问题，例如如何分析MySQL死锁及其解决方案。本文将以一个实际案例来分析MySQL死锁的原因和解决方法。

2. 线上问题

2.1 线上异常日志

在错误日志中，我们发现死锁问题。通过排查业务代码，我们确定了问题：在业务逻辑中，先更新历史的回收单状态为失效，再插入新的回收单数据。如果并发执行类似操作，可能会导致死锁。

2.2 数据准备

在测试库中创建一个表，并插入测试数据。

CREATE TABLE `checkout_detail` (
  `id` bigint(20) NOT NULL COMMENT '主键id',
  `recycle_order_id` bigint(20) NOT NULL COMMENT '回收单ID',
  `confirm_recycle_time` datetime NOT NULL COMMENT '确认回收时间',
  `contrast_type` int(4) NOT NULL COMMENT '对比类型：1:售前、2:后验、3:售后',
  `remark` varchar(255) DEFAULT '' COMMENT '备注',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_idx_recycle_order_id_contrast_type` (`recycle_order_id`,`contrast_type`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='后验详情表';

插入几条测试数据：

INSERT INTO checkout_detail (id, recycle_order_id, confirm_recycle_time, contrast_type, remark) VALUES
(1, 1, '2024-07-15 19:56:01', 1, '回收单1'),
(2, 10, '2024-07-15 19:56:01', 2, '回收单10'),
(3, 20, '2024-07-15 19:56:01', 3, '回收单20');

2.3 问题复现

执行如下操作可以模拟死锁：

2.3.1 执行流程

执行顺序	事务A	事务B
START	`START TRANSACTION;`	`START TRANSACTION;`
1	`UPDATE checkout_detail SET remark = '更新状态' WHERE recycle_order_id = 30;`
2		`UPDATE checkout_detail SET remark = '更新状态' WHERE recycle_order_id = 40;`
3	`INSERT INTO checkout_detail (id, recycle_order_id, confirm_recycle_time, contrast_type, remark) VALUES(30, 30, '2024-07-15 19:56:01', 1, '插入回收单30');`
4		`INSERT INTO checkout_detail (id, recycle_order_id, confirm_recycle_time, contrast_type, remark) VALUES(40, 40, '2024-07-15 19:56:01', 1, '插入回收单40');`

2.3.2 死锁排查

执行第三步时，会发生锁等待，执行第四步时会发生死锁。我们可以使用以下SQL查看死锁日志：

SHOW ENGINE INNODB STATUS;

2.3.3 死锁日志分析

我们重点分析日志中锁的信息：

事务A23087：在插入数据时，事务A持有针对uniq_idx_recycle_order_id_contrast_type索引的排他锁，但在等待一个插入意向锁。
事务B23088：事务B与事务A的情况类似，它们在不同的索引位置上都持有插入意向锁，最终导致死锁。

3. 分析原因

通过对MySQL锁机制的理解，死锁主要由间隙锁和插入意向锁的冲突导致。间隙锁防止多个事务同时在同一范围内插入数据，而插入意向锁与间隙锁互斥，导致事务相互等待，最终形成死锁。

4. 解决方法

先查询再更新或插入：在执行插入操作之前，先查询数据是否存在。如果存在，则进行更新操作；如果不存在，再进行插入操作。这可以减少锁的范围，避免不必要的锁冲突。
避免大范围数据修改：尽量减少每次事务中修改的数据量，减少锁定的范围。
合理使用索引：确保SQL语句能够使用适当的索引，减少锁定的行数。