跳到主要内容

加锁规则实战

锁机制讲了锁的种类，这页是手撕加锁范围的实战。大厂面试给一条 SQL 问「锁了哪些行、哪些间隙」是高频压轴题。

统一的实验表​

CREATE TABLE t (
  id int PRIMARY KEY,
  c  int DEFAULT NULL,
  d  int DEFAULT NULL,
  KEY c (c)
) ENGINE=InnoDB;

INSERT INTO t VALUES (0,0,0),(5,5,5),(10,10,10),(15,15,15),(20,20,20),(25,25,25);

全部案例都在 RR 隔离级别下，且 binlog_format=row。

🔥 两原则两优化一 bug​

这套总结必须背下来，所有案例都是它的推论：

规则内容
原则 1加锁的基本单位是 next-key lock（前开后闭区间）
原则 2查找过程中访问到的对象才会加锁
优化 1索引上的等值查询，给唯一索引加锁时，next-key lock 退化为行锁
优化 2索引上的等值查询，向右遍历时最后一个值不满足等值条件时，next-key lock 退化为间隙锁
一个 bug唯一索引上的范围查询会访问到不满足条件的第一个值为止

两条原则是机制，两条优化是 InnoDB 为了提升并发做的让步，那个 bug 是实现上的瑕疵（官方未认定为 bug，但行为确实不符合直觉）。

案例逐个拆​

案例 1：等值查询，值不存在​

UPDATE t SET d = d + 1 WHERE id = 7;
  • 在主键索引上找 id=7，不存在，定位到 id=10 这条记录
  • 原则 1 → 候选锁是 (5, 10]
  • id=10 不满足 id=7 这个等值条件 → 优化 2 生效，退化为间隙锁 (5, 10)

结果：锁住间隙 (5, 10)。此区间不能插入新行（如 id=8 会被阻塞），但可以更新 id=10 这一行。

案例 2：非唯一索引等值查询​

SELECT id FROM t WHERE c = 5 LOCK IN SHARE MODE;
  • c 是非唯一索引，等值查询必须继续向右扫到第一个不满足条件的值才能确定没有更多匹配
  • 加 (0, 5]；向右扫到 c=10 不满足 → 优化 2 退化为间隙锁 (5, 10)
  • 关键点：这是覆盖索引（只查 id，c 索引叶子就有 id），不需要回表，所以主键索引上没有任何锁

结果：c 索引上锁 (0, 5] 和 (5, 10)。所以 UPDATE t SET d=100 WHERE id=5 不会被阻塞。

:::tip 换成 FOR UPDATE 结果就不同 SELECT id FROM t WHERE c=5 FOR UPDATE —— InnoDB 认为你接下来要更新数据，会顺便给主键索引上满足条件的行加行锁。此时 id=5 这一行被锁住。

「LOCK IN SHARE MODE 只锁覆盖索引、FOR UPDATE 会连主键一起锁」是一个很细的区分点。 :::

案例 3：主键索引范围查询​

SELECT * FROM t WHERE id >= 10 AND id < 11 FOR UPDATE;

分两步理解：

  1. 先找 id=10，这是等值查询且唯一索引 → 优化 1 生效，(5,10] 退化为行锁 id=10
  2. 范围扫描继续向右，到 id=15 时发现不满足 id<11，但已经访问到了它 → 原则 2，加 next-key lock (10, 15]

结果：行锁 id=10 + next-key lock (10, 15]。

注意第二步没有退化为间隙锁 —— 优化 2 只适用于等值查询，这里是范围扫描。

案例 4：非唯一索引范围查询​

SELECT * FROM t WHERE c >= 10 AND c < 11 FOR UPDATE;

和案例 3 唯一的区别是索引从主键换成了普通索引 c：

  • 第一步找 c=10，虽然是等值查询，但 c 不是唯一索引 → 优化 1 不生效，保持 next-key lock (5, 10]
  • 继续向右到 c=15，加 (10, 15]

结果：(5, 10] + (10, 15]，即锁住 (5, 15]。比案例 3 多锁了 (5,10) 这个间隙。

案例 5：唯一索引范围查询的 bug​

SELECT * FROM t WHERE id > 10 AND id <= 15 FOR UPDATE;

按原则推导应该只锁 (10, 15]。但实际上：

  • 加 (10, 15] 之后，InnoDB 还会继续访问 id=20，加上 (15, 20]

结果：锁 (10, 20]。

这就是「一个 bug」—— 唯一索引上明明已经扫到了 id=15 这个等值边界，理论上可以确定不会有更多数据，没必要再往右。这个多余的锁会降低并发。

案例 6：非唯一索引上的重复值​

先插入一行让 c=10 有两条记录：

INSERT INTO t VALUES (30, 10, 30);
DELETE FROM t WHERE c = 10;

此时 c 索引上的顺序是 (c=5,id=5)、(c=10,id=10)、(c=10,id=30)、(c=15,id=15)。

结果：锁的范围是从 (c=5, id=5) 到 (c=15, id=15) 的开区间 —— 两条 c=10 的记录加行锁，中间和两侧的间隙都被锁住。

关键认知：非唯一索引的间隙是用 (索引值, 主键值) 这个组合来界定的，不是只看索引值。所以 INSERT INTO t VALUES (12, 10, 12) 会被阻塞（落在被锁的间隙里），而 INSERT INTO t VALUES (12, 15, 12) 也会被阻塞（(c=10,id=30) 到 (c=15,id=15) 的间隙）。

