WHERE id BETWEEN 10 AND 20 在RR级别下会锁住21，因为InnoDB加的是next-key锁，覆盖间隙(9,25)，21落在该间隙内；LOCK IN SHARE MODE同样会加间隙锁，无法避免。
为什么 WHERE id BETWEEN 10 AND 20 会锁住 21？
MySQL 在可重复读（RR）隔离级别下，SELECT ... FOR UPDATE 或 UPDATE 带范围条件时，InnoDB 不仅锁定命中记录，还会锁定「间隙（gap）」——即不存在记录的索引区间。比如主键是 id，现有记录为 9, 10, 15, 20, 25，执行 UPDATE t SET x=1 WHERE id BETWEEN 10 AND 20，InnoDB 实际加锁区间是 (9, 25)，其中 21 就落在这个间隙里，被锁住，导致其他事务插入 id=21 被阻塞。
用 SELECT ... LOCK IN SHARE MODE 能避免间隙锁吗？
不能。只要语句走的是**范围扫描 + 当前行锁模式开启**（如 RR 隔离级），InnoDB 就会加间隙锁或 next-key 锁，和你是 FOR UPDATE 还是 LOCK IN SHARE MODE 无关。唯一能绕过间隙锁的方式是：让查询退化为**唯一等值查找**，且该值存在。
- ✅ 安全：
SELECT * FROM t WHERE id = 15 FOR UPDATE—— 只锁单行（record lock） - ❌ 不安全：
SELECT * FROM t WHERE id >= 15 AND id —— 看似等值，但优化器可能走范围扫描，仍加 next-key 锁 - ✅ 可控： 加
FORCE INDEX强制走唯一索引，并确保EXPLAIN显示type=const或type=eq_ref
如何让 BETWEEN 查询不锁间隙？
核心思路是：**把范围查询拆成多个确定存在的等值点，再用 IN 替代**。前提是这些值能提前查出来、数量可控（一般 ≤ 几百），且业务允许“先查后更”两阶段操作。
例如：想更新所有 status = 'pending' 且 created_at 在某时间范围内的订单，但又不想锁住未来可能插入的时间间隙：
面向 CRM 系统的 MySQL 8 数据库模式设计。当用户需要为客户关系管理（CRM）系统设计、评审、优化或生成数据库模式时，应使用此项技能。
SELECT id FROM orders WHERE status = 'pending' AND created_at BETWEEN '2024-01-01' AND '2024-01-31' ORDER BY id;
拿到结果集后，再用 UPDATE orders SET status='processing' WHERE id IN (101,102,105,...) —— 此时每个 id 都是明确存在的主键值，只加 record lock，无间隙锁。
- ⚠️ 注意：如果
IN列表超 1000 项，MySQL 可能放弃使用索引，改用全表扫描，反而更慢更重 - ⚠️ 必须确保两次查询之间数据未被删改，否则出现「幻读」或更新丢失，需配合应用层幂等或重试逻辑
- ? 更稳妥的做法：在
created_at上建联合索引(status, created_at, id)，让范围扫描尽可能窄，缩小 gap 锁影响范围
哪些索引设计会让间隙锁更“痛”？
间隙锁的粒度由**索引结构和扫描方式**决定，不是由 WHERE 条件本身决定的。最典型的陷阱是：在非唯一、非覆盖的二级索引上做范围查询。
- ❌ 危险组合：
INDEX (category)（非唯一），执行UPDATE t SET price=99 WHERE category = 'book'—— 会锁住整个category='book'对应的所有索引间隙，包括之后插入的新book记录 - ✅ 改进：改为
INDEX (category, id)，并显式带上主键等值条件：WHERE category = 'book' AND id > 1000，让优化器能利用索引有序性，减少扫描宽度 - ✅ 更优：若业务常按
category批量更新，考虑加INDEX (category, updated_at)并用updated_at做分页条件，避免全索引扫描
真正难调的不是 SQL 写法，而是你不知道哪条看似简单的 UPDATE 正在悄悄锁住下一个小时要插入的 500 行——得靠 SELECT * FROM performance_schema.data_locks 结合 INFORMATION_SCHEMA.INNODB_TRX 实时看锁住了什么。
