MySQL间隙锁为什么会阻塞插入,如何从事务与索引确认锁范围?

发布人:A5数据发布时间:2026-11-10 12:11阅读量:66

很多间隙锁问题看起来像“查询锁住了不存在的行”:事务只查询了某个范围,另一个事务向范围中插入一条新记录,却一直等待。原因通常不是数据库给不存在的行加了行锁,而是 InnoDB 为了避免并发事务改变索引范围内的记录集合,对索引记录之间的间隙加了锁。

判断阻塞范围,不能只看 SQL 中的条件,还要结合事务隔离级别、实际使用的索引、执行计划和当前锁信息。所谓“锁住了 k=15”,实际可能是 k=15 所在的索引间隙被锁住;而同一张表上的另一条插入语句,也可能因为落在不同间隙而正常执行。

间隙锁锁的是什么

InnoDB 的间隙锁(Gap Lock)保护的是索引中两条记录之间的范围,不是某个现存记录。例如索引上有 10、20、30 三个值,那么记录 10 和 20 之间的间隙可以表示为 (10, 20)。对这个间隙加锁后,其他事务通常不能在其中插入新的索引记录。

InnoDB 还会使用临键锁(Next-Key Lock)。它可以理解为“一个索引记录及其前面的间隙”,例如锁住值为 20 的临键锁,覆盖范围可理解为 (10, 20]。扫描范围、索引边界以及查询是否匹配记录,都会影响实际锁定的记录和间隙。

间隙锁锁的是什么配图

索引末尾也有一个概念上的上界,称为 supremum。对无上界的范围扫描,锁可能涉及最大索引记录之后的范围。因此,WHERE k > 30 这类条件并不意味着锁范围只包含当前已存在的记录。

间隙锁主要用于阻止插入,不会把间隙变成一条被读取或更新的记录。InnoDB 的间隙锁通常属于抑制性锁:多个事务可以同时持有同一间隙上的间隙锁;但其他事务要在该间隙插入记录时,需要取得插入意向锁,可能被已有的锁阻塞。

为什么查询会导致插入等待

InnoDB 默认使用 REPEATABLE READ 隔离级别。对于锁定读、UPDATE、DELETE 等操作,InnoDB 会根据访问路径对索引记录及相邻间隙加锁,以限制并发插入改变被保护的范围。这样可以降低范围内出现“幻行”的风险。

以下例子使用非唯一索引。请在可自行清理的测试库中执行,不要直接在生产业务表上实验。

CREATE TABLE gap_demo (
    id BIGINT NOT NULL PRIMARY KEY,
    k  INT NOT NULL,
    KEY idx_k (k)
) ENGINE=InnoDB;

INSERT INTO gap_demo (id, k) VALUES
    (1, 10),
    (2, 20),
    (3, 30);

在会话 A 中开启事务并对索引记录 k=20 做锁定读:

START TRANSACTION;

SELECT id, k
FROM gap_demo
WHERE k = 20
FOR UPDATE;

在会话 B 中尝试插入 k=15:

INSERT INTO gap_demo (id, k) VALUES (4, 15);

在典型的 InnoDB REPEATABLE READ 场景下,会话 B 的插入可能等待会话 A 提交或回滚。原因是 idx_k 中值 10 与 20 之间的间隙受到保护,15 落在这个间隙中。会话 B 插入时需要取得对应的插入意向锁,因此不能立刻完成。

为什么查询会导致插入等待配图

作为对照,若会话 B 插入 k=25,它位于 20 和 30 之间;如果会话 A 的锁只覆盖前一个间隙,插入通常不会因为该间隙锁而等待。具体结果仍应以实际锁记录为准,因为查询计划、重复键、扫描边界等因素会改变锁定范围。

实验结束后,先在会话 B 中取消或等待插入结束,再在会话 A 中执行:

ROLLBACK;

回滚会释放该事务持有的锁。若测试表中已经成功插入了示例数据,清理前应先确认数据确实属于测试内容;删除数据会影响表内容,不要把清理语句直接用于生产表。

