Gavin 的点滴记录

菜单

Administrator
发布于 2026-09-22 / 0 阅读
0
0

MySQL 死锁排查方式

一、背景

  • Spring Boot + Mybatis + HikariCP + MySQL 5.7

  • 核心表 card （卡表），几十万行，热点表

  • 多个定时任务并发更新：状态同步、流量同步、续期、出库等；另有统计任务定时对卡表做聚合

  • 隔离级别 READ_COMMITTED，auto-commit：true，innodb_print_all_deadlocks 未开启

二、现象

系统启动的时候，应用日志大量报错：

DeadlockLoserDataAccessException
### SQL: update card set ... where uid = ?

调用栈指向某个同步任务。第一反应：单表、按主键更新，为什么会死锁？

三、排查方式一：SHOW ENGINE INNODB STATUS（事后取证）

1、日志片段

SHOW ENGINE INNODB STATUS 会保留最近一次死锁的现场，包括两个事务的 SQL、持锁、等锁信息。

它的特点是事后取证：死锁发生时，InnoDB 把现场写进 LATEST DETECTED DEADLOCK 段落。即使事务已经被回滚、连接已经断开、PROCESSLIST 里什么都看不到，这段日志仍然保留在那里，直到下一次死锁把它覆盖。

2026-09-21 08:49:32 0x2b62c0d67700
*** (1) TRANSACTION:
TRANSACTION 58038316775, ACTIVE 46 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 866603, OS thread handle 47703177766656, query id 68902882445 localhost 127.0.0.1 root updating
update card ...


*** (2) TRANSACTION:
TRANSACTION 58038311673, ACTIVE 63 sec fetching rows
mysql tables in use 2, locked 2
32647 lock struct(s), heap size 2957520, 331468 row lock(s)
MySQL thread id 866602, OS thread handle 47703142070016, query id 68902856859 localhost 127.0.0.1 root Sending data
UPDATE t_summary s
INNER JOIN (
select xx from card 
) set xx = xx

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 1008 page no 50501 n bits 96 index PRIMARY of table `test`.`card` trx id 58038311673 lock mode S locks rec but not gap

2、读日志

事务1：

  • ACTIVE 46 sec：事务已经存活 46 秒；

  • LOCK WAIT：正在等锁；

  • 1 row lock(s)：已经持有 1 行锁

这三个信息放在一起，说明它持有 1 行锁，同时在等另一行锁。

这说明它在等当前锁时，已经持有了别的行的锁。可能是循环更新、批量 in 更新，也可能是前面执行过别的 update。但不代表事务范围一定大——ACTIVE 时间长，可能只是因为卡在等锁上。

和回滚的日志对不上，排查应用层其他使用事务的方法。

事务2：

  • 331468 row lock(s)：持有 33 万多个行锁；

  • lock mode S：这些是共享锁；

  • fetching rows：还在执行，没提交。

这是一个 UPDATE ... JOIN 统计任务，对同一张表加了大量 S 锁。

四、排查方式二：INNODB_TRX + PROCESSLIST（事中监控）

SHOW ENGINE INNODB STATUS 是事后取证，而死锁可能只是锁等待一段时间后抛异常结束，现场很快就没了。如果想在死锁发生前或锁等待过程中主动发现问题，可以用 information_schema.INNODB_TRX + PROCESSLIST 看当前正在跑的事务：

SELECT trx_id, trx_state, trx_started,
       TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS elapsed_sec,
       trx_mysql_thread_id, trx_query
FROM information_schema.INNODB_TRX
ORDER BY trx_started;

关注几个点：

  • trx_started 很早、elapsed_sec 很大：长时间未提交的事务，重点怀疑对象；

  • trx_query 指向批量更新或 UPDATE ... JOIN：和高冲突 SQL 对得上；

  • 结合 PROCESSLIST 看对应的连接和当前 SQL：

SELECT p.id, p.user, p.host, p.db, p.command, p.time, p.state, p.info,
       t.trx_id, t.trx_started
FROM information_schema.PROCESSLIST p
LEFT JOIN information_schema.INNODB_TRX t ON t.trx_mysql_thread_id = p.id
WHERE p.command != 'Sleep' OR t.trx_id IS NOT NULL;

配合 information_schema.INNODB_LOCK_WAITS（MySQL 5.7）还能直接看到谁在等谁：

SELECT r.trx_id AS waiting_trx, r.trx_mysql_thread_id AS waiting_thread,
       r.trx_query AS waiting_query,
       b.trx_id AS blocking_trx, b.trx_mysql_thread_id AS blocking_thread,
       b.trx_query AS blocking_query
FROM information_schema.INNODB_LOCK_WAITS w
JOIN information_schema.INNODB_TRX b ON b.trx_id = w.blocking_trx_id
JOIN information_schema.INNODB_TRX r ON r.trx_id = w.requesting_trx_id;

五、根因

1、一个带事务的批量更新，在热点表上逐行加 X 锁，事务存活时间长；

2、一个统计任务，用 UPDATE ... JOIN 对同一张表加大量 S 锁，长时间不提交；

3、S 锁和 X 锁互斥，两边交叉等待，死锁。

六、解决办法

1、统计任务侧：错开时间、加锁互斥、改成先查后更、加索引、读从库，避免对热点表加大量 S 锁；

2、卡更新侧：缩短事务、逐卡独立事务、按固定顺序更新、捕获死锁重试；

3、监控侧：开启 innodb_print_all_deadlocks，应用层捕获死锁异常并告警。

七、附录：innodb_print_all_deadlocks 参数说明

作用：把所有死锁信息记录到 MySQL 错误日志中，而不只是保留在 SHOW ENGINE INNODB STATUS 的 LATEST DETECTED DEADLOCK 段落里。

默认值：OFF

为什么默认关闭：避免频繁写日志带来的性能开销和磁盘占用。高并发场景下死锁频繁发生时，错误日志会快速增长。

开启方式：

-- 临时开启，立即生效，不需要重启
SET GLOBAL innodb_print_all_deadlocks = ON;

-- 永久生效，在 my.cnf 的 [mysqld] 段加一行
innodb_print_all_deadlocks = ON

性能影响：

  • 死锁不频繁（正常业务每小时几条）：日志量增加有限，建议长期开启；

  • 死锁频繁：会增加日志写入频率和 IO 开销，可能消耗磁盘空间，建议临时开启排查后再关闭。

和 SHOW ENGINE INNODB STATUS 的关系：SHOW ENGINE INNODB STATUS 只保留最近一次死锁，新的死锁会覆盖旧的；开启 innodb_print_all_deadlocks 后，每次死锁都会写入错误日志，不会被覆盖，才能拿到完整的排查现场。

异地组网方案全景概览

评论