insert加update死锁了？-终于明白原因了

死锁问题排查

今天早上，生产环境出现了 DEADLOCK 问题，我们立刻开始排查。由于同事之前没有遇到类似问题，一时间有些无从下手。首先，我们尽可能多地获取相关信息，使用以下 SQL 查询死锁信息：

SHOW ENGINE INNODB STATUS;

我们使用的是 MySQL，事务隔离级别为 可重复读 (REPEATABLE READ)。

需要说明的是(我们目前是老版本)：

• 在 MySQL 8.0.18 及之后，查询结果包括两个事务各自 持有的锁 和 等待的锁。
• 在 MySQL 8.0.18 之前，查询结果仅包含 事务 1 等待的锁 以及 事务 2 持有的锁、等待的锁，但不包括 事务 1 持有的锁。

1. 死锁信息分析

查询结果显示的主要死锁信息如下：

------------------------
LATEST DETECTED DEADLOCK
------------------------
2024-01-10 16:38:00 0x7ff2d09aa700
*** (1) TRANSACTION:
TRANSACTION 13298670, ACTIVE 0 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 2
MySQL thread id 1505446, OS thread handle 140680854284032, query id 404266385 limit update
INSERT INTO lmt_act ...
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 313 page no 9 n bits 328 index UK_lmt_act of table `db`.`lmt_act` trx id 13298670 lock mode S waiting
Record lock, heap no 260 PHYSICAL RECORD: n_fields 9; compact format; info bits 0

*** (2) TRANSACTION:
TRANSACTION 13298669, ACTIVE 0 sec fetching rows
mysql tables in use 1, locked 1
116 lock struct(s), heap size 24784, 6 row lock(s), undo log entries 4
MySQL thread id 1505447, OS thread handle 140680858609408, query id 404266394 10.224.128.131 limit updating
UPDATE lmt_act SET avi_amt = avi_amt - 1480.00 WHERE limit_act = '' and ...
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 313 page no 9 n bits 328 index UK_lmt_act of table `db`.`lmt_act` trx id 13298669 lock_mode X locks rec but not gap
Record lock, heap no 260 PHYSICAL RECORD: n_fields 9; compact format; info bits 0

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 313 page no 270 n bits 168 index PRIMARY of table `db`.`lmt_act` trx id 13298669 lock_mode X locks rec but not gap waiting
Record lock, heap no 95 PHYSICAL RECORD: n_fields 24; compact format; info bits 0

2. 初步定位

我们查看了 SQL 语句对应的表结构，基本可以确认 死锁的根本原因是 UPDATE 语句的 WHERE 条件缺少索引，导致全表锁。

表结构示意如下：

字段	是否索引
id	自增主键
limit	额度标志(客户号)
act	账号
limit_act	额度标志拼接账号
avi_amt	可用额度
used_amt	已用额度

其中：

• 唯一索引 (UK_lmt_act)：(limit, act)
• limit_act 无索引（但被 UPDATE 语句作为 WHERE 条件使用）
• 为什么要增加一个limit_act字段，个人认为没什么用

3. 为什么会发生死锁？

代码逻辑是一个 限额控制 相关的流程，核心逻辑如下：

尝试插入日限额
--> 插入成功，则更新年限额
--> 插入失败（已存在），先更新日限额，再更新年限额

存在优化空间：
目前的逻辑可能导致性能下降。实际上，每个客户每天仅会有一次 INSERT 操作，因此可以优化为：

1. 优先尝试 UPDATE
2. 若 UPDATE 影响行数为 0，再尝试 INSERT
3. 若 INSERT 失败（唯一键冲突），则再次 UPDATE

3.1 事务执行分析

从 INNODB STATUS 可以看出，事务 1 (INSERT) 和事务 2 (UPDATE) 之间的锁冲突如下：

1. 事务 1 (INSERT)

   • 需要 获取 UK_lmt_act 唯一索引的 S 锁 进行唯一性检查。
   • 由于 INSERT 成功，事务 1 已持有 PrimaryKey 的 X 锁。
   • 但事务 2 早已持有 UK_lmt_act 的 X 锁，因此事务 1 等待 UK_lmt_act 的 S 锁。

2. 事务 2 (UPDATE)

   • 持有 UK_lmt_act 的 X 锁（因为事务 2 先 INSERT 成功）。
   • 由于 UPDATE 语句没有使用索引，导致 MySQL 需要锁住整个表。
   • UPDATE 需要 获取 PrimaryKey 的 X 锁，但事务 1 早已持有该锁，导致死锁。 执行示意图如下：

时间\事务	事务1(insert)	事务2(insert+update)
T1		insert成功:id=2索引加X锁;UK_lmt_act索引加X锁(其中两个字段值分别为111,888)
T2	id=3索引加X锁
T3		无索引update，给所有记录主键加锁，等待给id=3索引加锁
T4	检查limit_act索引唯一约束， UK_lmt_act存在相同键值(两字段同样为111,888)且有锁，等待加S锁

其中两个事务的sql分别为：

事务一：
insert into table_limt(limit, act, limit_act, avi_amt, used_amt, total_amt)values('111', '888', '111_888', 9999, 0)

事务二:
insert into table_limt(limit, act, limit_act, avi_amt, used_amt)values('111', '888', '111_888', 9999, 0);

update table_limit set avi_amt= avi_amt-1000, used_amt=used_amt+1000 where limit_act='111_888' and avi_amt>1000

4. 结论

本次死锁的核心问题：

1. UPDATE 语句 WHERE 条件缺少索引，导致 全表锁。
2. INSERT 触发唯一索引 (UK_lmt_act) 约束检查，等待 S 锁，而 UPDATE 事务已持有 X 锁。
3. 事务 2 (UPDATE) 需要 PrimaryKey X 锁，但事务 1 (INSERT) 已持有该锁，最终导致死锁。

优化方案

• 给 limit_act 字段加索引，避免 UPDATE 造成全表锁。
• 优化 INSERT + UPDATE 逻辑，优先尝试 UPDATE，避免不必要的 INSERT 操作。