美国服务器上的数据库频繁死锁,如何从事务日志定位冲突语句?

发布人:A5数据发布时间:2026-11-10 09:24阅读量:43

​美国服务器上的数据库出现“请求偶发卡住、接口超时、随后部分请求自动恢复”时,不能仅凭服务器位于美国或数据库负载升高就判断为网络故障。真正的死锁通常表现为两个或多个事务互相持有对方需要的锁,数据库检测到环路后主动回滚其中一个事务。定位关键不在于找到一条“报错 SQL”,而在于还原完整的锁关系:哪个事务持有什么资源、等待什么资源、对应哪条 SQL,以及应用为何以该顺序执行。

还需要先区分数据库引擎的日志类型。MySQL 的 redo/undo 日志主要用于崩溃恢复,通常不会直接给出可读的死锁图;InnoDB 死锁信息一般来自错误日志、SHOW ENGINE INNODB STATUS 和 Performance Schema。SQL Server 的事务日志同样不是定位死锁图的首选来源,通常应从 Extended Events 的 xml_deadlock_report 中读取。以下按“确认症状—建立原因树—逐层排除—定位根因—验证恢复”的顺序进行复盘。

确认症状:先证明是死锁,而不是普通锁等待

从错误码和时间线入手

先确定故障时间窗口,并统一使用 UTC 记录。美国服务器可能使用 UTC、EST 或 EDT,应用服务器、数据库实例和日志采集平台的时区不一致时,几秒钟的偏差就可能让两条相关日志看起来不在同一时间。

MySQL 常见的区分方式如下:

现象或错误码通常含义初步判断
ER_LOCK_DEADLOCK、错误码 1213InnoDB 检测到锁等待环路并回滚事务优先查死锁图
Lock wait timeout exceeded、错误码 1205等待锁超过超时时间可能是长事务或阻塞,不一定是死锁
SQL 执行时间突然升高,但没有锁错误可能是 CPU、磁盘、网络或执行计划问题先查系统和服务指标
应用收到错误后立即成功可能是事务重试掩盖了死锁关联重试日志和数据库错误日志

SQL Server 中,错误号 1205 通常表示事务成为死锁牺牲者;锁请求超时则常见为 1222。不同数据库的错误码不能混用,排查前应执行版本和引擎确认。

date -u

如果应用日志记录了请求编号,建议将下面几类事件按 UTC 排成时间线:

  1. 请求进入应用的时间;
  2. 事务开始和第一条写操作的时间;
  3. 数据库报告死锁或锁等待的时间;
  4. 数据库回滚牺牲事务的时间;
  5. 应用重试、失败或返回成功的时间。

下面是一段模拟时间线,只用于说明判断方式,并不代表某个真实环境:

2025-03-08T14:10:00.412Z  request_id=req-781  BEGIN
2025-03-08T14:10:00.438Z  request_id=req-781  UPDATE inventory ... sku='A'
2025-03-08T14:10:00.451Z  request_id=req-932  UPDATE inventory ... sku='B'
2025-03-08T14:10:00.467Z  mysql               deadlock detected, rollback trx-932
2025-03-08T14:10:00.489Z  request_id=req-781  COMMIT
2025-03-08T14:10:00.531Z  request_id=req-932  retry success

这条时间线只能证明“数据库检测到了死锁并回滚了一个事务”,还不能说明哪两条 SQL 形成了环路。下一步必须获取锁资源和事务关系。

观察影响范围,而不是只看单个报错

至少同步查看以下指标:

  • 死锁次数以及每分钟事务数;
  • 锁等待数量、等待时长和最长事务年龄;
  • 数据库 CPU、内存、磁盘延迟;
  • 应用连接池使用率、请求超时和自动重试次数;
  • 受影响的接口、表和业务操作类型。

如果只有一个后台批处理任务触发死锁,影响范围通常较窄;如果订单、库存、支付等多个接口同时出现超时,则需要考虑连接池拥塞、长事务、数据库 I/O 变慢或重试风暴。