哪些因素会改变锁范围

隔离级别

在 InnoDB REPEATABLE READ 下,范围扫描的锁定读和写操作常使用间隙锁或临键锁。切换到 READ COMMITTED 后,InnoDB 通常会减少普通搜索和索引扫描所用的间隙锁,未匹配记录的锁也可能更早释放。但外键检查和重复键检查等场景仍可能涉及间隙锁,不能简单理解为“READ COMMITTED 完全没有间隙锁”。

隔离级别应结合应用所需的一致性语义评估。为消除某次插入等待而修改全局隔离级别,可能改变其他事务的读写行为,并不只是缩小一把锁。

索引类型与查询条件

InnoDB 按索引顺序确定范围,而不是按 SQL 文本里看起来相邻的值确定范围。以下情况尤其需要核对:

  • 使用唯一索引对已存在的完整键值做等值搜索,通常只需锁定匹配记录,不需要锁住其前方间隙。
  • 对非唯一索引做等值搜索,可能扫描同值的多条索引记录及相关间隙。
  • 搜索不存在的键值时,即使没有匹配行,也可能对其应落入的索引间隙加锁。
  • 组合索引按列顺序排列。例如索引为 (a, b) 时,a 相同而 b 不同的记录仍按组合键顺序形成间隙;只看 b 的数值,无法判断锁范围。
  • 未命中合适索引、使用函数或隐式类型转换等情况,可能导致扫描更多索引记录,进而扩大锁影响范围。

唯一索引的“等值命中只锁记录”有明确前提:搜索条件应匹配完整唯一键,并实际走到该唯一索引上的点查。若条件不完整、键值不存在或执行计划选择了其他访问路径,就不能仅凭 SQL 写法推断不会加间隙锁。

访问路径与事务持续时间

锁跟随实际扫描的索引记录和范围,而不是只由 WHERE 条件的文字边界决定。执行计划采用哪个索引、扫描方向和范围多宽,都会影响锁定对象。即使查询最终只返回一行,执行过程中也可能访问更多索引记录。

相关锁通常至少要等持锁事务结束后才释放。因此,事务中执行了锁定查询后,即使应用已经完成业务判断,只要事务迟迟没有提交或回滚,插入仍可能持续等待。长事务、人工暂停的测试事务,以及应用连接池中未正确结束的事务,都会延长这种等待。

间隙锁排查之外,数据库业务的持续运行也离不开合适的服务器资源。A5idc数据提供香港、美国、日本、韩国、中国台湾、新加坡和马来西亚等地区的物理服务器,涵盖Xeon Gold、AMD EPYC等平台,并有SSD或NVMe存储配置,可为数据库、业务后台及接口服务提供部署基础。不同地区的线路与资源组合各有侧重,能够对应不同用户分布、数据规模和业务负载的部署需求。

如何从事务和索引确认锁范围

判断时应先建立完整对应关系:哪个事务持有锁,哪个事务正在等待;锁落在哪张表、哪个索引;等待插入的键值会进入哪个索引间隙。只看“某条 SQL 执行很慢”,不足以确认是间隙锁。

先确认事务和等待关系

MySQL 8.0 可以查看 InnoDB 当前的数据锁和等待关系。以下查询将等待锁与阻塞锁关联起来:

SELECT
    w.REQUESTING_THREAD_ID AS waiting_thread,
    r.OBJECT_SCHEMA        AS waiting_schema,
    r.OBJECT_NAME          AS waiting_table,
    r.INDEX_NAME           AS waiting_index,
    r.LOCK_MODE            AS waiting_lock_mode,
    r.LOCK_STATUS          AS waiting_lock_status,
    r.LOCK_DATA            AS waiting_lock_data,
    w.BLOCKING_THREAD_ID   AS blocking_thread,
    b.LOCK_MODE            AS blocking_lock_mode,
    b.LOCK_STATUS          AS blocking_lock_status,
    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_LOCK_ID = w.REQUESTING_ENGINE_LOCK_ID
