SELECT ... FOR UPDATE 阻塞其他事务的根本原因是InnoDB行级锁机制：命中索引时加X锁，未走索引则升级为间隙锁或表锁；EXPLAIN可确认是否走索引，避免隐式类型转换与无意义全表加锁。

为什么 SELECT ... FOR UPDATE 会阻塞其他事务

根本原因在于 InnoDB 的行级锁机制：当事务 A 执行 SELECT ... FOR UPDATE 并命中索引时，InnoDB 会对匹配的索引记录加 X（排他）锁；如果查询未走索引，会升级为表级锁或锁住整个索引范围（间隙锁），导致事务 B 即使查不同主键也会被卡住。

• 确认是否走索引：执行 EXPLAIN SELECT ... FOR UPDATE，检查 key 和 rows 字段；key 为 NULL 或 rows 远大于实际结果数，大概率触发了全扫描+间隙锁
• 避免隐式类型转换：比如 WHERE user_id = '123'（user_id 是 INT），MySQL 会放弃索引，改用全表扫描加锁
• 不要在高并发路径上对无业务意义的字段加锁，例如 SELECT * FROM order WHERE status = 'pending' FOR UPDATE —— 这很可能锁住数百行，且后续 UPDATE 只改其中一行

SHOW ENGINE INNODB STATUS 中的死锁信息怎么看

该命令输出里 TRANSACTIONS 部分末尾的 LATEST DETECTED DEADLOCK 是关键。它不是实时日志，只保留最近一次死锁详情，但足够定位冲突源头。

• 重点关注 *** (1) WAITING FOR THIS LOCK TO BE GRANTED: 和 *** (2) HOLDS THE LOCK(S): 两段 —— 它们说明谁在等什么锁、谁持有什么锁
• 注意事务持有的锁类型：lock_mode X locks rec but not gap 是普通行锁；lock_mode X locks gap before rec 是间隙锁；lock_mode X 单独出现通常意味着临键锁（next-key lock）
• 对比两个事务的 SQL，往往能发现“事务 A 锁了 ID=5，再试图锁 ID=10；事务 B 先锁 ID=10，再试图锁 ID=5”这类循环依赖

------------------------
LATEST DETECTED DEADLOCK
------------------------
2025-04-10 14:22:33 0x7f8b1c01a700
*** (1) TRANSACTION:
TRANSACTION 123456, ACTIVE 2 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 101, OS thread handle 140221234567890, query id 2001 localhost root updating
UPDATE account SET balance = balance - 100 WHERE id = 5
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 123 page no 102 n bits 72 index PRIMARY of table `test`.`account` trx id 123456 lock_mode X locks rec but not gap waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
*** (2) TRANSACTION:
TRANSACTION 123457, ACTIVE 3 sec starting index read, thread declared inside InnoDB 1
mysql tables in use 1, locked 1
2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 102, OS thread handle 140221234567891, query id 2002 localhost root updating
UPDATE account SET balance = balance + 100 WHERE id = 10
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 123 page no 102 n bits 72 index PRIMARY of table `test`.`account` trx id 123457 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 123 page no 103 n bits 72 index PRIMARY of table `test`.`account` trx id 123457 lock_mode X locks rec but not gap waiting

如何用 performance_schema 实时抓取锁等待链

相比 SHOW PROCESSLIST，performance_schema 能暴露更底层的锁等待关系，尤其适合排查“谁在等谁”的长链问题（如 A→B→C→D）。

• 确保已启用相关消费者：UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE NAME IN ('events_waits_current', 'events_waits_history');
• 核心查询是连接 data_lock_waits 和 data_locks 表，过滤 OBJECT_SCHEMA 和 LOCK_STATUS = 'PENDING'
• 注意 LOCK_TRX_ID 和 BLOCKING_TRX_ID 是十六进制字符串，需用 CONV(..., 16, 10) 转成十进制才能和 INFORMATION_SCHEMA.INNODB_TRX 关联

SELECT 
  r.trx_id waiting_trx_id,
  r.trx_mysql_thread_id waiting_thread,
  r.trx_query waiting_query,
  b.trx_id blocking_trx_id,
  b.trx_mysql_thread_id blocking_thread,
  b.trx_query blocking_query
FROM performance_schema.data_lock_waits w
INNER JOIN information_schema.INNODB_TRX b ON b.trx_id = CONV(w.BLOCKING_TRX_ID, 16, 10)
INNER JOIN information_schema.INNODB_TRX r ON r.trx_id = CONV(w.REQUESTING_TRX_ID, 16, 10);

间隙锁（Gap Lock）引发的意外阻塞怎么验证

间隙锁本身不锁数据行，只锁索引区间，但它是可重复读（RR）隔离级别下防止幻读的核心机制——也是很多“没动这行却卡住”的根源。

• 复现方法：事务 A 执行 SELECT * FROM t WHERE id > 5 AND id （假设当前只有 id=6、7 两行），此时事务 B 插入 id=8 会被阻塞，即使该值尚未存在
• 验证是否启用了间隙锁：关闭 innodb_locks_unsafe_for_binlog（默认 OFF），并确认隔离级别是 REPEATABLE-READ；若改为 READ-COMMITTED，间隙锁自动禁用（但会丢失可重复读语义）
• 线上慎用 INSERT ... SELECT 或大范围 UPDATE 带 FOR UPDATE，它们极易触发大面积间隙锁，拖慢整个表的写入

锁的粒度和持续时间比大多数人想象中更隐蔽。一个没加索引的 WHERE 条件、一次跨索引的 ORDER BY、甚至客户端连接超时未提交事务，都可能让锁滞留数分钟。分析时别只盯着 SQL 本身，先看执行计划、再查锁状态、最后关联线程堆栈。