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。最小证据集包括最近一次死锁、当前事务、当前等待关系和连接上下文。下面这些查询都只读系统状态,适合作为故障初始快照。
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 都描述快速变化的内部状态,不应假设几条查询在不同时间点一定组成完全一致的快照,所以事故记录中要保留采集时间。
如果死锁不是偶发而是持续出现,可以在确认日志容量与合规要求后临时开启全量死锁记录:
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:
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 或应用日志定位业务动作。
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:
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,而且故障恢复速度未必比继续等待更快。处置顺序建议固定为:确认业务身份和事务作用 → 评估受影响请求 → 估算已修改数据量和回滚成本 → 判断能否业务降级或停止新增流量 → 最后决定是否终止连接。
需要终止时要区分两个命令:
KILL QUERY <ID>;
KILL CONNECTION <ID>;KILL QUERY 只停止当前语句,不代表已经结束整个事务;如果连接仍保持未提交事务,锁可能继续存在。KILL CONNECTION 会断开会话,未提交事务需要由服务器回滚,因此更适合确认必须结束整个事务的场景。执行前应把 <ID> 与 INNODB_TRX.trx_mysql_thread_id、业务实例和 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 开始猜。