MySQL / 技术文章

MySQL 死锁与长事务怎么排查

线上出现 ERROR 1213、请求突刺、连接池耗尽或 DDL 长时间卡住时,真正困难的不是“知道有锁”,而是判断死锁是否已经结束、谁还在持锁、哪一个长事务正在放大故障,以及现在能不能安全终止它;这份排查路径面向后端工程师、SRE 与 DBA,目标是在保全证据后把故障归因到具体事务、SQL、锁对象和业务动作,并给出处置优先级。

先分清死锁和长事务是不是同一个问题

死锁和长事务经常同时出现,但它们不是同一种现场。InnoDB 检测到死锁后通常会选择一个事务回滚,因此应用看到 ERROR 1213 (40001) 时,形成闭环的那组锁可能已经消失;此时继续只看实时锁等待,往往什么也抓不到。长事务则可能仍然存活,持续持锁、保留旧版本、拖慢 purge,或者挡住 DDL 的 Metadata Lock。

排查时先按现象分流:

  • 1213 Deadlock found:优先保存死锁日志,恢复事务之间的加锁顺序和冲突对象。
  • 1205 Lock wait timeout exceeded:它只是等待超过阈值,不等于发生死锁,应继续寻找 blocker。
  • 请求一直慢、没有明显报错:检查 INNODB_TRX、实时锁等待和连接状态,重点找事务年龄异常的会话。
  • DDL 卡住而普通 SQL 也开始排队:除行锁外,还要检查 performance_schema.metadata_locks。
  • 死锁频繁且总伴随同一批业务 SQL:不要只提高超时或加重试,应回到索引访问、事务边界与资源更新顺序。

一个实用原则是:死锁先查“刚刚发生过什么”,长事务先查“现在还有谁没结束”。

第一现场先保全四类证据

事故发生后先采证,再做 KILL。最小证据集包括最近一次死锁、当前事务、当前等待关系和连接上下文。下面这些查询都只读系统状态,适合作为故障初始快照。

sql
SHOW ENGINE INNODB STATUS\G

SELECT
  trx_id,
  trx_state,
  trx_started,
  TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS trx_age_s,
  trx_wait_started,
  trx_mysql_thread_id,
  trx_rows_locked,
  trx_rows_modified,
  trx_query
FROM information_schema.innodb_trx
ORDER BY trx_started;

SELECT
  requesting_engine_transaction_id AS waiting_trx_id,
  blocking_engine_transaction_id AS blocking_trx_id,
  requesting_engine_lock_id,
  blocking_engine_lock_id
FROM performance_schema.data_lock_waits;

SHOW FULL PROCESSLIST;

采集后至少记下故障时间、实例、事务 ID、PROCESSLIST ID、业务请求或 trace ID、SQL 文本、等待开始时间以及是否已经执行过人工处置。INNODB_TRX、data_locks 和 data_lock_waits 都描述快速变化的内部状态,不应假设几条查询在不同时间点一定组成完全一致的快照,所以事故记录中要保留采集时间。

如果死锁不是偶发而是持续出现,可以在确认日志容量与合规要求后临时开启全量死锁记录:

sql
SET GLOBAL innodb_print_all_deadlocks = ON;

事故结束后应按运维策略决定是否恢复该变量,避免长期增加错误日志噪声。

查死锁要还原等待闭环而不是只看被回滚 SQL

SHOW ENGINE INNODB STATUS 的 LATEST DETECTED DEADLOCK 是第一入口。阅读时不要只盯着 WE ROLL BACK TRANSACTION,而要为每个事务分别记录四件事:正在执行的 SQL、已经持有的锁、正在等待的锁、锁落在哪个表和索引上。最终要得到类似“事务 A 持有 R1 等 R2,事务 B 持有 R2 等 R1”的 wait-for cycle。

如果冲突仍然存在,可以把 data_lock_waits 与 data_locks、threads 连起来,直接看到 waiter、blocker、锁对象和连接 ID:

sql
SELECT
  w.REQUESTING_ENGINE_TRANSACTION_ID AS waiting_trx_id,
  wt.PROCESSLIST_ID AS waiting_processlist_id,
  wl.OBJECT_SCHEMA AS waiting_schema,
  wl.OBJECT_NAME AS waiting_table,
  wl.INDEX_NAME AS waiting_index,
  wl.LOCK_TYPE AS waiting_lock_type,
  wl.LOCK_MODE AS waiting_lock_mode,
  w.BLOCKING_ENGINE_TRANSACTION_ID AS blocking_trx_id,
  bt.PROCESSLIST_ID AS blocking_processlist_id,
  bl.LOCK_MODE AS blocking_lock_mode
FROM performance_schema.data_lock_waits AS w
JOIN performance_schema.data_locks AS wl
  ON wl.ENGINE_LOCK_ID = w.REQUESTING_ENGINE_LOCK_ID
JOIN performance_schema.data_locks AS bl
  ON bl.ENGINE_LOCK_ID = w.BLOCKING_ENGINE_LOCK_ID
LEFT JOIN performance_schema.threads AS wt
  ON wt.THREAD_ID = wl.THREAD_ID
LEFT JOIN performance_schema.threads AS bt
  ON bt.THREAD_ID = bl.THREAD_ID;