建立原因树:不要把所有异常都归因于数据库

网络层:网络不会直接制造锁环,但会延长持锁时间

美国服务器与应用服务器、缓存服务或其他区域数据库之间存在较高延迟时,事务可能在执行多条 SQL 的间隙等待网络响应。只要事务尚未提交,已经获取的锁仍可能被保留。

网络分支应重点观察:

  • 应用与数据库是否跨区域部署;
  • 数据库连接的平均 RTT 和 p95、p99 延迟;
  • 事务是否包含远程 API 调用、消息发送或文件操作;
  • 网络抖动是否触发客户端超时和重复提交;
  • 连接池是否在超时后创建大量新连接。

如果网络延迟升高但数据库没有形成互相等待的锁图,问题更可能是事务持续时间变长或应用重试增加,而不是死锁本身。不要用一次 ping 结果替代数据库连接和真实 SQL 的延迟观测。

系统层:资源争用可能放大死锁,但不是锁环证据

在 Linux 服务器上,可以使用只读方式采集短时间系统指标。下面命令要求系统已安装相应工具,采集间隔和次数仅为示例:

vmstat 1 5
iostat -xz 1 5

重点看以下情况:

  • CPU 长时间接近饱和,导致事务执行和死锁检测变慢;
  • 磁盘 await 或 %util 持续偏高,事务持锁时间增加;
  • 内存不足导致数据库或文件缓存抖动;
  • 系统时间未同步,日志顺序无法可靠对齐;
  • 数据库所在磁盘与备份、日志归档任务争用 I/O。

系统指标只能解释“为什么锁持有时间变长”,不能单独证明“哪两条 SQL 互相等待”。必须回到数据库的锁图和事务信息。

数据库服务层:锁模式、隔离级别和执行计划

数据库服务层常见原因包括:

  • 两个事务访问相同资源,但访问顺序相反;
  • 缺少合适索引,更新语句扫描了更多记录;
  • 范围查询在特定隔离级别下产生间隙锁或范围锁;
  • 长事务在应用等待期间仍未提交;
  • DDL、批量更新与在线业务同时操作同一张表;
  • 连接池复用连接时,上一事务没有正确提交或回滚。

在 MySQL InnoDB 中,REPEATABLE READ、唯一索引、非唯一索引和范围条件会影响锁的范围。将隔离级别直接改为较低级别并不能保证消除死锁,还可能改变业务读一致性,因此应先确认执行计划和锁资源,再在测试环境评估。

应用层:最常见的是访问顺序不一致

典型环路如下:

  • 事务 A 先锁定商品 A,再锁定商品 B;
  • 事务 B 先锁定商品 B,再锁定商品 A;
  • A 等待 B 释放商品 B;
  • B 等待 A 释放商品 A。

其他常见模式包括批量任务按数据库返回顺序更新,在线请求按用户输入顺序更新,或者一个事务先写订单再写库存,另一个事务先写库存再写订单。应用自动重试如果没有幂等设计,还可能在原事务尚未完全释放资源时制造更多竞争。

逐层排除:从低风险证据开始取数

MySQL:获取 InnoDB 死锁图

先确认版本、时区和错误日志位置:

SELECT
    VERSION() AS db_version,
    @@global.time_zone AS global_time_zone,
    @@session.time_zone AS session_time_zone,
    @@system_time_zone AS system_time_zone;

SHOW VARIABLES LIKE 'log_error';
SHOW VARIABLES LIKE 'innodb_print_all_deadlocks';

SHOW ENGINE INNODB STATUS\G 通常能看到最近一次死锁信息:

SHOW ENGINE INNODB STATUS\G

重点查找 LATEST DETECTED DEADLOCK,并记录以下内容:

  • TRANSACTION 后面的事务 ID;
  • MySQL thread id 和 query id;
  • 事务已经持有的锁;
  • WAITING FOR THIS LOCK TO BE GRANTED 后面的等待资源;
  • 事务当前或最近执行的 SQL;
  • WE ROLL BACK TRANSACTION 指出的牺牲事务。

