MySQL / 系统课时

理解 Next-Key Lock

理解 Next-Key Lock

Next-Key Lock 解决的是“范围内现在没有行,也不能让别的事务悄悄插进来”的问题。本节用一个普通二级索引实验,把记录锁、间隙锁与 Next-Key Lock 放到同一条索引线上,直接观察它怎样阻塞并发插入。

学习目标

  • 说清记录锁、间隙锁与 Next-Key Lock 的组合关系
  • 根据索引有序键值判断范围查询可能覆盖的锁区间
  • 用两个会话验证插入为何被阻塞以及何时恢复

从索引区间理解 Next-Key Lock

InnoDB 的行锁首先作用在索引记录上。记录锁保护某个已存在的索引项,Gap Lock 保护两个索引项之间尚未存在记录的空隙,而 Next-Key Lock 可以理解为“前一个间隙 + 当前索引记录”,常用区间记法是 (前一键, 当前键]。

在 REPEATABLE READ 下,锁定读和范围更新为了避免幻行,通常不会只锁查询结果中的现有行,还会保护扫描范围内的间隙。这样,其他事务即使准备插入一个当前不存在的键值,也可能因为目标插入点落在受保护间隙中而等待。

Next-Key Lock 由记录锁与前置间隙组成,并作用于有序索引区间

用并发实验观察区间阻塞

先建立只有四个索引值的测试表。普通二级索引 idx_score 会按 score 排序;当 score 相同时,还会结合主键值定位具体索引项。

sql
DROP TABLE IF EXISTS lock_demo;
CREATE TABLE lock_demo (
  id BIGINT PRIMARY KEY,
  score INT NOT NULL,
  KEY idx_score (score)
) ENGINE=InnoDB;

INSERT INTO lock_demo (id, score)
VALUES (1, 10), (2, 20), (3, 30), (4, 40);

预期结果

text
表 lock_demo 创建成功,并写入 4 行数据。

在会话 A 中显式使用可重复读,并对 20 到 30 的索引范围执行锁定读。事务不要提交。

sql
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;

SELECT id, score
FROM lock_demo
WHERE score BETWEEN 20 AND 30
FOR UPDATE;

预期输出

text
+----+-------+
| id | score |
+----+-------+
|  2 |    20 |
|  3 |    30 |
+----+-------+
2 rows in set

此时在会话 B 把锁等待时间缩短,再尝试向 20 与 30 之间插入 25。

sql
SET SESSION innodb_lock_wait_timeout = 2;

INSERT INTO lock_demo (id, score)
VALUES (5, 25);

预期输出

text
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

回到会话 A 提交事务,再在会话 B 重试同一条插入。

sql
COMMIT;

预期输出

text
Query OK, 0 rows affected
sql
INSERT INTO lock_demo (id, score)
VALUES (5, 25);

预期输出

text
Query OK, 1 row affected

为什么没有查询到 25 也会锁住它

会话 A 返回的只有 20 和 30,但 InnoDB 为了保证这个锁定范围在事务期间不出现新的索引记录,会保护两条现有索引记录以及它们之间的空隙。25 的插入位置正好落在 (20,30),因此必须等待持锁事务结束。

这也是排查锁竞争时最容易误判的地方:SQL 返回几行,不等于只锁几行。真正的锁范围取决于优化器选择的访问索引、扫描到的索引记录以及对应间隙。范围边界还可能让执行器检查到额外的相邻索引项,所以生产问题应结合 EXPLAIN 与锁监控信息验证,而不是只看 WHERE 条件猜测。

Next-Key Lock 何时不会完整出现

如果锁定读通过唯一索引精确命中一条已存在记录,InnoDB 已经能确定不会出现第二条相同唯一键,通常只需要记录锁,不必再用间隙锁保护相邻区间。相反,唯一键查询没有命中记录时,为了阻止其他事务在该位置插入“幻行”,仍可能锁住对应插入间隙。

普通一致性读不会因为读取范围就设置这类锁;SELECT ... FOR UPDATE、SELECT ... FOR SHARE 以及 UPDATE、DELETE 等需要加锁的语句才是本节关注的场景。READ COMMITTED 下 Gap Lock 的使用也明显减少,因此推导锁范围前必须先确认隔离级别。

判断 Next-Key Lock 的影响时,先确认隔离级别和访问索引,再把索引键按顺序排开,标出实际扫描的索引项,最后检查并发写入的目标键落在哪个间隙。只要把“SQL 条件”转换成“索引扫描区间”,Next-Key Lock 就不再是抽象概念。

按自己的节奏,确认真正理解。

目录与阅读设置

阅读设置

17

记录一个想法

理解 Next-Key Lock

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通