香港服务器数据库发生死锁时,如何从事务日志定位冲突语句?

发布人:A5数据发布时间:2026-10-21 11:52阅读量:70

判断数据库死锁时,不能只根据应用返回的“事务回滚”或“请求超时”来猜原因。对 MySQL/InnoDB 而言,最有价值的第一份证据通常是错误日志中的 LATEST DETECTED DEADLOCK 段落:其中会列出两个事务、各自持有的锁、正在等待的锁、MySQL 线程 ID,以及触发死锁时正在执行的语句。再用线程 ID 关联 performance_schema 的语句历史和应用请求日志,才能还原真正的冲突语句。

需要注意的是,InnoDB redo log、undo log 和 binlog 并不等同于“死锁明细日志”。redo log 主要用于崩溃恢复,binlog 更适合确认已经提交的数据变更,二者通常不能直接告诉你“事务 A 等待事务 B 的哪一行”。因此,定位过程应以死锁诊断段落为主,以锁等待视图、语句历史、慢查询记录和应用链路日志补全上下文。香港服务器的部署地域不会改变死锁机制,但跨地域访问带来的网络延迟、连接复用和事务停留时间,可能放大锁冲突。

先确认是否真的是死锁

区分死锁、锁等待和应用超时

常见错误需要先分型:

现象常见标识诊断含义
死锁MySQL 错误码 1213、SQLSTATE 40001存在相互等待,InnoDB 主动选择一个事务回滚
锁等待超时错误码 1205事务一直等不到锁,但不一定形成环路
应用请求超时网关、连接池或代码自定义超时可能是锁等待,也可能是网络、CPU、磁盘或连接池问题
数据库连接中断Lost connection、连接重置等不能直接证明发生了死锁

先确认数据库版本和基础状态,以下命令为只读检查:

SELECT VERSION();

SHOW GLOBAL STATUS
WHERE Variable_name IN (
  'Innodb_deadlocks',
  'Innodb_row_lock_current_waits',
  'Innodb_row_lock_time',
  'Innodb_row_lock_waits',
  'Threads_running'
);

Innodb_deadlocks 可以反映累计死锁次数,但它不能告诉你具体是哪两条 SQL。SHOW ENGINE INNODB STATUS 通常只保留最近一次死锁信息,因此如果故障已经过去较久,现场可能已被后续死锁覆盖。

SHOW ENGINE INNODB STATUS\G

重点搜索以下内容:

LATEST DETECTED DEADLOCK
*** (1) TRANSACTION:
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
*** (2) TRANSACTION:
*** (2) HOLDS THE LOCK(S):
*** WE ROLL BACK TRANSACTION (1)

先固定故障时间范围

香港服务器上的应用、数据库和日志系统应尽量使用统一时区。数据库日志常见为服务器本地时间,而应用链路可能记录 UTC 或业务时区。如果时间没有对齐,容易把另一批 SQL 误认为冲突语句。

建议先记录:

  • 首次出现 1213 的时间;
  • 死锁数量是否突然增加;
  • 受影响的接口、租户或业务表;
  • 应用是否自动重试;
  • 数据库 CPU、磁盘延迟、连接数是否同时升高;
  • 应用与数据库是否位于同一台香港服务器、同一网络区域,还是跨地域访问。

这些信息用于缩小范围,不应把“香港服务器”直接等同于死锁原因。

建立死锁原因树

死锁根因一般要按网络、系统、数据库服务和应用逻辑逐层排除。本文把“索引与访问路径优化”作为主要优化方向,因为访问路径过宽会延长持锁时间,并扩大两个事务交叉访问时形成环路的机会。

针对数据库事务、业务后台和接口服务等场景,A5数据提供中国香港物理服务器资源,覆盖Xeon Gold、AMD EPYC等平台,并配备SSD或NVMe存储及不同内存配置,为事务日志留存、并发连接处理和多任务运行提供硬件基础。香港产品同时提供CN2与国际带宽方案,可结合业务访问范围与网络需求承载数据库相关服务。

网络层:可能延长事务,但通常不是死锁的直接成因

数据库死锁要求至少两个事务之间形成循环等待。网络丢包或延迟本身不会凭空创建数据库锁,但会造成以下间接影响:

  • 应用在事务中等待远程服务返回;
  • SQL 执行后,应用迟迟没有提交或回滚;
  • 连接池中的连接被长时间占用;
  • 跨地域 RTT 增加,事务持锁时间随之变长;
  • 网络异常后,连接清理不及时,出现“看似空闲、实际仍有事务”的连接。

