MySQL update where 无索引导致死锁：日志分析与复现解决

故障现象与日志获取

在生产环境中出现“业务不正常，死锁异常日志输出”的情况时，首先需要冷静定位。执行以下命令获取最新的死锁详细报告：

SHOW ENGINE INNODB STATUS;

输出的报告中 LATEST DETECTED DEADLOCK 部分是核心分析对象。通常按以下逻辑梳理：查看涉及死锁的两个事务分别持有了什么锁、在等待什么锁，以及根据回滚的事务判断冲突点。

第1步：解析事务（一）信息

在日志中找到被标记为 (1) TRANSACTION 的部分。以实际案例为例：

• 执行的 SQL：UPDATE ... SET ... WHERE BATCH_NO = 'xxx' AND SFSB = '1'。注意这里的查询字段没有建立唯一索引。
• 持有的锁：日志显示 HOLDS THE LOCK(S)，其指向了主键索引上的特定记录（如 supremum 虚拟最大记录）。
• 等待的锁：状态为 WAITING FOR THIS LOCK TO BE GRANTED，试图获取其他记录的 X 锁。

第2步：解析事务（二）信息

找到被标记为 (2) TRANSACTION 的部分：

• 执行的 SQL：INSERT INTO ... VALUES ...。
• 持有的锁：持有了一条新插入记录的 Record Locks。
• 等待的锁：试图获取 insert intention locks（意向插入锁）并等待间隙锁。

加锁机制深度分析

要理解死锁原因，必须明确 InnoDB 在默认事务隔离级别下的行为。REPEATABLE READ（可重复读）是 MySQL 的默认隔离级别。

在此级别下：

1. 如果查询命中 唯一索引，且条件确定只有一行匹配，则仅锁定该行（Record Lock）。
2. 如果查询不使用索引或命中非唯一索引/全表扫描，InnoDB 会对扫描过的所有记录施加 Next-Key Locks（即记录锁 Record Lock + 间隙锁 Gap Lock）。这意味着它锁定了记录本身以及记录之间的区间。

回到我们的案例分析：

• 事务（二）先执行：事务 ID 较小，先插入了新数据 A，并获得了数据 A 的行锁。
• 事务（一）后执行：由于 UPDATE 的条件字段 BATCH_NO 无索引，执行时需要全表扫描。因此，它不仅对已存在的数据加了行锁，还对包括数据 A 在内的间隙加了间隙锁（Gap Lock）。
• 冲突爆发：当事务（二）试图继续插入一条位于事务（一）所覆盖间隙范围内的新数据 B 时，需要申请插入意向锁（Insert Intention Lock）。但事务（一）已经持有了该区间的间隙锁，导致事务（二）阻塞；而事务（一）因为某些逻辑也需要等待资源，最终形成了互相等待的死锁闭环。此时，MySQL 会回滚其中一个事务（通常是代价较小的那个）来解除死锁。

代码复现与验证

为了更直观地观察这一过程，可以通过一个简单的测试表进行复现。

第1步：创建测试表

CREATE TABLE `dead_lock` (
  `id` varchar(10) NOT NULL,
  `batch_no` varchar(10) DEFAULT NULL,
  `status` varchar(1) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO dead_lock (id,batch_no,status) VALUES
 ('10','1','1'),
 ('20','20','1'),
 ('30','30',NULL),
 ('32','32',NULL);

第2步：模拟事务 A（插入操作）

开启事务 A，插入一条新数据，暂不提交，以便让锁持续：

begin;
insert into dead_lock values ('34', '34', null);

此时可以看到事务 A 正在运行。

第3步：模拟事务 B（无索引更新）

开启新的事务 B，执行对无索引字段的更新：

begin;
update dead_lock set status = '1' where batch_no = '20';

由于 batch_no 没有索引，事务 B 会尝试锁定相关行及间隙，这会导致该语句被阻塞（如果锁已被其他事务占用）。

第4步：诱发死锁

回到事务 A，插入一条落在事务 B 所需间隙内的数据（如 id='11'，位于 '10' 和 '20' 之间），这将直接触发 MySQL 死锁检测：

insert into dead_lock values ('11', '11', null);

随后系统通常会抛出 Deadlock found when trying to get lock 错误，并自动回滚其中一个事务。

总结与最佳实践

本次复盘揭示了在高并发场景下 REPEATABLE READ 隔离级别的潜在风险。要避免此类死锁，核心原则如下：

1. 务必检查索引：在执行 UPDATE、DELETE 或带有条件的 SELECT 时，确保 WHERE 子句中的字段有合适的索引。
2. 缩小锁范围：利用索引可以将扫描范围从“间隙+记录”缩减为单纯的“记录”，大幅降低与其他事务产生间隙锁冲突的概率。
3. 规范开发流程：定期审查慢查询与分析工具，杜绝在大表上进行无索引的全量更新操作。

以上就是 MySQL update where 无索引导致死锁解决的详细内容，更多关于 MySQL 优化及死锁排查的资料请关注本站其它相关文章！