数据库服务器出现MySQL死锁,怎样从死锁日志定位冲突语句和事务?

发布人:A5数据发布时间:2026-10-26 17:46阅读量:152

MySQL 死锁通常表现为事务报错、业务请求重试,或少量写入突然失败;但仅凭错误码无法知道哪两条语句互相等待。定位时要把 InnoDB 死锁报告中的事务、索引和锁信息,与数据库错误日志、应用请求日志及事务执行顺序对应起来。报告能说明数据库检测到的锁等待环,却不一定包含事务从开始到回滚的全部 SQL,因此不能只看最后一条语句就认定它是唯一原因。

先确认是死锁,还是普通锁等待

应用端如果收到 MySQL 错误码 1213(SQLSTATE 40001),通常表示事务因死锁被回滚;1205(SQLSTATE HY000)通常表示锁等待超时。两者处理方式和证据不同:死锁报告描述的是形成循环等待并被 InnoDB 打断的事务,锁等待超时则不一定存在循环,也不一定会产生对应的死锁报告。

先确认是死锁,还是普通锁等待配图

先从应用日志记录的数据库错误码、发生时间、请求 ID 和重试结果入手。应用若统一把数据库异常包装成“写入失败”,应先确认原始错误码;否则可能把连接中断、约束冲突或锁等待超时误判为死锁。还要区分数据库事务被回滚与业务请求最终失败:应用可能重试后成功,也可能因重试未覆盖整个事务而留下不一致处理风险。

在数据库侧,先核对版本和错误日志位置:

SELECT VERSION();
SHOW GLOBAL VARIABLES LIKE 'log_error';
SHOW GLOBAL VARIABLES LIKE 'innodb_print_all_deadlocks';

这些查询只读取版本和变量。log_error 显示错误日志路径;值为空或为 stderr 时,日志可能由启动脚本、容器平台或系统服务接管,不能直接假定存在某个固定文件。可结合 MySQL 服务的启动方式确认日志实际落点。不同发行版的服务名、日志目录和日志收集方式可能不同,应先核验环境,再决定查看哪个文件或平台日志。

先取当前可用的死锁报告

查看最近一次 InnoDB 死锁

在 MySQL 命令行客户端中执行:

SHOW ENGINE INNODB STATUS\G

这是只读检查,不会修改数据。输出中找到 LATEST DETECTED DEADLOCK 段落;它通常包含最近一次被 InnoDB 记录的死锁,以及参与事务、等待锁和导致回滚的事务信息。该段并不是完整的历史记录:新死锁发生后,之前的报告可能被覆盖;而且在排查时如果死锁已经过去一段时间,当前报告也可能对应另一次事件。

如果没有这段内容,不能据此断定没有死锁。可能是查看时间较晚、当前报告已更新,或死锁日志没有保留在所查位置。此时应核对应用中的错误时间和数据库时区,并查看错误日志、监控平台或已有的数据库诊断记录。

需要连续收集时,临时记录所有死锁

如果问题间歇出现,SHOW ENGINE INNODB STATUS 的最近一次记录可能不够用。可在确认日志收集、磁盘空间和权限后,临时打开 InnoDB 死锁日志:

SET GLOBAL innodb_print_all_deadlocks = ON;

该设置会让后续检测到的死锁写入 MySQL 错误日志,不会补写设置之前的事件。它会增加日志内容,适合有明确观察窗口的排查,不建议在不了解日志保留策略时长期打开。此操作需要相应管理权限;不同 MySQL 版本的授权要求可能不同。

操作前记录原值:

SHOW GLOBAL VARIABLES LIKE 'innodb_print_all_deadlocks';

排查结束后恢复原值。如果原值为 OFF,执行:

SET GLOBAL innodb_print_all_deadlocks = OFF;

若原值原本为 ON,则不要关闭。恢复变量不影响已写入的日志。日志文件是否需要轮转或归档,应按现有日志管理方式处理,不要为了排查直接删除错误日志。

读懂死锁报告:由事务、锁到 SQL

一份典型报告的结构大致如下。以下内容是用于说明字段含义的示例,不代表任何实际环境:

*** (1) TRANSACTION:
TRANSACTION 812345, ACTIVE 2 sec starting index read
mysql tables in use 1, locked 1
MySQL thread id 52, query id 90123
UPDATE inventory SET quantity = quantity - 1 WHERE id = 10

*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 41 page no 7 n bits 80 index PRIMARY of table `shop`.`inventory`
lock_mode X locks rec but not gap

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 41 page no 7 n bits 80 index PRIMARY of table `shop`.`inventory`
lock_mode X locks rec but not gap waiting

