为什么MySQL在更新主键值时会锁定整个范围-mysql教程-PHP中文网

update主键值锁住(旧值, 新值]区间，是因为innodb在rr级别下将其视为“删除旧行+插入新行”，必须用next-key lock确保新主键不冲突且防止间隙插入；即使新值已存在或原记录不存在，仍按此逻辑加锁，无法绕过。

为什么mysql在更新主键值时会锁定整个范围

UPDATE主键值时为什么锁住(旧值, 新值]区间？

因为InnoDB在RR隔离级别下，主键更新会先对原记录加Record Lock，再对新主键位置加Next-Key Lock——后者本质是锁住从“前一个索引值”到“新主键值”的左开右闭区间，不是只锁两行。

这和普通UPDATE不同：改主键等于删一行、插一行，引擎必须确保新值不与现有记录冲突，同时防止其他事务在间隙中插入“撞车”数据。比如UPDATE t SET id = 8 WHERE id = 5，若当前索引有3, 5, 10，实际会锁住(3, 8]，即覆盖原记录位置+新值可能插入的所有空隙。

即使新主键值已存在（如id=8已有），也会先锁(3, 8]再报错，锁不会提前释放
若原主键无对应记录（WHERE id = 999不命中），则只锁Gap Lock，比如(10, ∞)或(3, 10)，取决于索引分布
SELECT ... FOR UPDATE查不到记录时也只加Gap Lock；但UPDATE主键值一定会触发Next-Key Lock逻辑，哪怕目标行不存在

主键更新比普通字段更新更容易引发死锁？

是的。主键更新涉及两次索引操作：先定位旧主键（走聚簇索引），再检查新主键是否冲突（再次走聚簇索引），中间还可能触发唯一约束校验锁。两个事务若交叉更新彼此的主键值，极易形成循环等待。

例如事务A执行UPDATE t SET id = 20 WHERE id = 10，事务B同时执行UPDATE t SET id = 10 WHERE id = 20，双方都持有原记录X锁，又互相等待对方释放新位置的锁，死锁检测器会在几毫秒内回滚其中一个。

普通字段更新（如UPDATE t SET name = 'x' WHERE id = 10）只锁id = 10这一行，无二次定位开销
主键更新无法使用覆盖索引优化，必然回表且重复扫描聚簇索引
批量更新主键时（如UPDATE t SET id = id + 100），会逐行做上述动作，锁范围指数级扩大

如何验证主键更新实际锁了哪些位置？

用performance_schema.data_locks查实时锁信息最直接。在UPDATE语句执行后、COMMIT前，运行：

MySQL

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

下载

SELECT ENGINE_TRANSACTION_ID, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_DATA 
FROM performance_schema.data_locks 
WHERE OBJECT_SCHEMA = 'your_db' AND OBJECT_NAME = 'your_table';

你会看到至少两条记录：一条LOCK_MODE = X, LOCK_DATA = '5'（原主键值），另一条LOCK_MODE = X, LOCK_DATA = '8'或LOCK_MODE = X, LOCK_DATA = '8' (next-key)（新值及间隙）。如果LOCK_DATA显示为NULL或数值范围（如8, 10），说明锁已扩展到间隙。

SHOW ENGINE INNODB STATUS里的TRANSACTIONS段也能看到lock struct(s)数量和trx_rows_locked，但不够精确到具体值
不要依赖EXPLAIN，它只反映查询计划，不体现锁行为
测试时务必在RR隔离级别下，RC下主键更新只锁记录本身，不锁间隙，但无法防止幻读

能绕过主键更新的范围锁定吗？

不能真正绕过，但可以规避。核心思路是：不直接UPDATE主键，而是用DELETE+INSERT替代，并控制事务粒度。

例如把UPDATE t SET id = 8 WHERE id = 5拆成：

BEGIN;
DELETE FROM t WHERE id = 5;
INSERT INTO t (id, ...) VALUES (8, ...);
COMMIT;

这样只锁id = 5和id = 8两行（假设id = 8已存在），不会锁整个(3, 8]区间。但要注意外键、触发器、自增逻辑等副作用。

如果新主键值不存在，INSERT会加Insert Intention Lock，不阻塞其他事务插入相邻间隙
DELETE和INSERT分属两个操作，锁持有时间更短，降低死锁概率
业务上需保证原子性时，仍得用单条UPDATE——此时锁范围就是设计使然，不是缺陷

主键更新的锁范围不是配置能调的，它是InnoDB在RR级别下保障可重复读和唯一约束的必然代价。真正容易被忽略的是：哪怕你只改一个主键值，只要表里主键分布稀疏，锁住的间隙可能远超预期，尤其在分库分表或归档场景下，这个“范围”可能横跨数万ID。