香港服务器数据库发生死锁时,如何从事务日志定位冲突语句?
判断数据库死锁时,不能只根据应用返回的“事务回滚”或“请求超时”来猜原因。对 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
这表示冲突可能涉及索引记录之间的间隙,而不是单纯的“两个事务更新了同一行”。
服务层:连接池可能隐藏事务未结束
连接池本身不会生成锁环,但错误的连接复用会让事务跨越多个业务操作。例如:
- 请求从连接池借出连接;
- 开启事务并执行更新;
- 中途调用远程接口;
- 请求异常退出,没有执行回滚;
- 连接仍被归还给连接池或继续被复用。
需要核对:
- 连接借出和归还是否成对;
- 异常分支是否执行回滚;
- 归还连接前是否恢复
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)
阅读时按以下顺序建立关系:
TRANSACTION后面的事务 ID 用于区分事务;MySQL thread id用于关联线程和语句历史;WAITING FOR THIS LOCK表示该事务当前想拿、但没有拿到的锁;HOLDS THE LOCK(S)表示它已经持有的锁;- 两个事务各自的“等待”和“持有”组合起来,才是完整的环路;
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 文本更有用:
| 事务 | 已执行并持有 | 当前等待 | 需要核对 |
|---|---|---|---|
| A | orders 的某行 | inventory 的某行 | 是否总是先订单后库存 |
| B | inventory 的某行 | 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 依赖,并保留变更记录。若新增索引后写入延迟、磁盘占用或锁竞争恶化,应按变更流程回退,而不是继续叠加更多索引。
应用端应正确处理死锁重试
死锁受害事务会被数据库回滚,应用不能把原连接上的业务状态当成“已执行成功”。常见处理方式是:
- 只针对明确的死锁错误码或 SQLSTATE
40001重试; - 每次重试重新开启完整事务;
- 使用有限次数和带随机抖动的退避;
- 确保写入操作具备幂等性;
- 连接归还连接池前清理事务状态;
- 超过重试次数后记录完整上下文并告警。
重试只能降低瞬时失败对用户的影响,不能替代统一锁顺序和索引优化。如果每次请求都无限重试,反而会让锁竞争和连接池占用进一步恶化。
为复发设置监控点
死锁问题修复后,至少保留以下监控维度:
- 单位时间内的死锁次数;
Innodb_row_lock_current_waits和锁等待累计时间;- 超过正常范围的事务持续时间;
- 按 SQL 指纹统计的扫描行数和执行时间;
- 连接池 active、idle、等待获取连接的数量;
- 数据库 CPU、磁盘延迟和日志盘剩余空间;
- 错误日志中的
LATEST DETECTED DEADLOCK数量; - 死锁涉及的表名、索引名、线程 ID 和应用接口。
可以先使用相对保守的参考告警规则,例如五分钟内出现新的死锁就触发低级告警;锁等待持续超过平时 P95 的数倍时升级关注;事务持续时间超过业务正常值后,进一步关联应用链路。具体阈值应根据业务基线调整,不宜把某个固定秒数套用到所有香港服务器实例。
当新的死锁再次出现时,按“错误日志死锁段落—线程 ID—语句历史—锁结构—执行计划—应用事务边界”的顺序回放,通常可以快速判断它是旧问题复发,还是新增了另一条访问路径。这样既能定位当前冲突语句,也能把索引、事务顺序、连接池和系统资源的影响区分开,避免仅凭一次超时现象做出错误优化。

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

