如何规避MySQL更新语句因范围查询所导致的意向排他锁冲突？

UPDATE范围查询触发IX锁冲突，是因为InnoDB在加next-key锁的同时隐式持有表级IX锁；IX本身不阻塞，但与DDL或表级X/S锁冲突，且背后行级锁争夺（如交叉扫描同一索引区间）导致实际阻塞。

UPDATE范围查询为什么触发意向排他锁（IX）冲突

意向排他锁（IX）本身不直接阻塞其他事务，但它会与表级锁（如DDL）、其他IX锁或S锁产生兼容性冲突。真正导致阻塞的是IX背后实际加的行级锁——当UPDATE走范围扫描时，InnoDB会在索引上加next-key锁，同时在表级别隐式持有IX锁。如果另一个事务正在执行ALTER TABLE或LOCK TABLES WRITE，就会因IX与X/S锁不兼容而卡住；更常见的是多个范围UPDATE交叉扫描同一索引区间，引发底层记录锁和间隙锁的争夺，最终表现为“等待获取IX锁”或“Waiting for table metadata lock”。

用主键分批 + 显式ORDER BY规避锁范围扩散

原生UPDATE不支持ORDER BY（MySQL 8.0.19+仅限单表无JOIN场景），所以不能靠SQL强制顺序。必须在应用层控制：

• 先执行SELECT id FROM t WHERE status = 'pending' AND created_at >= '2026-06-01' ORDER BY id LIMIT 500，确保拿到升序ID列表
• 对结果数组显式调用.sort()（防驱动或ORM重排）
• 拼UPDATE t SET status = 'processing' WHERE id IN (101,102,105,...)，且IN内ID顺序与排序后一致
• 每批执行完立刻COMMIT，释放所有锁并切断事务上下文

注意：步长别超500，否则优化器可能放弃索引；也别用LIMIT代替主键过滤——全表扫描再截断仍会锁所有扫描过的间隙。

联合索引设计压缩next-key锁影响边界

如果业务硬要按非主键字段做范围更新，单列索引效果有限。必须建覆盖式联合索引，让InnoDB能精准定位索引片段：

• 例如按status和created_at批量更新，建INDEX idx_status_ctime_id (status, created_at, id)
• 查询必须严格最左匹配：WHERE status = 'pending' AND created_at BETWEEN '2026-06-01' AND '2026-06-07'
• EXPLAIN中type应为range，key显示命中该联合索引，rows值合理（别动不动几万）

这样锁只落在(pending, '2026-06-01')到(pending, '2026-06-07')这个窄区间内，而不是整个created_at轴上的所有间隙。

READ COMMITTED隔离级别下间隙锁是否还生效

在READ COMMITTED下，InnoDB完全不加间隙锁（Gap Lock）和临键锁（Next-Key Lock），只对实际命中的记录加记录锁（Record Lock）。这意味着：

• UPDATE t SET x=1 WHERE id > 100只锁id=101、102…等已存在的行，不会锁(100,101)或(105,+∞)这些间隙
• 并发INSERT新id不再被阻塞，幻读可能发生，但写入吞吐显著提升
• 需确认业务能否接受不可重复读——比如两次SELECT之间其他事务提交了新数据，第二次UPDATE可能基于旧快照

设置方式：SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED；全局修改需改my.cnf中transaction_isolation并重启。别信ORM默认行为，有些框架（如旧版Django ORM）会静默维持RR。

真正难处理的不是IX锁本身，而是它和范围条件耦合后暴露的隐式依赖：比如你按时间范围更新，却没意识到这个范围在索引里对应一大片物理连续页，而另一事务正往同一页插入新记录——这时候即使加了索引，锁冲突仍会高频发生。