该命令通常只保留最近一次死锁。如果需要在短时间内捕获连续事件,可临时打开 InnoDB 死锁记录:

SET GLOBAL innodb_print_all_deadlocks = ON;

这项设置会把每次死锁写入错误日志,生产环境启用前应确认日志磁盘空间、采集规则和权限,并评估高并发场景下的日志量。完成取证后可以关闭:

SET GLOBAL innodb_print_all_deadlocks = OFF;

这不是删除数据的操作,但仍应由具备数据库管理权限的人员执行,并记录变更时间。错误日志的实际路径以 log_error 返回值为准,不要直接猜测 mysqld.log 或 error.log 的位置。

MySQL:补充当前锁等待和事务信息

MySQL 8.0 可通过 Performance Schema 查看当前锁等待。下面查询是只读示例,字段名称应以实际版本为准:

SELECT
    r.ENGINE_TRANSACTION_ID AS waiting_trx,
    r.OBJECT_SCHEMA AS waiting_schema,
    r.OBJECT_NAME AS waiting_table,
    r.INDEX_NAME AS waiting_index,
    r.LOCK_TYPE AS waiting_lock_type,
    r.LOCK_MODE AS waiting_lock_mode,
    r.LOCK_DATA AS waiting_lock_data,
    b.ENGINE_TRANSACTION_ID AS blocking_trx,
    b.OBJECT_SCHEMA AS blocking_schema,
    b.OBJECT_NAME AS blocking_table,
    b.INDEX_NAME AS blocking_index,
    b.LOCK_TYPE AS blocking_lock_type,
    b.LOCK_MODE AS blocking_lock_mode,
    b.LOCK_DATA AS blocking_lock_data
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
JOIN performance_schema.data_locks AS b
  ON b.ENGINE_LOCK_ID = w.BLOCKING_ENGINE_LOCK_ID;

再关联 InnoDB 事务视图,观察事务开始时间、状态和线程 ID:

SELECT
    trx_id,
    trx_started,
    trx_state,
    trx_wait_started,
    trx_mysql_thread_id,
    trx_rows_locked,
    trx_rows_modified,
    trx_query
FROM information_schema.innodb_trx
ORDER BY trx_started;

如果 trx_query 为空,不代表没有执行 SQL,可能是事务正在应用层等待,或者当前语句已经结束但事务仍未提交。此时可通过 performance_schema.threads 与语句历史表关联线程:

SELECT
    t.PROCESSLIST_ID,
    h.EVENT_ID,
    h.SQL_TEXT,
    h.ROWS_EXAMINED,
    h.ROWS_AFFECTED
FROM performance_schema.threads AS t
JOIN performance_schema.events_statements_history_long AS h
  ON h.THREAD_ID = t.THREAD_ID
WHERE t.PROCESSLIST_ID IN (1001, 1002)
  AND h.SQL_TEXT IS NOT NULL
ORDER BY t.PROCESSLIST_ID, h.EVENT_ID;

其中 1001、1002 是示例线程 ID,应替换为 innodb_trx.trx_mysql_thread_id 返回的实际值。若历史表没有记录,需要检查 Performance Schema 消费者是否启用,以及历史数据是否已被新语句覆盖。为扩大历史保留范围可能增加内存和采集开销,应在低峰期评估,不能把查询不到历史 SQL 当作“没有执行 SQL”。

SQL Server:从死锁 XML 而不是事务日志读取

SQL Server 常用的死锁证据是 Extended Events 中的 xml_deadlock_report。许多版本的 system_health 会话默认采集该事件,但应先确认目标配置:

SELECT
    s.name AS session_name,
    t.target_name,
    CAST(t.target_data AS xml) AS target_data
FROM sys.server_event_sessions AS s
JOIN sys.server_event_session_targets AS t
  ON s.event_session_id = t.event_session_id
WHERE s.name = N'system_health';