因此,网络层的判断依据不是单看 ping,而是对齐应用请求耗时、数据库事务持续时间和连接状态。若应用与香港数据库之间存在跨地域链路,可在应用日志中比较:

  • 发送 SQL 到收到结果的时间;
  • 收到结果到执行 COMMIT 的时间;
  • 连接从池中借出到归还的时间。

如果“收到 SQL 结果后很久才提交”,问题更接近应用事务边界;如果数据库执行本身变慢,则继续检查索引、磁盘和锁等待。

系统层:资源紧张会放大锁持有时间

CPU 饱和、磁盘延迟升高或内存不足,通常不会直接制造锁环,但会让事务执行和提交变慢。可在香港服务器上使用只读监控命令观察短时间窗口:

vmstat 1 5
iostat -xz 1 5

如果系统没有安装这些工具,应先确认发行版和运维规范,不要为了临时排障直接修改生产环境的软件源。

需要关注的不是某一个瞬时百分比,而是故障时段是否同时出现:

  • CPU 长时间接近满载;
  • 磁盘 await、队列长度明显高于平时;
  • 内存回收或交换分区活动增加;
  • 数据库线程数和运行中请求数量持续上升。

资源正常而死锁数量增加时,应把重点转回锁顺序、索引和事务边界。

数据库服务层:确认引擎、隔离级别和锁类型

先确认涉及表是否使用 InnoDB,以及连接实际采用的事务隔离级别:

SELECT TABLE_SCHEMA, TABLE_NAME, ENGINE
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = '业务库名'
  AND TABLE_NAME IN ('表A', '表B');

SELECT @@transaction_isolation, @@autocommit;

不同隔离级别会影响间隙锁和范围锁行为。以 MySQL InnoDB 常见配置为例,REPEATABLE READ 下,范围条件和非唯一索引扫描可能涉及 next-key lock;日志中若出现以下字样,就不能只盯着某一条主键记录:

locks gap before rec
lock_mode X

这表示冲突可能涉及索引记录之间的间隙,而不是单纯的“两个事务更新了同一行”。

服务层:连接池可能隐藏事务未结束

连接池本身不会生成锁环,但错误的连接复用会让事务跨越多个业务操作。例如:

  1. 请求从连接池借出连接;
  2. 开启事务并执行更新;
  3. 中途调用远程接口;
  4. 请求异常退出,没有执行回滚;
  5. 连接仍被归还给连接池或继续被复用。

需要核对:

  • 连接借出和归还是否成对;
  • 异常分支是否执行回滚;
  • 归还连接前是否恢复 autocommit、隔离级别和会话变量;
  • 连接池最大连接数是否远高于数据库合理并发;
  • 连接池超时是否小于事务实际可能持续时间。

如果连接池日志显示大量连接处于 active,但数据库只看到少数正在执行的 SQL,可能存在应用线程在事务中等待外部操作,而不是数据库执行本身慢。

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

典型死锁关系如下:

事务 A:先锁定订单,再锁定库存
事务 B:先锁定库存,再锁定订单

如果 A 已经持有订单行并等待库存行,B 已经持有库存行并等待订单行,就形成环路。两条语句单独执行都可能很快,但并发执行时会互相等待。

建立死锁原因树 应用层:最常见的是访问顺序不一致配图

另一个常见分支是同一张表的访问路径不同:

  • 一个事务按主键更新;
  • 另一个事务按状态、租户或时间范围扫描后更新;
  • 扫描语句缺少合适索引,持锁范围和持续时间扩大;
  • 两个事务随后又访问另一张表,最终形成交叉等待。

从死锁日志提取冲突语句

读取死锁段落中的四类信息

一段典型的模拟日志可能类似下面的形式,内容仅用于说明字段含义,不代表某个真实环境的现场输出:

从死锁日志提取冲突语句 读取死锁段落中的四类信息配图

LATEST DETECTED DEADLOCK
------------------------
2025-01-15 10:32:18

*** (1) TRANSACTION:
TRANSACTION 812341, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1128, 2 row lock(s)
MySQL thread id 101, query id 556781 app_user updating
UPDATE orders
SET status = 'paid'
WHERE tenant_id = 7 AND order_id = 9001

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