JOIN performance_schema.data_locks AS b
  ON b.ENGINE_LOCK_ID = w.BLOCKING_ENGINE_LOCK_ID;

结果中应重点核对 waiting_table、waiting_index 和两侧的锁模式、锁数据。MySQL 版本、索引类型和锁对象不同,展示格式会有差异;锁模式中可能出现类似 GAP、REC_NOT_GAP、INSERT_INTENTION 的标记。不要仅凭某一个字符串判断范围,需把锁数据和索引顺序一并解读。

如何从事务和索引确认锁范围配图

若以上查询没有结果,不代表此前绝无锁等待:等待可能已经结束,事务可能已经提交,或者当前账号和实例的性能监控配置无法提供所需信息。应在阻塞仍然发生时采集,并结合事务状态查看:

SELECT
    trx_id,
    trx_state,
    trx_started,
    trx_wait_started,
    trx_mysql_thread_id,
    trx_query
FROM information_schema.innodb_trx;

在 MySQL 5.7 等没有 performance_schema.data_locks 的环境中,可检查 information_schema.innodb_trx、information_schema.innodb_lock_waits 和 information_schema.innodb_locks。这些视图的字段和可见信息与 MySQL 8.0 不完全相同,需按实际版本确认。SHOW ENGINE INNODB STATUS 也可辅助查看最近的锁等待和死锁信息,但它提供的是某一时点的诊断内容,不能代替对等待双方事务的持续核对。

再确认执行计划和索引顺序

使用 EXPLAIN 查看查询是否走预期索引,尤其关注 key、type 和 rows。这只能说明优化器预计采用的访问方式,不能直接显示事务实际持有哪些锁。

EXPLAIN
SELECT id, k
FROM gap_demo
WHERE k = 20
FOR UPDATE;

确认 key 后,再查看索引定义及数据顺序:

SHOW INDEX FROM gap_demo;

SELECT id, k
FROM gap_demo
ORDER BY k, id;

对于组合索引,应按照完整键的排序规则画出相邻键值。例如 (a, b) 索引中的 (2, 10) 与 (2, 20) 是相邻键时,插入 (2, 15) 可能落在两者之间;但 a 值变化时,索引间隙的边界也会随之变化。

把等待插入值映射到间隙

确认等待语句要写入的索引键值,按索引顺序找到它前后的现存键,然后检查阻塞事务持有的锁是否覆盖该间隙。若会话 A 锁住的是 idx_k 上 10 与 20 之间的范围,而会话 B 正要插入 15,两者就具备了发生插入等待的条件。若等待值落在另一个间隙,则需继续检查是否存在其他锁、唯一约束检查或外键检查等因素。

诊断记录中建议同时保存事务开始时间、等待开始时间、SQL、索引名、等待值和锁模式。这样可以区分“某个间隙被保护”与“只是事务尚未结束”这两种容易混淆的现象。

结论边界与使用限制

间隙锁不是所有 SELECT 都会产生的普通读锁。普通一致性读通常读取事务可见的版本,不等同于 SELECT ... FOR UPDATE。而锁定读、UPDATE、DELETE 是否使用间隙锁,仍取决于隔离级别、查询类型和实际访问路径。

也不能把“插入卡住”直接等同于间隙锁。主键或唯一键冲突、外键检查、表级锁、元数据锁以及其他事务中的记录锁,都可能导致等待。应先确认等待关系和锁对象,再检查索引范围;如果锁对象不是预期的索引间隙,就应沿着实际等待链继续分析。

实际判断可以归纳为三步:先找到等待事务与阻塞事务,再确认两者涉及的索引和锁模式,最后依据索引排序把待插入键映射到具体间隙。MySQL 的锁视图用于验证当前锁,EXPLAIN 用于辅助确认访问路径,二者都不能单独替代对事务状态和索引边界的核对。

结论边界与使用限制配图

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

相关推荐