在 SSMS 中可以打开“管理—扩展事件—会话—system_health”,筛选 xml_deadlock_report。死锁 XML 中通常要看三个部分:

  • victim-list:数据库选择回滚的事务;
  • process-list:会话、事务、执行栈、inputbuf 和客户端信息;
  • resource-list:键锁、页锁、对象锁以及持有和等待关系。

如果已确认 XE 文件路径,也可以用只读 T-SQL 读取。下面路径是版本化示例,不代表所有服务器都相同:

SELECT
    CAST(event_data AS xml) AS deadlock_xml
FROM sys.fn_xe_file_target_read_file(
    N'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Log\system_health*.xel',
    NULL,
    NULL,
    NULL
)
WHERE object_name = N'xml_deadlock_report';

不要把 fn_dblog 当作正式死锁诊断工具。它属于未公开接口,事务日志中的记录也不等同于带有完整锁关系的死锁图。若 XE 没有保留故障时间段,应检查文件滚动和保留策略,再在可接受的日志量范围内建立专门的死锁事件会话。

定位根因:把死锁图还原成可执行的冲突关系

先画出“持有—等待”关系

拿到日志后,不要只复制牺牲事务的 SQL。应把每个事务整理成四列:

定位根因:把死锁图还原成可执行的冲突关系配图

事务已持有资源正在等待资源对应语句
T1inventory 的商品 A 行锁inventory 的商品 B 行锁UPDATE ... sku='B'
T2inventory 的商品 B 行锁inventory 的商品 A 行锁UPDATE ... sku='A'

只要等待关系可以闭环,死锁就成立。牺牲事务只是数据库选择的回滚对象,不一定是根因所在的事务;另一个事务也必须纳入修复分析。

如果日志只显示参数化 SQL,例如:

UPDATE inventory SET reserved = reserved + ? WHERE sku = ?

还需要通过请求 ID、数据库线程 ID、应用 trace、审计日志或短时间的语句历史补齐参数值。生产环境不建议为了追一条 SQL 就长时间打开全量 general log,因为它可能产生较大 I/O、日志存储和敏感数据暴露风险。确需临时启用时,应先评估容量、脱敏、权限和关闭方式,并保留回滚安排。

判断是顺序问题、索引问题还是事务边界问题

可以按下面顺序核对:

  1. 访问顺序是否一致

所有更新多个资源的代码路径,是否按相同的主键、业务编号或排序规则访问。最直接的修复通常是统一加锁顺序,例如所有流程都先处理较小的商品编号,再处理较大的商品编号。

  1. 谓词是否使用合适索引

使用 EXPLAIN 检查更新对应的查询条件。若一个 UPDATE 因缺少索引扫描大量记录,锁持有范围和时间都可能扩大。索引调整必须先在测试环境验证执行计划、写入成本和回滚方案,不能在高峰期直接创建大索引。

  1. 事务是否过长

事务中不应包含远程 API、用户交互、长时间文件处理或不必要的查询。将非数据库操作移出事务,或把事务拆成明确的短阶段,但要重新设计一致性和补偿逻辑。

  1. 是否存在批处理与在线请求竞争

批量更新应控制每批行数,并按稳定顺序处理。批处理与在线请求使用不同顺序时,死锁可能只在高峰期出现。

  1. 隔离级别和范围锁是否符合业务需要

修改隔离级别可能降低部分范围锁竞争,但会改变一致性语义,不能作为未经验证的应急方案。先确认是记录锁、间隙锁、键锁还是表级资源,再决定是否调整。

网络和系统证据如何参与判断

如果死锁图显示两个事务确实互相等待,网络延迟只能解释事务为什么持续更久,不能替代应用层修复。此时将应用和数据库放在同一地区、降低 RTT 可能有帮助,但仍需修复锁顺序或事务边界。

如果没有死锁图,只有接口超时,则应继续检查:

  • 数据库连接建立时间和 SQL 执行时间是否同时升高;
  • 磁盘延迟是否与事务年龄同步增加;
  • 应用是否在超时后重复提交;
  • 数据库连接池是否耗尽;
  • 是否有备份、归档或批处理占用 I/O。

