数据库服务器MySQL长事务如何定位阻塞源和提交风险?
线上接口出现超时、数据库连接池等待升高,SHOW PROCESSLIST 中却看不到一条持续运行很久的 SQL,这类问题经常不是“慢查询”本身,而是某个连接开启事务后长时间没有提交或回滚。定位时应沿着“连接 ID—事务 ID—锁对象—等待连接”建立关系,先确认谁在等待、谁持有锁,再判断是否需要终止会话。
MySQL 长事务的提交风险,主要取决于事务是否修改过数据、锁住了多少行、是否涉及大批量写入,以及当前是否伴随复制延迟、磁盘写入压力和回滚积压。只执行 KILL QUERY 不一定能释放事务锁;直接执行 KILL CONNECTION 会触发未提交事务回滚,回滚时间可能接近甚至超过原执行时间。因此,处理顺序应从低风险观察开始,逐步进入会影响业务的操作。
先确认长事务是否真的造成阻塞
区分三种容易混淆的现象
排查前先把现象分开:
- 长 SQL:某条语句本身执行时间长,连接通常处于
Query或执行状态。 - 长事务:事务已经开启很久,当前可能正在执行 SQL,也可能处于
Sleep,但事务仍未提交。 - 锁等待:某个连接正在等待其他事务释放行锁、表锁或元数据锁。
长事务不一定阻塞其他请求。例如,一个只读事务可能没有持有排他行锁,但在默认的 REPEATABLE READ 隔离级别下,仍可能保持较旧的一致性读视图,导致 InnoDB Purge 清理受阻。相反,一个只修改少量数据的事务,也可能因为占用关键行锁而让大量请求排队。
SHOW PROCESSLIST 中的 Time 表示当前线程处于某个状态的时间,不等于事务持续时间。事务年龄应以 information_schema.innodb_trx.trx_started 为准。
建立初步基线
以下查询适用于 MySQL 5.7 和 8.0,建议使用只读监控账号执行。查看其他账号的完整连接信息通常需要 PROCESS 权限,权限不足时应让数据库管理员代查,不要为了临时排障直接授予过大的管理权限。
SELECT
ID,
USER,
HOST,
DB,
COMMAND,
TIME,
STATE,
INFO
FROM information_schema.processlist
ORDER BY TIME DESC;
重点观察以下组合:
COMMAND='Sleep',但后续能在innodb_trx中找到同一个连接 ID;STATE出现Waiting for ... lock、Waiting for table metadata lock;- 大量连接处于
Sleep,同时连接池活跃连接数接近上限; Threads_running不高,但业务请求仍然超时,这通常更像锁等待或连接池等待,而不是 CPU 饱和。
再查看数据库层面的粗粒度指标:
SHOW GLOBAL STATUS
WHERE Variable_name IN (
'Threads_connected',
'Threads_running',
'Innodb_row_lock_current_waits',
'Innodb_row_lock_time',
'Innodb_row_lock_time_max',
'Innodb_buffer_pool_wait_free'
);
这些指标只用于确定方向,不能单独证明某个事务是阻塞源。例如,Threads_running 较低并不代表没有锁等待;大量线程可能都处于等待状态。
操作系统侧可用低风险命令观察 CPU、内存和磁盘等待:
iostat -xz 1 5
如果 %util 长时间接近 100%、await 明显升高,提交或回滚可能同时受到磁盘压力影响。若 CPU、磁盘均正常,但应用端延迟突然升高,应优先检查事务和锁链,而不是立即扩容数据库服务器。
对于承载 MySQL 的业务后台,A5idc数据提供香港、美国、日本等地区的物理服务器租用,配置涵盖 Xeon Gold、AMD EPYC、大内存与 SSD、NVMe 存储,并有不同线路方案,可为数据库、接口服务及多任务业务提供相应的计算、内存、存储和网络资源基础。
从连接池和应用边界检查事务来源
MySQL 只能看到连接和事务,不能直接判断“哪个业务代码忘记提交”。长事务常见于以下连接池问题:
- 从连接池借出连接后开启事务,业务调用外部接口或执行耗时任务;
- 异常分支没有执行
ROLLBACK,连接仍被归还到连接池; - 连接归还前没有重置事务状态,下一次请求复用到旧事务;
- 手动关闭了自动提交,但业务逻辑没有在所有分支执行
COMMIT或ROLLBACK; - 批处理一次处理过多记录,事务范围覆盖了整个文件或整批任务;
- 连接池等待超时被误认为数据库 SQL 慢,实际上请求还没有拿到连接。
应用日志应至少记录以下信息:
- 数据库连接 ID,即
SELECT CONNECTION_ID()的结果; - 事务开始时间、提交时间、回滚时间;
- 业务请求 ID、批次 ID;
- 事务内第一条 SQL 和最后一条 SQL;
- 连接归还连接池时是否仍处于事务状态。
在业务连接内部,可用以下语句核对会话设置:
SELECT
CONNECTION_ID() AS connection_id,
@@session.autocommit AS autocommit,
@@session.transaction_isolation AS transaction_isolation,
@@session.tx_read_only AS tx_read_only;
这条语句只能查看当前会话,不能代表整个连接池。若应用使用连接池,应在连接借出、归还和异常回收环节分别记录状态。
连接池指标需要和 MySQL 指标对照:
| 观察项 | 典型异常表现 | 可能含义 |
|---|---|---|
| 活跃连接数 | 长时间接近连接池上限 | 事务未归还、SQL 阻塞或连接泄漏 |
| 空闲连接数 | 仍有空闲连接但请求等待 | 可能是池内并发限制、路由或租借超时 |
| 等待连接数 | 与数据库锁等待同时升高 | 数据库阻塞正在向应用层扩散 |
| 单连接持有时间 | 明显高于单次 SQL 时间 | 事务范围过大或业务调用夹在事务中 |
| 归还时未提交事务数 | 出现非零值 | 应用事务边界或异常回滚存在缺陷 |
这一层优先处理应用事务边界,因为它不需要修改数据库数据,也比直接终止数据库会话更安全。
查询事务年龄和连接对应关系
使用 innodb_trx 找到真正的长事务
MySQL 5.7 和 8.0 都可以使用 information_schema.innodb_trx 查看 InnoDB 事务:
SELECT
t.trx_id,
t.trx_state,
t.trx_started,
TIMESTAMPDIFF(SECOND, t.trx_started, NOW()) AS trx_age_seconds,
t.trx_wait_started,
t.trx_mysql_thread_id AS connection_id,
t.trx_rows_locked,
t.trx_rows_modified,
t.trx_tables_locked,
t.trx_isolation_level,
t.trx_autocommit_non_locking,
t.trx_query,
p.USER,
p.HOST,
p.DB,
p.COMMAND,
p.TIME AS processlist_time,
p.STATE,
p.INFO
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;
各字段的判断重点如下:
trx_age_seconds:事务实际存活时间,不要用processlist_time替代;trx_state='RUNNING':事务正在执行或刚执行过操作;trx_state='LOCK WAIT':事务自身正在等待锁;trx_rows_locked:当前事务持有或涉及的锁行数量,数值较大时终止影响更广;trx_rows_modified:事务修改过的行数参考值,越大,提交或回滚的处理量通常越大;trx_query为空:不代表事务结束,连接可能只是暂时没有执行语句;COMMAND='Sleep'且出现在innodb_trx:重点怀疑“空闲但未提交”的事务。
例如,下面是一个模拟判断场景,并非某个真实环境的执行结果:
trx_id trx_state trx_age_seconds connection_id trx_rows_locked trx_rows_modified COMMAND
912345678 RUNNING 1850 421 3 0 Sleep
912345679 LOCK WAIT 42 438 0 0 Query
此时 421 号连接虽然处于 Sleep,但事务已持续约 1850 秒;438 号连接正在等待锁。还不能仅凭这段结果断定 421 是阻塞源,还需要查询锁等待关系,确认两个事务是否关联到同一锁对象。

