MySQL RR级别下WHERE id BETWEEN 10 AND 20会锁住(20, 25)区间，因InnoDB按临键锁机制扫描索引节点至25才终止，将该间隙纳入锁定范围，导致id=21插入被阻塞。
直接结论：别让 WHERE 条件触发全索引扫描，尤其要避开非唯一索引上的范围查询；必须用主键或唯一索引做精确切片，否则 next-key lock 会锁住你根本没想动的间隙。
为什么 WHERE id BETWEEN 10 AND 20 会锁住 (20, 25)？
MySQL RR 隔离级别下，BETWEEN 不是“只锁命中的行”，而是走临键锁（next-key lock）：每访问一个索引节点，就锁住它和下一个值之间的区间。假设主键有 9, 10, 15, 20, 25，执行 UPDATE ... WHERE id BETWEEN 10 AND 20 时，InnoDB 会读到 25 才确认扫描结束，于是连带把 (20, 25) 这个间隙也锁了——id = 21 的插入就会被阻塞。
关键点在于：LOCK_MODE X locks gap before rec 出现在 SHOW ENGINE INNODB STATUS 中，且 WAITING 行显示 insert intention 和 LOCK_DATA: 21，基本可断定掉进这个坑。
- 加锁范围不由
WHERE文本决定，而由「实际扫描路径」决定 - 非唯一索引上范围查询更危险：可能向右多扫几个节点，锁住更大间隙
- 间隙锁之间彼此兼容，但和插入意向锁互斥——这才是阻塞根源，不是锁太多，是类型冲突
如何用主键分批 UPDATE 避免锁扩散？
不能靠 LIMIT 100 控制锁范围，MySQL 仍可能全表扫描再截断，锁住所有扫描过的间隙。真正有效的是基于主键范围驱动，每次只扫确定的一小段。
- 写法必须是
WHERE id > @last_id AND id ，而不是 <code>LIMIT 100 OFFSET 10000 - 每批处理完立刻
COMMIT，释放所有锁，避免事务堆积 - 批次间加
DO SLEEP(0.1)，缓解锁竞争和主从延迟 - 用
ROW_COUNT()判断是否还有数据，防止漏更新 - 确保
id字段有主键或唯一索引，否则WHERE条件会退化为全表扫描
哪些写法会让索引失效、间接引发大范围锁？
索引没建错，但查询写法让它“半残”——这是最隐蔽的锁扩散原因。
-
WHERE DATE(create_time) = '2024-01-01'→ 函数操作字段，索引失效，退化为全表扫描加锁 -
WHERE user_id = '123'（user_id是INT）→ 隐式类型转换，索引失效 -
WHERE a > 10 AND b = 5，索引是(a, b)→ 因a是范围列，b无法下推，key_len截断，Extra显示Using where -
IN列表超过 1000 项 → MySQL 可能放弃走索引，改用全表扫描
查是否真走索引，唯一可靠方式是看 EXPLAIN 的 type（应为 range/ref）和 key（非 NULL），别只信 key 字段有没有值。
有没有比 BETWEEN 更安全的替代方案？
有，但要看业务是否允许。核心思路是把“范围扫描”转成“等值定位”：
- 先用
SELECT id FROM t WHERE status = 'pending' AND created_at BETWEEN '2024-01-01' AND '2024-01-31' ORDER BY id拿到 ID 列表 - 再用
UPDATE t SET status = 'processing' WHERE id IN (101, 102, 105, ...)—— 此时每个id都是明确存在的主键值，只加 record lock，无间隙锁 - 注意：
IN列表别超 1000 项，否则优化器可能弃用索引；两次查询之间需考虑数据删改，建议加应用层幂等或重试逻辑 - 更稳妥：在
created_at上建联合索引(status, created_at, id)，让初始范围扫描尽可能窄，缩小 gap 锁影响范围
最易被忽略的一点：哪怕你写了完美的 SQL，只要事务没及时提交、或者锁等待没被干预，TRX_ROWS_LOCKED 就可能滚到几十万——这时别等超时，立刻查 INFORMATION_SCHEMA.INNODB_TRX 并 KILL 掉 TRX_MYSQL_THREAD_ID。锁退化后止损，比优化 SQL 更紧迫。
