数据库服务器上的MySQL间隙锁,为什么会阻塞看似无关的插入?

发布人:A5数据发布时间:2026-11-14 10:23阅读量:27

“事务只查询 score = 15,为什么插入 score = 18 也要等待?”在 MySQL 数据库服务器上,这类现象未必是锁错了数据。对于 InnoDB,事务保护的对象不一定是一条已经存在的记录,也可能是索引中两个相邻键值之间的空白区间。两个条件在业务上不同,却可能落入同一个被保护的间隙。

这就是间隙锁让插入“看似无关、实际相关”的原因:插入是否等待,取决于新索引项将落在哪里,而不只是新记录是否满足另一条 SQL 的查询条件。理解这一点,需要把 SQL 条件、实际访问的索引和事务隔离级别放在一起看。

先划清边界:间隙锁不是表锁,也不是普通行锁

本文讨论 MySQL 的 InnoDB 存储引擎。其他存储引擎的并发控制机制不能直接套用。

在 InnoDB 中,通常所说的行锁,具体会落到索引记录上。理解间隙锁,首先要区分三个概念:

锁类型保护对象对插入的主要影响
记录锁(Record Lock)某个索引记录不直接保护记录前后的空白区间;插入仍可能受唯一性检查等因素影响
间隙锁(Gap Lock)相邻索引记录之间,或索引边界之外的间隙阻止向受保护间隙插入新的索引项
临键锁(Next-Key Lock)索引记录及其前方间隙同时保护已有记录和相邻的插入空间

例如,某个索引当前只有三个键值:

10 → 20 → 30

可以用以下区间理解它们的关系:

间隙:(10, 20)
记录:20
临键锁对应的区间:(10, 20]

这里的圆括号表示不包含端点,方括号表示包含端点。因此,对 (10, 20) 加间隙锁,核心效果是限制新索引项进入这个区间,并不等于把键值为 10 和 20 的记录本身都锁住。

如果事务保护了 (10, 20),插入 12、15、18 都可能等待。数据库不是逐个判断这些数字是否与原查询“业务相关”,而是在判断它们是否进入同一个索引间隙。

还要注意:上述数字模型省略了索引内部结构。对于 InnoDB 非唯一二级索引,索引记录还包含主键值,相同二级索引值之间也有顺序。真实锁边界应以具体索引记录为准,不能只看业务字段的数值范围。

可直接用于判断的定义:间隙锁保护的是索引中的插入位置,而不是 SQL 条件所描述的抽象业务集合。

为什么连没有查到的数据,也需要保护

空结果不代表没有并发冲突

考虑一张按 score 建立普通索引的表,现有值仍是 10、20、30。事务 A 执行:

SELECT id, score
FROM a5idc_gap_lock_demo
WHERE score = 15
FOR UPDATE;

查询没有返回任何记录,但这并不意味着没有需要保护的状态。

在 REPEATABLE READ 隔离级别下,这类锁定读需要处理其他事务插入新记录的情况。如果允许事务 B 立即插入 score = 15,原先“不存在符合条件记录”的状态就会发生变化。

InnoDB 的处理方式不是凭空创建一条值为 15 的记录来加锁,而是定位这个值应该出现的索引位置:它位于 10 和 20 之间。于是,事务 A 可以通过保护这个间隙,限制其他事务向其中插入。

问题在于,索引中的空白区间并不总能按 SQL 条件切得很细。保护 15 所在的间隙,也会影响同一间隙中的 18。这就是逻辑上的精确等值条件,产生更宽插入限制的原因。

为什么连没有查到的数据,也需要保护|空结果不代表没有并发冲突配图

临键锁把记录和间隙连在一起

对于范围扫描,InnoDB 在 REPEATABLE READ 下通常使用临键锁保护扫描涉及的索引范围。

沿着索引顺序,可以把临键区间理解为:

…… → (10, 20] → (20, 30] → ……

当某个临键区间被锁定时,影响不只落在右端的已有记录上,也落在这条记录之前的插入空间中。

实际 SQL 的加锁范围还受执行计划、边界判断和索引特性影响。扫描可能需要访问用于确认范围结束的后继记录,因此不能仅根据 WHERE 中写出的上下界,直接推断所有锁的边界。

