为什么MySQL在复合索引上范围更新会锁定不相关行-mysql教程-PHP中文网

mysql在rr隔离级别下，复合索引范围查询触发next-key lock，实际锁定匹配记录、扫描经过的所有间隙及最后一个匹配记录之后的间隙，以防止幻读。

为什么mysql在复合索引上范围更新会锁定不相关行

复合索引范围查询触发 Next-Key Lock 的真实加锁范围

MySQL 在可重复读（RR）隔离级别下，对复合索引执行范围条件的 UPDATE 时，不是“锁定不相关行”，而是按临键锁（Next-Key Lock）规则，**锁住匹配记录 + 所有扫描到的间隙 + 最后一个匹配记录之后的间隙**。这个行为常被误读为“锁多了”，实际是 InnoDB 为防止幻读主动覆盖的索引区间。

比如表 t(a INT, b INT, c INT)，有联合索引 idx_ab(a,b)，执行：

UPDATE t SET c = c + 1 WHERE a = 1 AND b > 5;

若索引中 a=1 的数据在 b 上分布为 (1,3), (1,5), (1,7), (1,9)，则该语句会扫描从 (1,5) 开始的所有 a=1 记录，并对 (1,5) 加记录锁，对 (1,5)→(1,7)、(1,7)→(1,9)、(1,9)→(1,+∞) 这三个间隙加间隙锁——也就是说，(1,6)、(1,8)、(1,10) 等尚未存在的值也被封锁，不允许插入。

为什么 `b > 5` 会锁住 `b = 3` 这样的“不相关行”？

它根本没锁 b = 3。真正被锁的是满足 a = 1 且 b > 5 的起始位置之后的整个索引段。关键点在于：InnoDB 的范围扫描是从索引最左前缀开始定位的，a = 1 定位到索引子树后，就在线性遍历该子树内所有 b 值，直到超出 b > 5 范围为止。所以：

MySQL

编写正确的MySQL查询，避免字符集、索引和锁方面的常见陷阱。

下载

(1,3) 和 (1,5) 会被读取（因为要找到第一个 b > 5 的位置），但只有 (1,7) 及之后的记录才会被更新；
而 InnoDB 对所有**扫描经过但未命中的记录**（如 (1,3)、(1,5)）仍会加间隙锁，防止其他事务在它们之间插入新值（比如 (1,4)）；
因此，看似“不相关”的 (1,3) 行本身没被改，但它和 (1,5) 之间的间隙被锁了——这是间隙锁的典型表现，不是锁错行，是锁对了间隙。

如何验证当前语句到底锁了哪些索引位置？

不能只看 SHOW ENGINE INNODB STATUS 里的 lock_mode X locks rec but not gap，它只反映锁的类型快照，且不显示间隙范围。更可靠的方式是：

在另一个会话中执行 INSERT INTO t VALUES (1, 4, 100); —— 若被阻塞，说明 (1,3)→(1,5) 间隙已被锁；
执行 SELECT * FROM t WHERE a = 1 AND b BETWEEN 4 AND 6 FOR UPDATE; —— 若被阻塞，说明该范围已被当前事务的 Next-Key 锁覆盖；
用 EXPLAIN FORMAT=tree 查看是否走了索引、扫描行数是否远大于实际匹配数（rows 高但 filtered 低，往往意味着大量扫描+加锁）；
确认隔离级别：SELECT @@transaction_isolation;，RR 下默认启用间隙锁，RC 下仅加记录锁（但会丢失幻读防护）。

想避免范围更新锁太多，有哪些务实选择？

没有银弹，只有权衡。常见做法包括：

把范围条件拆成多个等值更新，例如用 WHERE a = 1 AND b IN (7,9,11) 替代 b > 5，能精准命中索引记录，避免扫描和间隙锁；
业务允许时降级隔离级别到 READ COMMITTED，此时 InnoDB 不使用间隙锁，只对实际更新的行加记录锁；
确保 WHERE 条件能利用联合索引的最左前缀，且范围字段放在最后——比如 idx_a_b_c(a,b,c) 上，WHERE a = ? AND b = ? AND c > ? 比 WHERE a = ? AND c > ? 安全得多；
批量更新时加 ORDER BY pk LIMIT N，让加锁顺序一致，降低死锁概率，但不减少锁数量。