🔥 案例 7：limit 显著缩小锁范围​

DELETE FROM t WHERE c = 10 LIMIT 2;

接案例 6 的数据。因为 LIMIT 2 在找到两条 c=10 的记录后就停止扫描，根本不会访问到 c=15：

结果：只锁到 (c=10, id=30)，不再锁 (c=10,id=30) 到 (c=15,id=15) 的间隙。

:::tip 这是一条可以直接用的生产实践 删除数据时尽量加 LIMIT，一石二鸟：

  1. 控制单次删除的条数，避免大事务（长事务、主从延迟、binlog 膨胀）
  2. 缩小加锁范围，显著降低锁冲突概率

批量清理数据的标准写法是循环执行 DELETE ... WHERE ... LIMIT 1000，而不是一条语句删几百万行。 :::

案例 8：next-key lock 是分两段加的​

-- 事务 A                              -- 事务 B
BEGIN;
SELECT id FROM t WHERE c=10 LOCK IN SHARE MODE;
  -- 加 (5,10] next-key lock
                                       BEGIN;
                                       UPDATE t SET d=d+1 WHERE c=10;
                                         -- 要加 (5,10] 的写锁
                                         -- 间隙锁部分不冲突，行锁部分被阻塞
INSERT INTO t VALUES (8,8,8);
  -- 需要插入意向锁，被 B 持有的间隙锁阻塞 → 死锁

原理：next-key lock = 间隙锁 + 行锁，而且是分成两步加的。间隙锁之间互相兼容，所以事务 B 能拿到间隙部分，只在行锁部分被阻塞。这个「部分成功」的中间状态正是死锁的来源。

🔥 两阶段锁协议​

InnoDB 的行锁在需要时才加，但统一在事务提交（或回滚）时才释放。

这条机制有一个直接可用的推论：

把最可能造成锁冲突、最影响并发度的那个更新操作，尽量放在事务的最后。

经典例子（丁奇的电影票交易）—— 一笔交易要做三件事：

BEGIN;
-- ① 从顾客 A 账户余额扣 14 元
-- ② 给影院 B 账户余额加 14 元
-- ③ 插入一条交易记录
COMMIT;

影院 B 是热点行 —— 同一时刻可能有一百个顾客在买这家影院的票，都要更新这同一行。

  • 按 ①②③ 顺序：B 的行锁从第 2 步开始持有，一直到提交。锁的持有时间 = 第 2、3 步的耗时
  • 按 ③①② 顺序：B 的行锁在最后一刻才申请，持有时间最短，并发度最高

:::warning 这里还隐藏着一个死锁检测的坑 热点行上的高并发会引出另一个问题：死锁检测本身的开销。每个新来的等待线程都要遍历锁等待图，1000 个并发更新同一行就是约 100 万次检测，CPU 直接打满（表现是 CPU 使用率很高但 TPS 很低）。

三种应对：

  1. 确认业务不会死锁时，临时关掉 innodb_deadlock_detect（有风险，要配超时兜底）
  2. 在中间件 / 应用层做并发控制，对同一行的更新排队，让到达数据库的并发数降下来
  3. 把一行拆成多行 —— 影院账户拆成 10 条记录，每条存总额的 1/10，更新时随机选一条。并发度提升 10 倍。代价是要处理「某条记录余额不足」的退款逻辑 :::

加锁范围怎么自己验证​

不要靠记忆和推理，生产排查要看实际的锁：

-- MySQL 8.0：最直观
SELECT * FROM performance_schema.data_locks;
SELECT * FROM performance_schema.data_lock_waits;
SELECT * FROM sys.innodb_lock_waits;      -- 谁阻塞了谁，直接可读

-- 通用
SELECT * FROM information_schema.innodb_trx;   -- 当前事务
SHOW ENGINE INNODB STATUS;                     -- TRANSACTIONS 段

data_locks 的 LOCK_MODE 列直接告诉你锁的类型：

LOCK_MODE含义
X,REC_NOT_GAP行锁（排他）
X,GAP间隙锁
Xnext-key lock（行锁 + 间隙锁）
X,INSERT_INTENTION插入意向锁
IX / IS表级意向锁

MySQL 5.7 用的是 information_schema.innodb_locks / innodb_lock_waits，8.0 已移除这两个视图，这个差异经常让人踩空。

其他容易被追问的点​

DELETE 和 UPDATE 的加锁范围一样吗？ 一样 —— 都是当前读，按同一套规则加锁。INSERT 不同，它加的是插入意向锁 + 对新插入的行加行锁，并且如果触发唯一键检查，还会对冲突的那条已存在记录加共享锁（这是 INSERT ... ON DUPLICATE KEY UPDATE 高并发下死锁的常见来源）。

没有索引的条件会锁什么？ UPDATE t SET d=1 WHERE d=5 —— d 上没有索引，只能全表扫描，于是表里每一行和每个间隙都被锁住，等价于锁表。这是「更新条件必须走索引」的硬性理由，不只是性能问题。

RC 隔离级别下呢？ 没有间隙锁，只有行锁，且不满足条件的行会在判断后立刻释放锁（这是 semi-consistent read 的效果）。所以 RC 的并发高得多，代价是有幻读。很多互联网公司生产就用 RC，理由正是避免间隙锁带来的死锁和并发下降。

最后于 更新