间隙锁排查实战：一条SQL揪出阻塞元凶

关键词间隙锁、锁等待、performance_schema、SHOW ENGINE INNODB STATUS、死锁大家好呀我是数据库小学妹上一篇我们搞懂了MVCC和锁联手如何解决幻读知道了Next-Key Lock行锁间隙锁是RR隔离级别下的“幻读克星”。但理论懂了实战中遇到问题时怎么快速定位比如事务突然卡住、插入被阻塞——这背后很可能藏着一个间隙锁幽灵在作祟今天我们就用MySQL自带的监控工具手把手揪出间隙锁让问题无所遁形一、场景模拟间隙锁引发的“插入阻塞”假设订单表orders有索引字段order_date。事务A执行了范围锁定--- 事务A BEGIN; SELECT * FROM orders WHERE order_date BETWEEN 2026-05-01 AND 2026-05-10 FOR UPDATE; -- 事务A 未提交此时事务B试图插入一条新订单-- 事务B另一个会话 INSERT INTO orders (order_id, order_date) VALUES (1005, 2026-05-05);​结果​事务B被​卡住不动​。​原因​事务A的间隙锁锁住了order_date在[2026-05-01, 2026-05-10]范围内的“间隙”导致新插入被阻塞。​二、排查利器1SHOW ENGINE INNODB STATUS​——快速定位锁等待当系统出现卡顿时第一步登录MySQL执行SHOW ENGINE INNODB STATUS\G在输出中搜索TRANSACTIONS段落找到处于LOCK WAIT的事务。关键信息WAITING FOR THIS LOCK TO BE GRANTED显示正在等待的锁类型如lock_mode X locks gap before rec​锁的范围​例如RECORD LOCKS ... index order_date ... lock_mode X locks gap before rec​阻塞事务ID​找到持有锁的事务事务A 如果看到LATEST DETECTED DEADLOCK说明发生了死锁InnoDB已自动回滚一个事务需进一步分析。​三、排查利器2performance_schema​——深入锁细节MySQL 5.7版本提供了更强大的性能监控工具performance_schema。执行以下查询直接获取锁等待关系SELECT OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, BLOCKING_ENGINE_TRANSACTION_ID, REQUESTING_ENGINE_TRANSACTION_ID FROM performance_schema.data_lock_waits JOIN performance_schema.data_locks ON data_lock_waits.REQUESTING_ENGINE_LOCK_ID data_locks.ENGINE_LOCK_ID;重点关注字段LOCK_MODE X,GAP→ 排他间隙锁LOCK_STATUS WAITING→ 谁在等BLOCKING_ENGINE_TRANSACTION_ID→ 谁在阻塞拿到阻塞事务ID后到INNODB_TRX表查具体SQLSELECT trx_id, trx_state, trx_started, trx_query FROM information_schema.INNODB_TRX WHERE trx_id 阻塞事务ID;四、实战解读一个典型的间隙锁日志执行SHOW ENGINE INNODB STATUS后你会看到类似这样的片段---TRANSACTION 31045, ACTIVE 10 sec 2 lock struct(s), heap size 1136, 1 row lock(s) MySQL thread id 8, query id 123 localhost root SELECT * FROM orders WHERE status pending FOR UPDATE ---TRANSACTION 31046, ACTIVE 5 sec 1 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1 MySQL thread id 9, query id 124 localhost root INSERT INTO orders (id, status, amount) VALUES (7, pending, 150) ------- TRX HAS BEEN WAITING 5 sec FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 28 page no 4 n bits 72 index idx_status of table test.orders trx id 31046 lock_mode X locks gap before rec​结论​事务31046INSERT在等待一个lock_mode X locks gap before rec间隙锁持有者是事务31045FOR UPDATE查询。五、避坑指南间隙锁排查的3个易错点易错点正确理解间隙锁 ≠ 死锁间隙锁导致锁等待需手动处理死锁自动回滚日志会记录RC级别无间隙锁读已提交RC下不会产生间隙锁。若performance_schema仍显示gap可能是视图残留以实际隔离级别为准无索引 表锁陷阱WHERE条件无索引时间隙锁会升级为全表锁性能暴跌务必先用EXPLAIN检查 我是数据库小学妹一个用设计师思维学数据库的转行人。我们一起把复杂的技术变得简单有趣本文示例基于 ​​MySQL​​ 8.0。不同版本锁表名有差异5.7用​*INNODB_LOCKS​等请自行适配。*