SQL如何快速定位复杂的JOIN连接死锁_锁等待监控与诊断报告-SQL-PHP中文网

show engine innodb status 是诊断死锁的首选命令，其 latest detected deadlock 区块提供真实现场，需重点分析 waiting for this lock 和 holds the lock(s) 行，结合 performance_schema.data_locks 实时监控锁等待链。

sql如何快速定位复杂的join连接死锁_锁等待监控与诊断报告

死锁发生时，`SHOW ENGINE INNODB STATUS` 是唯一能立刻看到现场的命令

MySQL 的死锁不是“卡住”，而是事务互相持有对方需要的锁、又申请对方持有的锁，InnoDB 自动选一个事务回滚。真正要诊断的，不是“为什么卡”，而是“哪两个事务在争什么资源”。SHOW ENGINE INNODB STATUS 输出里 LATEST DETECTED DEADLOCK 区块就是全部线索——它不骗人，但信息密、字段缩写多，容易看漏关键行。

重点盯紧 WAITING FOR THIS LOCK TO BE GRANTED 和 HOLDS THE LOCK(S) 两段：前者是当前被堵住的 SQL 和等待的锁类型（如 lock_mode X locks rec but not gap waiting），后者是另一个事务正在占着什么记录或间隙
每个事务的 TRANSACTION 行后面跟着 mysql tables in use 和 locked tables，能快速确认是否涉及 JOIN 多表、有没有显式锁表（比如 LOCK TABLES）
别只看 SQL 文本——注意 index 名和 space id/page number，相同 page number 往往意味着冲突发生在同一数据页上，JOIN 条件没走索引时极易出现

用 `performance_schema.data_locks` 实时查谁在等、谁在占

相比 SHOW ENGINE INNODB STATUS 的快照式输出，performance_schema.data_locks 提供实时锁视图，尤其适合在死锁频发但没立刻触发时主动排查。它把每把锁拆成独立记录，配合 data_lock_waits 能直接连出等待链。

必须提前开启：确认 performance_schema 已启用，且 innodb_monitor_enable = 'all' 或至少 'lock_wait'；否则 data_locks 和 data_lock_waits 为空

常用查询模式：

SELECT r.trx_id waiting_trx_id, r.trx_mysql_thread_id waiting_thread,<br>       b.trx_id blocking_trx_id, b.trx_mysql_thread_id blocking_thread,<br>       r.lock_trx_id, r.lock_mode, r.lock_type, r.lock_table<br>FROM performance_schema.data_lock_waits w<br>JOIN performance_schema.data_locks r ON w.BLOCKING_LOCK_ID = r.ENGINE_LOCK_ID<br>JOIN performance_schema.data_locks b ON w.REQUESTING_LOCK_ID = b.ENGINE_LOCK_ID<br>JOIN information_schema.INNODB_TRX rt ON r.lock_trx_id = rt.trx_id<br>JOIN information_schema.INNODB_TRX bt ON b.lock_trx_id = bt.trx_id;

JOIN 场景下，lock_table 常显示多个表名（如 `db`.`t1`, `db`.`t2`），说明锁跨表；若 lock_type = 'RECORD' 但 lock_data 为空，大概率是全表扫描导致锁了整页——这时得回头检查 JOIN 条件列有没有索引

`EXPLAIN FORMAT=JSON` 看 JOIN 执行计划里隐含的锁风险点

很多死锁不是逻辑写错，而是优化器选了意料之外的驱动表或访问路径，导致锁范围远超预期。比如本该用 t1.id 驱动 t2，结果因统计信息偏差改用 t2.status 驱动，而 t2.status 上只有低基数索引，锁住几百行。

重点关注 JSON 输出里的 "used_columns" 和 "key_length"：如果 key_length 远小于索引定义长度（比如索引是 (a,b,c) 但只用了前 1 字节），说明只走了最左前缀，后续条件在内存中过滤，InnoDB 仍会对所有扫描到的记录加锁
"rows_examined_per_scan" 值大 + "filtered" 值低（比如 10%），基本等于告诉 InnoDB：“我打算锁几千行，再筛出几十行”——高并发下这就是死锁温床
特别警惕 "type": "ALL" 或 "type": "index" 出现在 JOIN 的非驱动表上：这意味着对整个二级索引或聚簇索引做扫描，锁范围完全失控

监控脚本里别只抓 `Deadlock found when trying to get lock` 错误日志

应用层捕获到这个错误，说明死锁已经发生并回滚，属于事后响应。真正要防的是锁等待升级为死锁的过程。MySQL 的 INFORMATION_SCHEMA.INNODB_TRX 表里，trx_state = 'LOCK WAIT' 且 trx_wait_started 超过 3 秒的事务，就是高危信号。

写巡检脚本时，别只 grep 错误日志——用 SQL 查：

SELECT trx_id, trx_mysql_thread_id, trx_query,<br>       TIMESTAMPDIFF(SECOND, trx_wait_started, NOW()) wait_sec<br>FROM information_schema.INNODB_TRX<br>WHERE trx_state = 'LOCK WAIT' AND<br>      TIMESTAMPDIFF(SECOND, trx_wait_started, NOW()) > 3;

JOIN 场景下，trx_query 里若含多个 UPDATE ... JOIN 或 DELETE ... JOIN，优先级应高于单表语句——这类语句锁粒度更难控制，且往往出现在业务关键路径
注意 trx_isolation_level：如果是 REPEATABLE READ，间隙锁（gap lock）会参与死锁计算；换成 READ COMMITTED 可禁用间隙锁，但得同步确认业务能否接受幻读——这不是开关一调就完事，得测

锁等待本身不可怕，可怕的是等待期间还在执行复杂 JOIN，把几秒的等待拖成几十秒，中间任何一次新请求都可能补上最后一块拼图，让两个等待事务正式握手成死锁。查 data_lock_waits 和盯 INNODB_TRX 的 wait 时间，比等日志报错有用得多。