*** (2) TRANSACTION:
TRANSACTION 812346, ACTIVE 2 sec
MySQL thread id 53, query id 90124
UPDATE inventory SET quantity = quantity - 1 WHERE id = 11

*** WE ROLL BACK TRANSACTION (2)

分析时按以下顺序读取,避免把某一个 SQL 片段孤立地当成根因。

读懂死锁报告:由事务、锁到 SQL配图

  1. 区分事务编号和连接编号。 TRANSACTION 后的数字是 InnoDB 事务 ID;MySQL thread id 是连接线程 ID。前者用于识别报告中的事务,后者适合与数据库连接、应用连接池记录关联,二者不是同一个编号。
  2. 查看事务状态与已执行语句。 ACTIVE 后的时间表示报告生成时事务活动时间的大致信息;事务处于读、更新或其他状态的描述可以提供线索。报告中的 SQL 往往是事务等待时相关的当前语句,不等于事务中此前执行过的所有语句。
  3. 确认“持有”与“等待”的对象。 HOLDS THE LOCK(S) 表示该事务已持有的相关锁;WAITING FOR THIS LOCK TO BE GRANTED 表示它还要等待的锁。比较两个事务是否分别持有对方需要的锁,才能看出循环等待。
  4. 核对表、索引和锁模式。 table、index、lock_mode 等字段说明冲突落在哪张表、哪个索引以及锁的大致类型。X 通常表示排他锁,S 通常表示共享锁;是否涉及间隙锁,要结合报告中的 gap、rec but not gap、insert intention 等文字和隔离级别、执行计划判断。不要只凭一个锁标记推断完整执行路径。
  5. 确认被回滚的事务。 WE ROLL BACK TRANSACTION 指出此次死锁中 InnoDB 选择回滚的事务。它表示谁被选为牺牲者,不代表该事务单方面制造了死锁。应用通常需要把整个事务按业务允许的方式重试,而不是只重放最后一条 SQL。

上例可初步读成:事务 1 与事务 2 分别访问 inventory 主键记录,至少一个事务等待另一个事务持有的锁,形成了互相等待的条件。若结合前序 SQL 发现事务 1 先更新 id=10 再更新 id=11,而事务 2 顺序相反,就有理由进一步核对并发事务的加锁顺序。但报告里的两条 UPDATE 本身还不足以证明这就是完整原因;需要用应用日志或语句历史补齐事务前序。

将死锁报告、数据库日志和应用时间线对齐

日志关联的关键不是只对齐“某分钟”,而是尽可能找到同一事件的时间、连接和请求标识。建议整理成一条时间线:

将死锁报告、数据库日志和应用时间线对齐配图

证据来源优先记录的字段用途
应用日志时间戳、时区、请求 ID、错误码、重试次数、数据库连接标识判断业务请求何时失败、是否重试
InnoDB 死锁报告报告时间、事务 ID、thread id、query id、表与索引、等待锁、回滚事务还原等待关系
MySQL 错误日志日志时间、死锁报告正文、实例标识找到连续发生的死锁事件
语句审计或诊断采集连接 ID、语句文本、开始时间、事务边界补足报告未包含的事务前序

先确认各端时间口径。应用、数据库主机、日志平台可能使用不同时区;数据库时间函数、主机系统时间和日志采集时间也不一定采用相同设置。可查询数据库时区变量,并与主机及应用日志配置对照:

SHOW GLOBAL VARIABLES LIKE 'time_zone';
SHOW GLOBAL VARIABLES LIKE 'system_time_zone';

这些只读结果用于解释时间戳,不会自动完成时区换算。统一到同一时区后,再按“错误发生时间—死锁报告时间—应用请求 ID”检索。若应用日志有毫秒而数据库日志只有秒,时间相同仍不能单独证明是同一请求,需进一步比较连接 ID、SQL、表名或业务主键。

在 MySQL 8.0 中,Performance Schema 可提供当前锁等待和语句信息。例如,可先检查是否有正在发生的等待:

SELECT
    w.REQUESTING_ENGINE_TRANSACTION_ID AS waiting_trx_id,
    w.BLOCKING_ENGINE_TRANSACTION_ID AS blocking_trx_id,
    r.OBJECT_SCHEMA,
    r.OBJECT_NAME,
    r.INDEX_NAME
FROM performance_schema.data_lock_waits AS w
JOIN performance_schema.data_locks AS r
  ON r.ENGINE_LOCK_ID = w.REQUESTING_ENGINE_LOCK_ID;