这种情况不能直接写成“美国服务器网络不稳定导致数据库死锁”,因为证据链尚未闭合。

修复与验证:验证的是冲突是否消失,而不是一次请求成功

修复时保持可回滚

代码和 SQL 结构调整应先在测试环境用接近生产的并发场景验证。涉及索引、隔离级别、表结构或批量任务时,至少准备:

  • 变更前的执行计划、表结构和关键配置备份;
  • 变更窗口、影响表和预计锁持有时间;
  • 失败时的回滚脚本或旧版本发布包;
  • 变更后观察指标和停止条件。

应用侧通常采用以下组合:

  • 统一多资源访问顺序;
  • 缩短事务范围,避免事务中调用外部服务;
  • 为死锁错误增加有限次数的重试,使用指数退避和随机抖动;
  • 确保重试操作幂等,避免重复扣库存、重复扣款或重复写入;
  • 批处理按稳定顺序、分批提交;
  • 为高竞争资源设计更明确的队列或串行化策略。

重试不应设置为无限循环。若每次失败后立即重试,可能形成“死锁—重试—更高并发—再次死锁”的风暴。重试次数、退避时间和最终失败处理应由业务一致性要求决定。

观察多个高峰窗口

修复后至少同时验证数据库、应用和系统三类结果:

验证对象关注指标判断方式
数据库死锁次数、锁等待 p95、最长事务年龄与修复前同一时间窗口比较
应用1213/1205/1205 SQL Server 错误、重试次数、接口 p95确认错误下降且没有重试风暴
系统CPU、磁盘延迟、连接数、网络 RTT确认没有用资源瓶颈掩盖锁问题
数据正确性订单、库存、支付状态及重复记录确认重试没有产生业务副作用

“连续几分钟没有报错”不能证明问题已经解决。应覆盖至少一个业务高峰和一个批处理窗口;如果死锁本身低频,则按事务量统计更有意义。例如,可将“每万次事务的死锁数”作为参考指标,但具体告警阈值应依据历史基线和业务容忍度设置,不能套用单一数值。

复发监控点:把一次故障变成可追踪事件

建议为每次死锁保留统一事件字段:UTC 时间、数据库实例、事务 ID、线程或会话 ID、对象名、索引名、锁模式、等待资源、SQL 模板、应用请求 ID、重试次数和最终结果。参数中可能包含个人信息或业务敏感数据,应使用脱敏后的值或参数摘要。

日常监控可以按四层设置:

  • 网络层:应用到数据库的连接 RTT、连接失败率、跨区域流量和超时比例;
  • 系统层:磁盘延迟、CPU 饱和度、内存压力、时间同步状态;
  • 数据库层:死锁率、锁等待时长、事务年龄、回滚事务数、活跃连接数;
  • 应用层:事务耗时、请求超时、重试次数、幂等冲突和批处理运行时间。

当再次出现错误时,优先保存故障窗口内的死锁图和应用请求链路,不要先重启数据库或批量终止连接。重启可能暂时清空现场,批量终止阻塞会触发事务回滚并扩大业务影响。只有在确认影响范围、保存必要证据并获得变更授权后,才考虑取消特定会话或暂停批处理。

最终的定位标准应当是:日志能够明确指出至少两个事务、它们各自持有和等待的资源,以及对应的冲突语句;修复后锁访问顺序或事务边界发生了可验证的变化;在高峰和批处理窗口中,死锁率、锁等待和应用重试均回到可接受基线。这样才能把“美国服务器上的数据库偶发超时”还原成一条完整、可复盘、可预防的故障链。

面向美国业务场景,A5idc数据提供常规 Xeon、AMD EPYC 物理服务器及 CN2 GIA 线路方案,也有搭配大内存与 NVMe 存储的配置,可承载数据库、业务后台和接口服务等工作负载。不同硬件与网络资源组合,为数据库事务处理、日志留存及应用服务部署提供相应的基础设施选择。

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

相关推荐