*** (2) TRANSACTION:
TRANSACTION 812342, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
MySQL thread id 102, query id 556782 app_user updating
UPDATE inventory
SET reserved = reserved + 1
WHERE tenant_id = 7 AND sku_id = 3008

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 44 page no 188 n bits 96 index PRIMARY
of table `shop`.`inventory` trx table locks 2
lock_mode X locks rec but not gap

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 51 page no 214 n bits 80 index PRIMARY
of table `shop`.`orders` trx table locks 2
lock_mode X locks rec but not gap waiting

*** WE ROLL BACK TRANSACTION (1)

阅读时按以下顺序建立关系:

  1. TRANSACTION 后面的事务 ID 用于区分事务;
  2. MySQL thread id 用于关联线程和语句历史;
  3. WAITING FOR THIS LOCK 表示该事务当前想拿、但没有拿到的锁;
  4. HOLDS THE LOCK(S) 表示它已经持有的锁;
  5. 两个事务各自的“等待”和“持有”组合起来,才是完整的环路;
  6. WE ROLL BACK TRANSACTION 表示 InnoDB 选择的回滚受害者,不代表它一定是根因。

上例可以还原为:

事务 1 持有 orders,等待 inventory
事务 2 持有 inventory,等待 orders

真正需要整改的不是被回滚的某一条 SQL,而是两个事务访问资源的顺序和访问范围。

用线程 ID关联语句历史

死锁日志中的 MySQL thread id 对应 performance_schema.threads.PROCESSLIST_ID。可以先查线程映射:

SELECT THREAD_ID, PROCESSLIST_ID, PROCESSLIST_USER,
       PROCESSLIST_HOST, PROCESSLIST_DB,
       PROCESSLIST_COMMAND, PROCESSLIST_TIME
FROM performance_schema.threads
WHERE PROCESSLIST_ID IN (101, 102);

再读取对应线程最近执行过的语句:

SELECT THREAD_ID,
       EVENT_ID,
       EVENT_NAME,
       SQL_TEXT,
       CURRENT_SCHEMA,
       ROWS_EXAMINED,
       ROWS_AFFECTED
FROM performance_schema.events_statements_history_long
WHERE THREAD_ID IN (
  SELECT THREAD_ID
  FROM performance_schema.threads
  WHERE PROCESSLIST_ID IN (101, 102)
)
ORDER BY THREAD_ID, EVENT_ID;

这里的 101、102只是上面模拟日志中的线程 ID。实际排查时应替换为死锁日志中的值。

events_statements_history_long 可能因为保留容量有限而找不到目标语句。如果故障发生后间隔较长,或者语句历史消费者没有启用,就需要依赖:

  • 数据库错误日志;
  • 应用 SQL 日志;
  • 慢查询日志;
  • APM 链路中的 SQL 指纹;
  • 短时间开启的 general log。

如果需要启用语句历史,应先检查消费者状态:

SELECT NAME, ENABLED
FROM performance_schema.setup_consumers
WHERE NAME LIKE '%events_statements_history%';

调整 Performance Schema 消费者也会带来存储和性能开销,生产环境应先评估容量,并在采集结束后恢复原状态。

实时锁等待与死锁日志的区别

死锁已经被 InnoDB 处理后,实时锁等待可能已经消失;因此 data_locks 适合观察当前等待,不适合替代历史死锁日志。MySQL 8.0 可使用以下查询查看当前锁等待:

SELECT
  w.REQUESTING_ENGINE_TRANSACTION_ID AS waiting_trx_id,
  r.THREAD_ID AS waiting_thread_id,
  r.OBJECT_SCHEMA AS waiting_schema,
  r.OBJECT_NAME AS waiting_table,
  r.INDEX_NAME AS waiting_index,
  r.LOCK_MODE AS waiting_lock_mode,
  r.LOCK_TYPE AS waiting_lock_type,
  r.LOCK_DATA AS waiting_lock_data,
  w.BLOCKING_ENGINE_TRANSACTION_ID AS blocking_trx_id,
  b.THREAD_ID AS blocking_thread_id,
  b.OBJECT_SCHEMA AS blocking_schema,
  b.OBJECT_NAME AS blocking_table,
  b.INDEX_NAME AS blocking_index,
  b.LOCK_MODE AS blocking_lock_mode,
  b.LOCK_TYPE AS blocking_lock_type,
  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;