这类表主要反映查询时点的锁状态。死锁一经检测,事务通常很快被回滚,等待关系也会消失,因此事后查询不到记录并不能否定已发生的死锁。语句历史能否提供完整信息,还取决于 Performance Schema 相关采集是否启用、历史缓冲区是否覆盖事件以及保留时长;不宜将其当作必然完整的审计日志。

MySQL 5.7 的锁等待信息主要可从 INFORMATION_SCHEMA.INNODB_LOCKS、INNODB_LOCK_WAITS 等表查看,但同样偏向当前等待状态;不同小版本的表结构与可用字段可能有差异。跨版本排查时先查询版本,并以该版本实际可用的表为准,不要直接复制针对其他版本的 SQL。

如果报告没有前序语句,可使用已经启用的审计日志、应用 SQL 日志或语句历史补线索。慢查询日志只记录符合其阈值或过滤条件的语句,不能作为完整事务轨迹;通用查询日志可能产生大量 I/O 和存储开销,且开启后也不能找回过去未记录的内容。不要为追溯单个历史死锁而未经评估就长期打开通用查询日志。

数据库死锁排查涉及事务并发、索引访问和日志留存,稳定的服务器资源可为数据库及相关业务服务提供运行基础。A5数据提供中国香港、美国、日本、韩国、中国台湾、新加坡和马来西亚等地区的物理服务器,覆盖Xeon Gold与AMD EPYC平台,并有SSD或NVMe存储配置,可用于承载数据库、业务后台和接口服务。不同地区及产品系列提供不同的处理器、内存、存储和网络方案,满足多种数据库部署与业务架构的资源需求。

按结果分支判断下一步

  • 两个事务访问相同表、索引和记录,且语句顺序相反:优先核查业务代码是否对多行数据采用不同的更新顺序。若同一业务对象需要更新多行,可评估让相关事务按稳定、统一的主键顺序处理。修改前应在测试环境验证并发行为、事务语义和性能。
  • 报告出现非预期索引、扫描范围大或不同访问路径:检查 SQL 的执行计划、索引设计和实际过滤条件。索引可能改变扫描与加锁范围,但新增索引也会增加写入维护成本;应依据真实查询计划和业务负载评估,不要仅凭死锁报告直接加索引。
  • 涉及间隙锁、插入意向锁或范围条件:核对事务隔离级别、索引结构及范围查询条件。隔离级别会影响锁行为,不能为消除死锁而直接全局降低隔离级别;要先评估幻读、并发一致性及应用约束。
  • 应用只记录了错误,没有 SQL、连接或请求关联信息:问题可能不是数据库“没有日志”,而是应用侧缺少可关联的诊断字段。优先补充请求 ID、连接标识、事务开始与结束记录和数据库错误码,并限制日志中敏感参数的采集范围。
  • 只有 1205,没有死锁报告:按锁等待超时方向检查持锁事务、事务持续时间和阻塞链,不要把它强行解释成死锁。长事务可能导致等待,但“等待很久”本身并不等于形成循环。
  • 只有一个事务片段,无法确认另一方:先不要据此修改 SQL。检查错误日志是否完整、报告是否对应同一时间窗口,并在可复现时开启短期死锁日志采集。

每个分支都应以报告、执行计划或应用时间线作为证据。若要调整 SQL、索引、隔离级别或事务边界,先记录现有配置和变更内容,在测试环境或小范围流量中验证;涉及结构变更时准备符合业务要求的备份与回退方案。数据库参数的回滚方式也要明确:动态变量恢复原值;索引或代码变更则按已验证的部署回滚流程执行。不要在生产环境凭单次报告直接执行数据修改或批量变更。

修复后复测与持续观察

验证不应止于“报错暂时消失”。先用相同业务路径和接近的并发条件复测,记录事务顺序、错误码和响应结果;若调整了锁顺序,应确认参与同类数据更新的事务都遵守一致顺序。若调整了索引或 SQL,应重新查看执行计划,并确认写入延迟、锁等待和业务结果没有明显异常。

发布后在一个覆盖业务峰值的观察窗口内,持续关注 1213、1205 的数量、死锁报告频率、事务持续时间及重试成功率。死锁在并发数据库中不一定能完全避免,可靠的事务重试也应纳入应用设计:对可重试事务按业务规则整体重试,设置次数上限与退避,并避免在事务外重复执行不可幂等的副作用。

确认问题稳定后,关闭临时开启的死锁详细日志并恢复原值;保留必要的报告、时间线和变更记录,便于同类问题复现。若死锁仍出现,比较新旧报告中的事务顺序、索引和等待记录,检查修复是否覆盖了所有参与路径,而不是仅凭一段时间未报警判定根因已消除。

本文由A5数据原创发布,转载请注明出处:https://www.a5idc.com/helpcontent/5935.html

相关推荐