InnoDB在RR级别下DELETE使用Next-Key Lock锁索引记录及间隙,非单纯行锁;即使ID区间不重叠,扫描落入同一间隙仍会争抢insert intention lock;ORDER BY字段需严格单调(如自增id)才能避免重复扫描;RC级别可消除Gap锁死锁但引入幻读风险;超80%数据删除应改用表替换或分区裁剪。

为什么DELETE WHERE id BETWEEN 1000 AND 2000会和id BETWEEN 1990 AND 2990互相卡住
根本不是“区间重叠”本身导致冲突,而是InnoDB在RR隔离级别下对WHERE扫描范围加Next-Key Lock——它锁的是索引记录+间隙,不是单纯的数据行。哪怕两个事务删的主键值完全不重合(比如一个删1000–1999,另一个删2000–2999),只要它们扫描时落在同一个间隙上,就可能争抢同一把insert intention lock。
典型触发条件:
-
id字段有索引,但WHERE没走索引(如隐式类型转换、函数包裹)→ 退化为全表扫 → 锁整个聚簇索引范围 - 使用
BETWEEN或IN且边界值非单调锚点 → 优化器无法确定精确扫描起点,每次执行都可能从同一Gap开始扫描 - 事务未提交,多轮分批DELETE堆在一个长事务里 → Gap锁长期持有,其他事务一靠近就
waiting
用ORDER BY id LIMIT分批时,为什么第二轮还扫到第一轮删过的行
关键在ORDER BY字段是否“单调可锚定”。如果id是自增主键,那没问题;但如果ORDER BY create_time,而create_time存在大量重复值(比如批量导入时毫秒级时间戳相同),InnoDB就无法唯一确定下一轮该从哪条记录继续——它只能按索引顺序往下扫,结果可能反复扫到已删/未删混杂的区域,造成锁重叠。
实操建议:
- 必须用严格单调字段做锚点:
DELETE FROM t WHERE status = 0 AND id > 12345 ORDER BY id LIMIT 1000 - 删完立刻记录
MAX(id)(不是LIMIT末尾那个id),因为ORDER BY id LIMIT不保证返回最大id,只保证按序取前N条 - 建联合索引
INDEX idx_status_id (status, id),确保WHERE + ORDER BY能走同一个索引,避免回表或排序临时表干扰锁范围
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED真能解死锁吗
能,但只解决Gap锁层面的冲突。RC级别下,InnoDB只对实际命中的记录加Record Lock,不加Gap Lock,因此两个事务分别删id > 1000和id > 1990,不会因争夺(1000, 1990)这个间隙而死锁。
但要注意副作用:
- 幻读风险:同一事务内多次执行相同
SELECT可能看到新插入的行 - 不能替代索引优化:如果仍没索引,RC下照样全表扫+全表Record Lock,只是不锁间隙而已
- 需确认业务允许:金融类场景常要求RR语义,盲目降级可能引发一致性问题
什么情况下该放弃DELETE,直接物理替换表
当你要删的数据量超过整表80%,或单次DELETE预估耗时超5分钟,就别再调优SQL了。这时候DELETE不是删除数据,是在持续阻塞写入、膨胀undo log、拖垮缓冲池。
更稳的选择:
- 保留少量数据:用
CREATE TABLE t_new LIKE t→INSERT INTO t_new SELECT * FROM t WHERE id > X→RENAME TABLE t TO t_old, t_new TO t - 已有分区:直接
ALTER TABLE t DROP PARTITION p202510,毫秒完成,无锁 - 业务可停写:
TRUNCATE TABLE t比大DELETE快一个数量级,且重置AUTO_INCREMENT
真正容易被忽略的是:分批DELETE的“批大小”从来不是性能瓶颈,锁范围是否互斥、是否及时释放、索引是否精准覆盖WHERE+ORDER BY,这三点才决定你是不是在给自己挖坑。

















