非唯一索引等值查询必须锁相邻区间,因为InnoDB需通过锁定“值所在区间”防止幻读;其无法仅靠单行锁保证一致性,相同键值可能被反复插入,故必须封锁前后间隙。

非唯一索引等值查询为什么必须锁相邻区间
因为 InnoDB 要靠锁住「值所在区间」来防止幻读,而非唯一索引无法通过单行锁定保证一致性——相同键值可能被反复插入,所以必须封锁前后间隙。
比如表 t 有普通索引 idx_name,数据为 ('a', 'b', 'd'),执行 SELECT * FROM t WHERE name = 'c' FOR UPDATE:
-
'c'不存在,但 InnoDB 会定位到'b'和'd'之间,加间隙锁(b, d) - 此时另一个事务执行
INSERT INTO t (name) VALUES ('c'),需申请插入意向锁,与该间隙锁冲突,直接阻塞 - 若只锁命中行(实际没行可锁),就无法阻止新
'c'插入,RR 隔离语义即被破坏
非唯一索引 vs 唯一索引的加锁差异
唯一索引(如主键)等值查询能退化为纯记录锁,而非唯一索引哪怕只匹配一行,也必然带间隙锁——这是设计刚性,不是 bug。
实操验证时注意:
- 用
SELECT @@tx_isolation确认是REPEATABLE-READ - 查
performance_schema.data_locks,你会看到两把锁:lock_mode S在非唯一索引上 +lock_mode S,REC_NOT_GAP在主键上,外加隐式间隙锁 - 对唯一索引执行
WHERE id = 5(且id=5存在),data_locks中只有lock_mode X或S的单条记录锁,无gap before rec
为什么死锁常出现在两个事务查同一不存在值
两个事务都执行 SELECT ... WHERE name = 'c' FOR UPDATE('c' 不存在),各自在 (b, d) 加间隙锁;接着又都尝试 INSERT ('c'),双方都在等对方释放间隙锁,环路形成。
安装、修复、更新或打包一个基于 Python 和 PyMySQL 实现的 Windows `mysql` 命令。适用于用户希望在 PowerShell/CMD 中输入 `mysql` 时重定向到 Python 实现、需要交互式输入 MySQL 连接信息、需要同步到当前用户本地 bin 目录,或需要将该能力封装成可公开复用的 Codex skill。
关键线索在 SHOW ENGINE INNODB STATUS 输出里:
-
lock_mode X locks gap before rec表示当前持有间隙锁 -
insert intention waiting出现在WAITING行,说明正卡在插入意向锁申请上 - 若 A 的
WAITING指向 B 持有的间隙,B 的WAITING指向 A 持有的间隙,就是标准死锁
想绕过间隙锁?代价很实在
只有两条路,且都得接受副作用:
- 降级隔离级别为
READ COMMITTED:间隙锁失效,UPDATE和SELECT ... FOR UPDATE只锁真实存在的行,但幻读风险回归 - 确保查询走唯一索引 + 等值 + 记录存在:例如
UPDATE t SET x=1 WHERE id = 100,id是主键且该行真实存在,此时才可能避免间隙锁
别指望加 FORCE INDEX 或重写 SQL 能绕过——只要是非唯一索引等值查询,InnoDB 就按 next-key 规则加锁,这个逻辑写死在存储引擎里。