如果是 MariaDB 或较旧版本,视图名称和字段可能不同,应先执行:

SELECT VERSION();
SHOW TABLES FROM information_schema LIKE 'INNODB%';

不要直接套用 MySQL 8.0 的查询。当前事务概况还可以通过以下命令辅助判断:

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

trx_query 通常只表示当前语句,不是该事务全部历史。要还原事务之前执行过什么,仍需结合语句历史或应用日志。

逐层排除并定位根因

先看是否形成真正的环路

可以把死锁证据整理成如下关系表:

证据说明下一步
A 等待 B 持有的记录,B 又等待 A 持有的记录已形成死锁环路比较两边访问顺序
只有 A 等待 B,没有 B 等待 A更像普通锁等待查长事务、慢 SQL 和资源
等待锁位于非唯一索引或范围条件可能涉及多行或间隙锁检查索引和隔离级别
事务已持续数秒或更久持锁时间过长查远程调用、连接池和事务边界
日志只有事务 ID,没有完整 SQL现场采集不完整查线程历史、应用日志或短时 general log

用 EXPLAIN 验证访问路径

索引优化不能根据“日志里出现了某个索引”就直接添加索引。要用与现场一致的条件、数据分布和参数执行 EXPLAIN。以下是示例,只用于分析,不应把更新语句直接执行在生产库:

EXPLAIN
UPDATE orders
SET status = 'paid'
WHERE tenant_id = 7
  AND order_id = 9001;

重点观察:

  • key 是否使用预期索引;
  • key_len 是否覆盖了主要查询条件;
  • type 是否从 ALL 变成更有选择性的访问方式;
  • rows 估算值是否过大;
  • Extra 是否出现需要额外过滤的情况。

如果查询条件是:

WHERE tenant_id = ?
  AND status = ?
  AND created_at < ?

组合索引通常要根据等值条件、范围条件和实际选择性设计,而不是机械地为每个字段分别建立索引。索引是否合适,最终要通过 EXPLAIN、实际执行时间、扫描行数和死锁日志中的锁范围共同验证。

索引优化的边界也要明确:

  • 新增索引会增加写入和索引维护成本;
  • 同一张表存在多个相似索引时,优化器未必选择预期索引;
  • 更新语句可能同时维护聚簇索引和二级索引;
  • 范围更新即使使用了索引,也可能锁定多条记录;
  • 统计信息失真时,EXPLAIN 估算行数可能与实际差异较大。

因此,不能把“加一个索引”作为所有死锁的通用修复方案。

检查事务访问顺序

将两条冲突语句按事务顺序列出来,通常比单看 SQL 文本更有用:

事务已执行并持有当前等待需要核对
Aorders 的某行inventory 的某行是否总是先订单后库存
Binventory 的某行orders 的某行是否存在反向访问
A 或 B非唯一索引范围另一张表记录是否因扫描范围过大
长事务多张表、多条记录某一热点行是否把外部调用放在事务内

如果业务允许,应统一资源访问顺序。例如所有涉及订单和库存的事务都先锁定订单,再锁定库存,或者全部反过来,但不能由不同代码路径各自决定。

对于必须锁定的查询,SELECT ... FOR UPDATE 的使用也要审查。它会把读取变成锁定读取,若放在大范围查询或事务早期,可能显著增加锁持有时间。调整前要确认业务一致性要求,不能为了减少等待而随意取消锁定读取。

事务日志与 binlog如何配合

redo、undo和binlog不能替代死锁报告

三类日志的用途不同:

日志或记录适合确认不适合直接确认
InnoDB redo log崩溃恢复、持久化顺序哪两个事务互相等待
undo 相关信息回滚和旧版本读取完整 SQL 文本和业务请求
binlog已提交事务的数据变更、复制顺序未提交事务的等待关系
错误日志中的死锁段落持有锁、等待锁、线程 ID、受害事务故障前所有历史 SQL
Performance Schema 语句历史线程最近执行的 SQL超出保留窗口的历史
应用链路日志请求、参数、事务边界和重试数据库实际锁结构

如果 binlog 使用 row 格式,mysqlbinlog 解码后主要看到行变化,而不是应用最初提交的完整 UPDATE 条件。它可以帮助确认某个事务是否最终提交、修改了哪些行,但不能独立证明死锁环路。

必要时采集所有死锁

