数据库服务器MySQL长事务如何定位阻塞源和提交风险?

发布人:A5数据发布时间:2026-10-30 09:45阅读量:108

线上接口出现超时、数据库连接池等待升高,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 是阻塞源,还需要查询锁等待关系,确认两个事务是否关联到同一锁对象。

421 处于 Sleep,关联事务 912345678,事务年龄约 1850 秒;438 处于 Query 和 LOCK WAIT,关联事务 912345679

处理没有事务记录的情况

如果应用请求超时,但 innodb_trx 没有明显长事务,可能存在以下分支:

  1. 请求还在连接池中等待,没有到达 MySQL;
  2. 等待的是元数据锁,而事务信息尚未按预期显示;
  3. SQL 是非 InnoDB 表或涉及其他存储引擎;
  4. 连接已经断开,应用正在重试;
  5. 主要瓶颈是 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 作为首选方案。重启可能扩大影响范围,恢复阶段仍可能需要处理未完成的回滚。

修复连接池和事务边界

长事务问题反复出现时,修复重点应放在应用生命周期,而不是依赖数据库管理员手工清理。

建议将事务范围控制在必要的数据库操作内:

  1. 借出连接后,紧接着开启事务;
  2. 在事务内只执行必要的读写,不调用外部 HTTP、消息队列或人工确认流程;
  3. 成功路径明确提交;
  4. 所有异常路径执行回滚;
  5. 连接归还连接池前,检查事务状态并重置会话变量;
  6. 对批量任务按业务可接受的批次提交,并记录批次号和幂等键。

批量提交不能机械按固定数量处理。应结合单行大小、索引数量、锁竞争、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

相关推荐