这也解释了一个常见误区:

查询返回了多少行,与事务保护了多少插入位置,并不是同一个指标。返回零行可能存在间隙锁;返回少量记录,也可能因为扫描路径而涉及较大的锁定范围。

为什么两个间隙锁可以共存,插入却不能继续

间隙锁主要用于阻止插入。不同事务持有的纯间隙锁可以兼容,并不是看到锁模式中有排他标记,就一定意味着两个间隙锁互相阻塞。

插入记录前,事务会申请插入意向锁。若目标位置处于其他事务保护的间隙内,插入就可能等待。

因此,现场可能出现这样的关系:

事务 A:持有目标间隙的间隙锁
事务 B:可以在该间隙持有兼容的间隙锁
事务 C:申请向该间隙插入,需要等待

插入意向锁之间也不是一律冲突。不同事务向同一间隙的不同位置插入,在没有其他冲突时可以并行。真正需要识别的是:谁保护了这个间隙,谁正试图进入它。

为什么两个间隙锁可以共存,插入却不能继续配图

哪些因素决定“无关插入”是否被阻塞

隔离级别决定是否需要常规间隙保护

在 InnoDB 的 REPEATABLE READ 下,范围锁定读以及部分更新、删除操作,通常会涉及间隙锁或临键锁。

但普通一致性读,例如通常情况下不带锁定子句的 SELECT,主要依靠 MVCC 读取快照,不能与 SELECT ... FOR UPDATE 混为一谈。同样是“查询”,是否为锁定读,是分析入口之一。

在 READ COMMITTED 下,搜索和索引扫描通常不再使用常规间隙锁,但外键约束检查、重复键检查等仍可能涉及间隙锁。

因此,“改成 RC 就没有间隙锁”并不准确。改变隔离级别也会改变事务可见性语义,不能只根据一次插入等待就直接修改生产配置。

唯一索引只在特定条件下缩小锁范围

如果使用唯一索引的全部唯一键列进行等值查找,并且找到了对应记录,InnoDB 通常只需要锁住记录,而不必保护其前面的间隙。

但以下情况不能直接套用这一结论:

  • 对普通非唯一索引进行等值查询。
  • 对联合唯一索引只指定部分列。
  • 对唯一索引执行范围查询。
  • 等值查询没有命中记录。

特别是最后一种情况:唯一键查询没有命中,也不等于没有锁。在需要保护“不存在”状态时,仍可能锁定该键应当落入的间隙。

执行计划决定实际沿哪条索引加锁

同一条 SQL,如果访问路径不同,接触的索引记录和间隙也可能不同。

业务上很精确的条件,不代表执行时一定只扫描很小的范围。缺少合适索引时,语句可能扫描并锁定大量索引记录,表现为大面积等待,但这不能简单称为“行锁自动升级成表锁”。

对于二级索引访问,事务还可能锁定对应的聚簇索引记录。一次插入又需要维护主键及相关二级索引,因此,即使新主键与已有主键完全不同,也可能在某个二级索引上等待。

事务持续时间决定等待持续多久

间隙锁通常随事务结束而释放。查询已经返回,并不代表事务已经提交。

如果应用使用显式事务,锁定读之后又执行远程调用、等待用户操作,或者遗漏提交,相关插入就可能一直等待到事务结束或触发超时。

这类场景中,数据库服务器的 CPU、磁盘利用率可能都不高。等待的原因是事务之间存在顺序约束,而不是计算资源不足。增加服务器配置,并不能直接解除锁冲突。

用三个连接验证:查询 15,为什么插入 18 等待

下面通过一个没有匹配记录的等值查询,观察间隙锁的效果。示例适用于 MySQL 8.0、8.4 的 InnoDB 环境,锁信息使用 Performance Schema 查看。

准备独立测试表并确认环境

只在隔离测试库中执行,三个连接需要使用同一个测试库。示例会创建表并写入少量演示记录,不要替换成业务表。若测试环境包含需要保留的数据,应先完成相应备份或快照。

CREATE TABLE 属于 DDL,不能依靠后续 ROLLBACK 撤销。以下事务中的演示插入可以回滚,建表和初始化数据则会保留。建议使用新建的专用连接,不要直接复用应用连接池。

先检查各连接的环境:

SELECT
    VERSION() AS mysql_version,
    DATABASE() AS current_database,
    @@SESSION.transaction_isolation AS isolation_level,
    @@SESSION.autocommit AS autocommit,
    CONNECTION_ID() AS connection_id;

确认已经选择独立测试库,再执行一次初始化:

CREATE TABLE a5idc_gap_lock_demo (
    id INT NOT NULL,
    score INT NOT NULL,
    PRIMARY KEY (id),
    KEY idx_score (score)
) ENGINE = InnoDB;

START TRANSACTION;

INSERT INTO a5idc_gap_lock_demo (id, score)
VALUES
    (100, 10),
    (200, 20),
    (300, 30);

COMMIT;

如果同名表已经存在,应停止并核对用途,不要覆盖或清空。初始化完成后,二级索引中的有效顺序可以简化为:

(score=10, id=100)
(score=20, id=200)
(score=30, id=300)

连接 A:锁定一个没有记录的查询位置

在连接 A 中执行:

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

EXPLAIN
SELECT id, score
FROM a5idc_gap_lock_demo FORCE INDEX (idx_score)
WHERE score = 15
FOR UPDATE;

START TRANSACTION;

SELECT id, score
FROM a5idc_gap_lock_demo FORCE INDEX (idx_score)
WHERE score = 15
FOR UPDATE;

查询应返回空结果,随后保持事务开启,不要提交。

这里使用 FORCE INDEX 是为了固定演示访问路径。生产优化不能照搬,应结合真实执行计划判断。

此时,15 应落在 (10, 100) 与 (20, 200) 两个二级索引记录之间。连接 A 保护的是这个插入间隙,而不是一条不存在的 score = 15 记录。

连接 B:插入另一条业务值不同的记录

在连接 B 中执行:

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

START TRANSACTION;

INSERT INTO a5idc_gap_lock_demo (id, score)
VALUES (180, 18);

这条插入预期会等待。

主键 180 没有重复,score = 18 也不等于连接 A 查询的 15。但它对应的二级索引项仍落在同一个间隙内,因此不能绕开连接 A 的保护。

如果已经发生锁等待超时,先在连接 B 执行 ROLLBACK,再重新开始这一事务。不要把超时错误当作整个事务必然自动结束。

连接 C:查看等待关系,而不只观察客户端卡住

连接 C 需要具备读取相关 Performance Schema 表的权限。先查看测试表上的锁:

SELECT
    ENGINE_TRANSACTION_ID,
    THREAD_ID,
    INDEX_NAME,
    LOCK_TYPE,
    LOCK_MODE,
    LOCK_STATUS,
    LOCK_DATA
FROM performance_schema.data_locks
WHERE OBJECT_SCHEMA = DATABASE()
  AND OBJECT_NAME = 'a5idc_gap_lock_demo'
ORDER BY ENGINE_TRANSACTION_ID, INDEX_NAME, LOCK_DATA;

重点观察 idx_score 上的锁。该示例中,典型现象包括:

  • 连接 A 持有带 GAP 标记的锁。
  • 锁关联到间隙右端的索引记录,数据可能显示为 20, 200。
  • 连接 B 存在带 INSERT_INTENTION 标记、状态为 WAITING 的锁。

具体显示顺序、锁条目数量及数据格式可能存在差异,应以实际结果为准。锁数据中显示了右端记录,也不代表一定锁住了右端记录本身,需要结合锁模式判断。

再查看阻塞关系:

SELECT
    r.ENGINE_TRANSACTION_ID AS waiting_trx,
    b.ENGINE_TRANSACTION_ID AS blocking_trx,
    r.INDEX_NAME AS waiting_index,
    r.LOCK_MODE AS waiting_mode,
    b.LOCK_MODE AS blocking_mode,
    r.LOCK_DATA AS waiting_lock_data,
    b.LOCK_DATA AS blocking_lock_data
FROM performance_schema.data_lock_waits AS w
JOIN performance_schema.data_locks AS r
  ON r.ENGINE = w.ENGINE
 AND r.ENGINE_LOCK_ID = w.REQUESTING_ENGINE_LOCK_ID