SHOW ENGINE INNODB STATUS 只展示最近一次死锁。需要持续观察一段时间时,可以在确认日志容量和权限后开启:

SET GLOBAL innodb_print_all_deadlocks = ON;

该设置只影响开启之后发生的死锁,输出通常进入 MySQL 错误日志。路径应通过变量确认,不要猜测发行版文件位置:

SHOW VARIABLES LIKE 'log_error';

采集完成后,如果不再需要持续记录,应关闭:

SET GLOBAL innodb_print_all_deadlocks = OFF;

如果要写入配置文件使其重启后仍生效,应先备份配置、评估日志增长和磁盘空间,并安排受控变更;回滚时删除或注释对应配置,再按发行版规范重启或重新加载服务。不要在没有日志轮转和容量监控的情况下长期开启高频诊断日志。

general log 可以记录更多 SQL,但会带来明显的 I/O、性能和敏感数据暴露风险。只有在 Performance Schema、错误日志和应用日志都不足以还原问题时,才考虑在短时间窗口内开启,并设置明确的关闭时间。

验证修复是否真正生效

优先在相同并发条件下复现

修复前后的验证条件应尽量一致:

  • 使用相同的事务隔离级别;
  • 使用接近生产的数据量和索引;
  • 保持相似的并发事务数;
  • 使用真实的 SQL 访问顺序;
  • 包含异常、重试和连接归还路径。

测试环境中可以让两组事务反复按不同顺序访问两张表,验证是否能复现日志中的等待关系。不要直接在生产库通过强制锁表或大范围更新来“制造死锁”,这可能扩大影响范围。

索引变更要有备份、影响评估和回滚路径

如果确认根因是缺少索引或访问路径不合理,变更前至少核对:

  • 表数据和结构已有备份;
  • 磁盘空间足以容纳新索引和临时空间;
  • DDL 对应的 MySQL/MariaDB 版本和存储引擎支持情况;
  • 在线 DDL 是否会短暂阻塞写入;
  • 业务低峰期和连接池并发是否适合执行;
  • 回滚方式是删除新索引,还是恢复表结构。

删除索引属于数据库结构变更,不能直接在生产环境执行。应先确认该索引没有被其他 SQL 依赖,并保留变更记录。若新增索引后写入延迟、磁盘占用或锁竞争恶化,应按变更流程回退,而不是继续叠加更多索引。

应用端应正确处理死锁重试

死锁受害事务会被数据库回滚,应用不能把原连接上的业务状态当成“已执行成功”。常见处理方式是:

  1. 只针对明确的死锁错误码或 SQLSTATE 40001 重试;
  2. 每次重试重新开启完整事务;
  3. 使用有限次数和带随机抖动的退避;
  4. 确保写入操作具备幂等性;
  5. 连接归还连接池前清理事务状态;
  6. 超过重试次数后记录完整上下文并告警。

重试只能降低瞬时失败对用户的影响,不能替代统一锁顺序和索引优化。如果每次请求都无限重试,反而会让锁竞争和连接池占用进一步恶化。

为复发设置监控点

死锁问题修复后,至少保留以下监控维度:

  • 单位时间内的死锁次数;
  • Innodb_row_lock_current_waits 和锁等待累计时间;
  • 超过正常范围的事务持续时间;
  • 按 SQL 指纹统计的扫描行数和执行时间;
  • 连接池 active、idle、等待获取连接的数量;
  • 数据库 CPU、磁盘延迟和日志盘剩余空间;
  • 错误日志中的 LATEST DETECTED DEADLOCK 数量;
  • 死锁涉及的表名、索引名、线程 ID 和应用接口。

可以先使用相对保守的参考告警规则,例如五分钟内出现新的死锁就触发低级告警;锁等待持续超过平时 P95 的数倍时升级关注;事务持续时间超过业务正常值后,进一步关联应用链路。具体阈值应根据业务基线调整,不宜把某个固定秒数套用到所有香港服务器实例。

当新的死锁再次出现时,按“错误日志死锁段落—线程 ID—语句历史—锁结构—执行计划—应用事务边界”的顺序回放,通常可以快速判断它是旧问题复发,还是新增了另一条访问路径。这样既能定位当前冲突语句,也能把索引、事务顺序、连接池和系统资源的影响区分开,避免仅凭一次超时现象做出错误优化。

为复发设置监控点配图

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

相关推荐