拿到闭环后再问“为什么这两条 SQL 会同时锁到这些对象”。高频根因通常落在三类:不同事务以相反顺序更新同一组资源;缺少合适索引导致扫描和锁定范围扩大;事务内包含外部调用、批处理或无关逻辑,使本来很短的锁窗口被拉长。死锁本身由 InnoDB 自动打破,但应用仍应把 1213 当成可重试的事务级失败,并确保重试具备幂等性,而不是把一半业务动作留在事务外。

查长事务先找最老事务再判断它造成了什么

长事务不能只按“执行中的 SQL 很慢”来找。连接可能处于 Sleep,但事务仍未提交;trx_query 也可能为空,因此要以事务开始时间和线程 ID 为主线,再回到 PROCESSLIST 或应用日志定位业务动作。

sql
SELECT
  t.trx_id,
  t.trx_state,
  t.trx_started,
  TIMESTAMPDIFF(SECOND, t.trx_started, NOW()) AS trx_age_s,
  t.trx_wait_started,
  t.trx_mysql_thread_id AS processlist_id,
  p.USER,
  p.HOST,
  p.DB,
  p.COMMAND,
  p.TIME AS processlist_time_s,
  t.trx_rows_locked,
  t.trx_rows_modified,
  t.trx_query,
  p.INFO AS processlist_sql
FROM information_schema.innodb_trx AS t
LEFT JOIN information_schema.processlist AS p
  ON p.ID = t.trx_mysql_thread_id
ORDER BY t.trx_started;

不要预先写死“超过 30 秒就是异常”之类阈值。批量结算、在线迁移、报表、后台任务的正常事务长度不同,判断应同时看事务年龄、修改行数、持锁或等待情况、业务重要性和实例资源压力。更值得警惕的是“事务很老 + 修改很多 + 正在阻塞别人”,或者“事务很老 + 没有活跃 SQL + 应用连接长期未提交”。

如果线上同时有锁等待,再用阻塞关系确认这个老事务是不是根 blocker;如果没有行锁等待但 DDL 卡住,则补查 Metadata Lock:

sql
SELECT
  OBJECT_TYPE,
  OBJECT_SCHEMA,
  OBJECT_NAME,
  LOCK_TYPE,
  LOCK_DURATION,
  LOCK_STATUS,
  OWNER_THREAD_ID
FROM performance_schema.metadata_locks
WHERE LOCK_STATUS = 'PENDING';

看到 PENDING 只能说明有人在等 MDL,还要用 OWNER_THREAD_ID 回查 performance_schema.threads,再结合事务状态找出真正持有或排在前面的会话,不能直接把等待者当成根因。

处置长事务时先算回滚成本再决定是否终止

发现 blocker 后最危险的动作是立刻 KILL。对修改量很大的长事务,终止连接会触发回滚,回滚期间可能继续消耗 I/O 和 CPU,而且故障恢复速度未必比继续等待更快。处置顺序建议固定为:确认业务身份和事务作用 → 评估受影响请求 → 估算已修改数据量和回滚成本 → 判断能否业务降级或停止新增流量 → 最后决定是否终止连接。

需要终止时要区分两个命令:

sql
KILL QUERY <ID>;
KILL CONNECTION <ID>;

KILL QUERY 只停止当前语句,不代表已经结束整个事务;如果连接仍保持未提交事务,锁可能继续存在。KILL CONNECTION 会断开会话,未提交事务需要由服务器回滚,因此更适合确认必须结束整个事务的场景。执行前应把 <ID> 与 INNODB_TRX.trx_mysql_thread_id、业务实例和 SQL 再核对一次,避免杀错连接。

处置后不要以“命令返回成功”作为恢复标准,而要重新验证:

sql
SELECT COUNT(*) AS waiting_edges
FROM performance_schema.data_lock_waits;

SELECT
  COUNT(*) AS active_trx,
  MAX(TIMESTAMPDIFF(SECOND, trx_started, NOW())) AS oldest_trx_age_s
FROM information_schema.innodb_trx;

SHOW GLOBAL STATUS LIKE 'Threads_running';

真正恢复应表现为关键等待边消失、最老异常事务退出、应用侧超时与连接池排队下降,并且没有因为大事务回滚出现新的资源尖峰。

从一次事故反推出代码和监控改造

复盘不要只记录“杀了哪个线程”。死锁需要落到具体资源顺序和 SQL 访问路径,长事务需要落到事务边界和提交责任。可以按下面的映射生成整改项:

  • 同一对 SQL 反复死锁:统一资源更新顺序,检查过滤条件是否命中预期索引,并缩小单次事务修改范围。
  • 长事务包含 RPC、HTTP、消息等待或大量计算:把非数据库工作移出事务,缩短从首次加锁到提交的时间。
  • Sleep 连接长期挂着事务:检查连接池、异常分支和框架事务注解,确保成功、失败和超时路径都能提交或回滚。
  • DDL 经常等待 MDL:上线前增加长事务和 metadata lock 门禁,不在高峰期直接赌锁窗口。
  • 事故只能靠人工现查:持续采集死锁增量、当前锁等待数量、最长等待时长、最老事务年龄、回滚中的事务以及连接池等待。

最终的排障记录至少应回答六个问题:哪条业务请求触发了事务、事务何时开始、访问了哪些表和索引、谁在等谁、为什么锁窗口被放大、改造后用什么指标证明问题没有复发。这样下一次遇到 1213、1205、连接堆积或 DDL 卡住时,团队执行的是一条可验证的诊断路径,而不是从 SHOW PROCESSLIST 开始猜。

目录与阅读设置

记录一个想法

MySQL 死锁与长事务怎么排查

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通