处理没有事务记录的情况
如果应用请求超时,但 innodb_trx 没有明显长事务,可能存在以下分支:
- 请求还在连接池中等待,没有到达 MySQL;
- 等待的是元数据锁,而事务信息尚未按预期显示;
- SQL 是非 InnoDB 表或涉及其他存储引擎;
- 连接已经断开,应用正在重试;
- 主要瓶颈是 CPU、磁盘、临时表或网络,而不是事务锁。
这时应继续对照应用连接池等待时间、数据库错误日志、慢查询日志和资源监控,避免把所有超时都归因于长事务。
定位行锁链和元数据锁
MySQL 8.0 使用 Performance Schema 查询行锁等待
MySQL 8.0 主要通过 performance_schema.data_lock_waits 和 performance_schema.data_locks 查询 InnoDB 锁等待。可以使用以下只读查询:
SELECT
rw.THREAD_ID AS waiting_thread_id,
wt.PROCESSLIST_ID AS waiting_connection_id,
wt.PROCESSLIST_USER AS waiting_user,
wt.PROCESSLIST_HOST AS waiting_host,
rw.OBJECT_SCHEMA AS waiting_schema,
rw.OBJECT_NAME AS waiting_table,
rw.INDEX_NAME AS waiting_index,
rw.LOCK_TYPE AS waiting_lock_type,
rw.LOCK_MODE AS waiting_lock_mode,
rw.LOCK_DATA AS waiting_lock_data,
bw.THREAD_ID AS blocking_thread_id,
bt.PROCESSLIST_ID AS blocking_connection_id,
bt.PROCESSLIST_USER AS blocking_user,
bt.PROCESSLIST_HOST AS blocking_host,
bw.LOCK_MODE AS blocking_lock_mode,
bw.LOCK_DATA AS blocking_lock_data
FROM performance_schema.data_lock_waits AS w
JOIN performance_schema.data_locks AS rw
ON rw.ENGINE_LOCK_ID = w.REQUESTING_ENGINE_LOCK_ID
JOIN performance_schema.data_locks AS bw
ON bw.ENGINE_LOCK_ID = w.BLOCKING_ENGINE_LOCK_ID
LEFT JOIN performance_schema.threads AS wt
ON wt.THREAD_ID = rw.THREAD_ID
LEFT JOIN performance_schema.threads AS bt
ON bt.THREAD_ID = bw.THREAD_ID;
结果中需要重点确认:
waiting_connection_id是当前受影响的请求;blocking_connection_id是持有冲突锁的会话;- 两者是否指向同一个表、索引和记录;
- 阻塞连接是否在连接池中处于
Sleep; - 阻塞连接的事务是否修改过大量数据。
如果 performance_schema 未开启或相关表没有数据,应先核对版本和配置,不要直接猜测表名:
SELECT VERSION();
SHOW VARIABLES LIKE 'performance_schema';
SHOW TABLES FROM performance_schema
LIKE 'data_lock%';
MySQL 5.7 环境常见的是 information_schema.INNODB_LOCKS 和 information_schema.INNODB_LOCK_WAITS,但这些视图与 MySQL 8.0 的 Performance Schema 结构不同,不能直接混用查询。
元数据锁要单独排查
执行 ALTER TABLE、CREATE INDEX、RENAME TABLE 等操作时,可能等待某个长事务释放元数据锁。此时即使没有明显的行锁等待,普通查询也可能被 DDL 拖住。
MySQL 8.0 可以查看元数据锁:
SELECT
ml.OBJECT_SCHEMA,
ml.OBJECT_NAME,
ml.LOCK_TYPE,
ml.LOCK_DURATION,
ml.LOCK_STATUS,
t.PROCESSLIST_ID AS connection_id,
t.PROCESSLIST_USER AS user_name,
t.PROCESSLIST_HOST AS host_name,
t.PROCESSLIST_TIME AS processlist_time,
t.PROCESSLIST_STATE AS processlist_state,
t.PROCESSLIST_INFO AS processlist_info
FROM performance_schema.metadata_locks AS ml
LEFT JOIN performance_schema.threads AS t
ON t.THREAD_ID = ml.OWNER_THREAD_ID
WHERE ml.LOCK_STATUS IN ('GRANTED', 'PENDING')
ORDER BY ml.OBJECT_SCHEMA, ml.OBJECT_NAME, ml.LOCK_STATUS;
同一表上同时出现 GRANTED 和 PENDING 时,应结合连接 ID、事务开始时间和 SQL 内容判断谁在持有锁、谁在等待锁。若安装了 sys schema,也可以先查看汇总结果:
SELECT
object_schema,
object_name,
waiting_pid,
waiting_query,
blocking_pid,
blocking_query
FROM sys.schema_table_lock_waits;
DDL 等待元数据锁时,不要为了“让建表或加索引继续”而盲目终止所有长事务。应先确认阻塞事务对应的业务、是否仍需提交,以及终止后会不会造成大范围回滚。
按结果分支判断根因
分支一:活动写事务持有行锁
特征通常是:
trx_rows_modified大于零;trx_rows_locked较高;- 锁等待查询显示该连接为
blocking_connection_id; - 当前 SQL 可能正在执行批量更新,也可能已经处于空闲状态。
如果业务操作仍可安全完成,优先让业务负责人确认是否允许自然提交。不要由管理员在另一条连接中替它执行 COMMIT,因为管理员无法确认事务中的业务步骤是否完整。
如果事务已经失控、业务明确允许放弃,则进入会话终止流程。终止前记录连接 ID、事务 ID、应用实例、SQL 和锁对象,避免连接 ID 被复用后误杀其他请求。
分支二:空闲连接持有未提交事务
特征是:
processlist.COMMAND='Sleep';- 同一连接在
innodb_trx中仍存在; trx_query为空或不再变化;- 其他业务请求等待该事务持有的锁。
这类问题通常应修复应用的事务清理逻辑。临时处置时,只有在业务确认该事务可以放弃的情况下,才考虑终止连接。连接池归还连接前执行回滚和状态重置,应作为长期修复的一部分。
分支三:长只读事务拖慢 Purge
特征是:
trx_rows_modified接近零;- 没有明显行锁等待;
SHOW ENGINE INNODB STATUS\G中的 History list length 持续增长;- 磁盘空间、Undo 表空间或后台清理压力逐步上升。
这类事务不一定直接阻塞某一条更新语句,但会让旧版本记录保留更久。应查明长查询、报表任务或游标是否一直未关闭,优先缩短查询范围和事务生命周期,而不是单纯增加连接数。
分支四:慢 SQL 让事务迟迟不能结束
如果长事务内部包含全表扫描、缺少索引的更新或大范围排序,慢查询可能是事务迟迟不提交的原因。此时先在测试环境使用 EXPLAIN 验证执行计划,再考虑索引和 SQL 改写。
不要在生产高峰期直接创建大索引或修改 SQL。DDL 可能引入元数据锁和大量磁盘写入;SQL 改写也应先核对结果集、唯一性约束和事务隔离语义。优化目标不是只降低单条 SQL 的耗时,而是缩短“事务开始到提交”的总时长。
分支五:没有锁链,但资源指标异常
若行锁、元数据锁均没有形成明确链条,应转向以下方向:
- 磁盘写入延迟导致提交和回滚排队;
- Buffer Pool 命中率下降,读盘增加;
- 临时表溢出到磁盘;
- 复制线程或半同步确认拖慢提交;
- 应用网络调用、连接池或线程池等待;
- 大量重试造成连接数和事务数放大。
这时终止一个看似“最长”的事务未必能解决问题,应先用时间线对照数据库、操作系统、应用和复制监控。
评估提交与终止风险
事务提交风险的判断维度
| 观察维度 | 风险较低的表现 | 风险升高的表现 |
|---|---|---|
| 修改行数 | 主要是只读,trx_rows_modified 接近零 | 批量插入、更新或删除,修改行数较大 |
| 锁数量 | 锁行少、影响表少 | 锁行多,涉及热点表或多个业务表 |
| 事务状态 | 业务已完成最后一步,只差提交 | 仍在执行中,或包含外部调用和多阶段逻辑 |
| 复制状态 | 无明显复制延迟 | 主从延迟、日志堆积或下游消费变慢 |
| 磁盘状态 | 写入延迟正常 | 磁盘忙、Redo 或 Binlog 刷盘延迟 |
| 终止后影响 | 可明确重试且具备幂等性 | 不清楚已完成步骤,重试可能重复写入 |
只读事务通常不需要大规模数据回滚,但终止仍可能影响正在运行的报表请求。写事务终止后,InnoDB 会回滚未提交的数据,期间锁不一定立即消失;如果事务修改量大,回滚可能持续较长时间,不能因为客户端已经断开就认为资源马上释放。
提交本身还可能受到 Redo 日志刷盘、Binlog 刷盘、同步复制和磁盘延迟影响。具体耗时与 innodb_flush_log_at_trx_commit、sync_binlog、复制模式和硬件条件有关,不能只根据事务年龄推断提交一定很慢。
KILL QUERY 和 KILL CONNECTION 的区别
KILL QUERY connection_id:尝试终止当前正在执行的语句,但通常不会结束整个会话,也不一定回滚整个事务;KILL CONNECTION connection_id:终止连接,未提交事务会进入回滚流程,并释放该连接持有的资源;- 对处于
Sleep的空闲事务执行KILL QUERY往往不能解决问题,因为它当前没有正在执行的 SQL; - 对正在等待锁的受害连接,终止等待方可能比终止阻塞方影响更小,但只能解决当前请求,不能消除根因。
执行终止操作前,应确认已经有可用备份、Binlog 或业务补偿方案,并记录操作对象。这里的“备份”不是为了撤销 KILL,而是为了在误判业务状态或需要数据核对时保留恢复依据。
示例命令如下,必须把连接 ID 替换为刚刚复核过的目标值:
-- 先终止当前语句,不结束会话
KILL QUERY 438;
-- 只有确认可以放弃该连接未提交事务时才执行
KILL CONNECTION 421;
如果执行后连接没有立即消失,继续观察事务状态和回滚进度,不要反复发送 KILL,也不要把重启 MySQL 作为首选方案。重启可能扩大影响范围,恢复阶段仍可能需要处理未完成的回滚。
修复连接池和事务边界
长事务问题反复出现时,修复重点应放在应用生命周期,而不是依赖数据库管理员手工清理。
建议将事务范围控制在必要的数据库操作内:
- 借出连接后,紧接着开启事务;
- 在事务内只执行必要的读写,不调用外部 HTTP、消息队列或人工确认流程;
- 成功路径明确提交;
- 所有异常路径执行回滚;
- 连接归还连接池前,检查事务状态并重置会话变量;
- 对批量任务按业务可接受的批次提交,并记录批次号和幂等键。
批量提交不能机械按固定数量处理。应结合单行大小、索引数量、锁竞争、Redo 生成量和复制能力测试。例如,几千行窄表更新与几千行宽表更新的事务压力可能完全不同。拆分批次后要验证中途失败时是否能安全重试,避免只解决锁时间而引入重复写入。
连接池侧应持续采集借用时长、归还时长、等待连接数、活跃连接数和异常归还数。可以设置“事务持续时间”和“连接持有时间”告警,但阈值应依据业务正常分位数和接口超时设置,不建议把某个固定秒数当成所有业务的通用标准。
wait_timeout 可以清理长时间闲置的连接,但它不是事务治理方案。过于激进的设置可能在业务仍认为连接可用时强制断开连接,导致请求失败和事务回滚。调整前应核对连接池保活策略、连接最大生命周期和应用重连逻辑。
修复后的复测与持续观察
完成业务回滚、终止异常连接或发布应用修复后,按同一条链路复测,不要只看接口是否恢复。
数据库侧复测
SELECT
trx_id,
trx_state,
TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS trx_age_seconds,
trx_mysql_thread_id AS connection_id,
trx_rows_locked,
trx_rows_modified,
trx_query
FROM information_schema.innodb_trx
ORDER BY trx_started;
同时再次查询:
SELECT COUNT(*) AS lock_wait_count
FROM performance_schema.data_lock_waits;
预期结果是异常事务消失,锁等待数量恢复到业务正常范围,且没有新的长事务迅速出现。如果事务变为 ROLLING BACK 或连接已经终止但资源仍未完全释放,应继续观察,不要立即判定修复失败。
应用和资源侧复测
至少核对以下指标:
- 接口超时率、数据库错误率和重试次数;
- 连接池等待数、活跃连接数和连接持有时间;
Threads_running是否回到正常区间;- 行锁等待是否再次增长;
- History list length 是否停止持续上升;
- 磁盘写入延迟、Redo 使用和复制延迟;
- 同一业务请求是否出现重复提交或补偿任务。
持续观察时,可以按事务年龄、修改行数和是否持锁分级告警。只读长事务更适合关注历史版本和资源趋势;写事务则应优先关注锁等待、业务影响和终止后的回滚状态。把这些指标与连接池日志中的业务请求 ID 关联起来,下一次出现超时,就能从“哪个接口受影响”快速追到“哪个连接开启了事务、持有什么锁、为什么没有提交”。
本文由A5数据原创发布,转载请注明出处:https://www.a5idc.com/helpcontent/6522.html



