如何在MySQL5.7中排查因事务隔离级别导致的间隙锁与索引竞争？-mysql教程-PHP中文网

mysql 5.7中间隙锁是否生效取决于事务隔离级别、查询是否走索引及是否涉及唯一键检查；需通过@@tx_isolation、innodb_locks_unsafe_for_binlog、binlog_format、explain和innodb_locks等交叉验证。

如何在mysql5.7中排查因事务隔离级别导致的间隙锁与索引竞争？

MySQL 5.7 中，间隙锁（GAP lock）是否生效、何时生效、和哪些索引交互，完全取决于事务隔离级别 + 查询是否走索引 + 是否涉及唯一键检查。不是“RR 就一定有 GAP，RC 就一定没有”，更不是“加了索引就安全”。排查必须从这三个变量交叉切入。

很多人以为 SET TRANSACTION ISOLATION LEVEL REPEATABLE READ 执行成功就万事大吉，但实际可能被会话级配置或全局参数覆盖：

SELECT @@tx_isolation 必须返回 REPEATABLE-READ，不能是 READ-COMMITTED 或空值（某些客户端连接时会默认覆盖）
检查 SELECT @@innodb_locks_unsafe_for_binlog —— 若为 ON，即使在 RR 下也会禁用大部分 GAP 锁（该参数已弃用，但旧配置仍有效）
确认 binlog_format 是 ROW：若为 MIXED 或 STATEMENT，部分场景下 InnoDB 可能规避 GAP 锁以保证复制安全，导致行为不一致

间隙锁只作用于索引，且对唯一索引的 duplicate-key 检查是强制行为——哪怕你在 RC 级别，INSERT ... ON DUPLICATE KEY UPDATE 依然会申请 GAP 锁。所以第一步不是看隔离级别，而是看执行计划：

MySQL

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

下载

对疑似阻塞的 SQL 执行 EXPLAIN FORMAT=JSON，确认 key 字段非 NULL，且 possible_keys 包含你预期的唯一索引（如 songId_idx）
若 type 是 ALL 或 index，说明没走有效索引 → InnoDB 只能全表扫描加锁 → 表现为“像锁了整张表”，此时 GAP 锁范围扩大到整个索引 B+ 树的间隙，极易冲突
注意隐式类型转换：比如 WHERE user_no = 1001（字段是 VARCHAR），会导致索引失效，触发全扫描加锁

看到 lock_mode X locks gap before rec 别急着归因于“隔离级别设错了”。它在不同上下文含义完全不同：

出现在 INSERT 或 INSERT ... ON DUPLICATE KEY UPDATE 的等待行中 → 大概率是插入意向锁（insert intention）在等唯一键检查所需的 GAP 锁释放，和业务逻辑无关，但和并发插入同范围值强相关
出现在 SELECT ... FOR UPDATE 的持有行中，且 lock_index 是非唯一普通索引（如 idx_user_name）→ 这才是 RR 下典型的间隙锁行为，用于防止幻读
若 lock_mode 是 S + gap before rec，且事务正在执行 INSERT ... ON DUPLICATE KEY UPDATE → 这个 S 锁是 InnoDB 为做 duplicate-key 检查而自动加的，不是你写的 SQL 主动请求的

INNODB_LOCK_WAITS 在 GAP 场景下基本不可信，因为它只记录 RECORD 级阻塞链。真正要定位冲突根源，得靠 INNODB_LOCKS 的物理三元组：

先从死锁日志中提取两个事务等待的 lock_index（如 un_idx_user_no）、lock_space、lock_page 和近似 lock_rec（如 heapno=5）
再查 SELECT * FROM information_schema.INNODB_LOCKS WHERE lock_index = 'un_idx_user_no' AND lock_space = XXX AND lock_page = YYY
若事务 A 持有 lock_mode = X + lock_type = RECORD，事务 B 等待 lock_mode = X + lock_type = INSERT_INTENTION，且两者 lock_page 相同但 lock_rec 不同 → 典型 GAP + 插入意向锁循环等待，和隔离级别无关，只和索引结构与并发插入值分布有关