JOIN performance_schema.data_locks AS b
  ON b.ENGINE = w.ENGINE
 AND b.ENGINE_LOCK_ID = w.BLOCKING_ENGINE_LOCK_ID
WHERE r.OBJECT_SCHEMA = DATABASE()
  AND r.OBJECT_NAME = 'a5idc_gap_lock_demo';

这一步可以把“插入正在等待”落实为“哪个事务的哪一个索引锁阻塞了它”。

加一个对照:插入 25 是否也会等待

仍在连接 C 中执行:

START TRANSACTION;

INSERT INTO a5idc_gap_lock_demo (id, score)
VALUES (250, 25);

ROLLBACK;

在只有这些演示事务参与的条件下,这条插入预期不会被连接 A 的间隙锁阻塞,因为 25 落在 20 与 30 之间,不属于连接 A 保护的间隙。

这个对照很重要:如果 18 等待而 25 可以完成,说明影响具有索引位置边界,而不是整张表都不能插入。

释放锁,确认等待链随之解除

回到连接 A:

ROLLBACK;

连接 B 中等待的插入随后应能继续执行。插入成功后,在连接 B 执行:

A 在 REPEATABLE READ 下对 score=15 执行锁定读并保持事务;B 插入 score=18 后等待;C 查询 data locks 和 d

ROLLBACK;

这样,18 不会保留在测试表中。

所有事务结束后,可以把连接 A 改为 READ COMMITTED,重复相同的空结果锁定读,再从连接 B 插入 18。在本例没有外键、唯一键冲突和其他并发事务的条件下,该插入通常不再因上述常规间隙保护而等待。

测试结束后,应恢复专用连接原有的会话设置,或者直接关闭这些测试连接。保留测试表用于复核即可;若需要清理,应单独确认库名、表名和数据保留要求,不能依赖事务回滚撤销建表。

如何把验证结果用于生产判断

实验能证明机制,但生产环境还需要核对实际语句和实际索引,不能仅凭“插入卡住”就认定是间隙锁。

可以按以下顺序判断:

  1. 确认事务边界。 找到阻塞事务,检查它是否已经执行完 SQL 却仍未提交,以及事务中是否夹杂外部调用或长时间等待。
  2. 确认锁定读和隔离级别。 区分普通一致性读、FOR UPDATE、FOR SHARE、更新和删除语句,不把所有查询视为同一种加锁行为。
  3. 确认访问索引。 结合执行计划和 INDEX_NAME,判断等待发生在主键还是二级索引。
  4. 确认锁模式与等待关系。 LOCK_TYPE = RECORD 并不表示一定只有记录锁,还要看 GAP、REC_NOT_GAP、INSERT_INTENTION 等标记。
  5. 确认新记录的索引位置。 不只比较主键或 SQL 条件,还要检查所有相关索引中的插入位置。

如果锁信息涉及 supremum pseudo-record,通常需要关注索引末端间隙。它是索引边界的内部表示,不是一条实际业务记录。

处理方式应与原因对应:事务过长就缩短持锁时间;访问路径过宽就评估索引与 SQL;业务确实不需要原有隔离语义时,再评估隔离级别调整。超时参数只能改变等待多久,不能消除锁之间的冲突。

还应区分锁等待与死锁。单条插入等待另一个事务结束,不一定形成死锁;只有事务之间构成循环等待,才涉及死锁判断。

最终,判断“无关插入”是否真的无关,应落到一条可以验证的链路上:

哪条语句,在什么隔离级别下,沿哪个索引,保护了什么区间,而新记录的索引项是否落入其中。

只要这条链路能够由执行计划、锁模式和等待关系相互印证,就能解释阻塞;如果对应不上,就应继续检查记录锁、唯一性检查、外键检查或元数据锁等其他原因,而不是把所有插入等待都归结为间隙锁。

对于承载 MySQL 的业务环境,A5idc数据提供香港、美国、日本等地区的物理服务器租用,涵盖 Xeon Gold、AMD EPYC 等平台,并有搭配 SSD 或 NVMe 存储、不同内存配置的方案,可用于数据库、业务后台及接口服务部署。多地区资源与不同线路选择,也为面向不同访问区域的业务提供基础设施组合空间。

本文由A5数据原创发布,转载请注明出处:https://www.a5idc.com/helpcontent/9342.html

相关推荐