MySQL 长事务排查实战：从 innodb_trx 到 history list 治理

dbaai

MySQL 长事务排查实战：从 innodb_trx 到 history list 治理

一、具体的问题

上个月接手一套 MySQL 5.7 的订单库，业务反馈两件事：一是每天下午的统计脚本跑得越来越慢，同一条 SQL 早上跑 8 秒，下午要 40 多秒；二是运维想给一张 6000 万行的大表加个字段，ALTER TABLE 卡在 Waiting for table metadata lock 十几分钟不动，只能放弃。

DBA 先看机器，CPU 30%、磁盘不忙、内存充足，什么硬件问题都没有。再看 SHOW PROCESSLIST，也没有特别离谱的运行中查询——大部分会话的 Command 都是 Sleep。看起来"数据库很闲"，但用户就是慢。

真正的问题藏在一个跑了 4 小时 37 分钟的事务里：一个批处理程序用框架开启了事务，中间调用了一个外部接口超时挂住，既不提交也不回滚。这个事务改了几十万行数据，于是连锁反应开始了：undo 历史链越拉越长，所有基于 MVCC 的一致性读都要沿着 undo 链回溯版本，统计查询越跑越慢；这张表上的元数据锁一直被持有，DDL 在后面排队；purge 线程无法清理，history list length 涨到 180 万。

这不是个例。长事务在 MySQL 里是最典型的"静默劣化"问题——它不报错、不告警（默认情况下）、在 processlist 里经常伪装成一条 Sleep 连接，但它的代价由全库所有读事务分摊。

二、核心原理

先纠正一个普遍的误解：长事务的危害不在于它"跑得久"，而在于它"牵制得多"。InnoDB 是多版本并发控制（MVCC），事务开始后，为了给这个事务提供一致性的读视图，它修改过、以及它"看见过"的所有旧版本数据都不能被清理。事务拖多久，这些旧版本就要保留多久。

具体牵制了四样东西：

第一，undo 链与 history list。 每个更新都会在 undo log 里留旧版本，事务提交后这些记录进入"待清理"状态，由 purge 线程按 history list length（历史链长度）逐步回收。活跃事务挡在中间，purge 就只能干等。历史链越长，两个后果：undo 表空间膨胀；更重要的是，需要读旧版本的一致性读（普通 SELECT）要沿着链回溯更多版本，读越多越慢。这就是下午统计脚本变慢的直接原因——不是数据变了，是版本链变长了。

第二，锁。 事务持有的行锁、间隙锁、MDL 都要到提交或回滚才释放。一个挂住的事务，会让所有想锁同一行的写入方排队，也会让排在它后面的 DDL 拿不到 MDL——而且 MDL 是公平队列，DDL 拿不到，排在 DDL 后面的新查询也全部跟着堵，这就是"一条 Sleep 连接拖垮全库"的机制。

第三，回滚成本。 这是最容易被低估的一点。KILL 一个修改了 50 万行的事务，InnoDB 要把这 50 万行逐一回滚——回滚通常比原事务执行还要慢（原事务是顺序写，回滚是按相反顺序逐条逆操作，且大量随机读）。所以"发现了长事务，KILL 掉就行"这句话对了一半：KILL 之前必须评估回滚代价，否则止血动作本身会变成新的故障源。

第四，复制与闪回的连带影响。 大事务的 binlog 事件在从库同样要重放很久；如果启用了 GTID，大事务还会让 binlog_transaction_dependency_tracking 的并行度退化。

还有一个隐蔽点：长事务在 SHOW PROCESSLIST 里通常显示为 Sleep。事务开启后如果当前没有正在执行的 SQL，Command 就是 Sleep，Time 显示的是"当前这条 SQL 空闲多久"，不是"事务开了多久"。所以靠 processlist 按时间排序找长事务，基本找不到目标——必须查 information_schema.innodb_trx 的 trx_started。

另外注意，autocommit=1 并不能防住长事务。只要应用显式发了 BEGIN（很多框架的声明式事务就是这么干的），事务就活了；还有一种更隐蔽的：SELECT 型事务——用 START TRANSACTION WITH CONSISTENT SNAPSHOT 做数据导出（mysqldump 单事务模式如果卡住也会这样），一条只读的长事务同样能撑爆历史链。

三、实例参考（动手步骤）

下面是完整的排查与治理步骤，可以在自己环境里照做。

步骤 0：确认现状基线

先量化当前长事务和历史链的底数：

2. -- 当前活跃事务全景，按开始时间排序
3. SELECT trx_id, trx_state, trx_started,
4.        TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS trx_seconds,
5.        trx_mysql_thread_id AS conn_id,
6.        trx_rows_locked, trx_rows_modified,
7.        LEFT(trx_query, 60) AS current_sql
8. FROM information_schema.innodb_trx
9. ORDER BY trx_started ASC;
11. -- 历史链长度：8.0 用 innodb_metrics
12. SELECT variable_value AS history_list
13. FROM information_schema.innodb_metrics
14. WHERE name = 'trx_rseg_history_len';
15. -- 5.7 无上面视图，用 SHOW ENGINE INNODB STATUS 看 TRANSACTIONS 段的 History list length
16. SHOW ENGINE INNODB STATUS\G

复制代码

5.7 里 history list length 更稳的取法：

2. -- 活跃事务数（非 history）：
3. SELECT count(*) FROM information_schema.innodb_trx;

复制代码

5.7 没有 Innodb_history_list_length 状态变量的话，直接 SHOW ENGINE INNODB STATUS 抓 TRANSACTIONS 段，或者用上面的 innodb_metrics（8.0）。两个版本的取法都先在测试库确认一遍再上生产脚本。

本次案例的基线值：最长活跃事务 trx_started 距今 4 小时 37 分，trx_rows_modified = 512,340；trx_rseg_history_len = 1,820,000；undo 表空间 ibdata 内 undo 段占用 47GB（5.7 集中式 undo，回缩要重建实例或用 8.0 的独立 undo）。

步骤 1：抓出"隐身"的长事务会话

事务的持有者大概率是条 Sleep 连接，把事务表和会话表联起来看：

2. SELECT t.trx_id, t.trx_started,
3.        TIMESTAMPDIFF(SECOND, t.trx_started, NOW()) AS trx_seconds,
4.        p.id AS conn_id, p.user, p.host, p.db,
5.        p.command, p.time AS sql_idle_seconds,
6.        LEFT(IFNULL(p.info, ''), 60) AS last_sql
7. FROM information_schema.innodb_trx t
8. JOIN information_schema.processlist p
9.   ON p.id = t.trx_mysql_thread_id
10. ORDER BY t.trx_started ASC;

复制代码

重点看两个值的组合：trx_seconds 大（比如超过 300 秒）且 command = Sleep——这就是挂住的事务，连着的 host 直接告诉你哪台应用机、哪个账号。

再补一刀，看它挡住了谁：

2. -- 8.0 直接用 sys 视图；5.7 装 sys schema 后同样可用
3. SELECT * FROM sys.innodb_lock_waits\G
4. -- 被 MDL 卡住的 DDL 排队情况（5.7 抓 metadata lock 需开启 instrument）：
5. UPDATE performance_schema.setup_instruments
6. SET enabled = 'YES' WHERE name = 'wait/lock/metadata/sql/mdl';
7. UPDATE performance_schema.setup_consumers
8. SET enabled = 'YES' WHERE name LIKE '%events_statements%';
9. SELECT * FROM sys.schema_table_lock_waits\G

复制代码

案例中 innodb_lock_waits 显示 27 个写入会话在等那个 4 小时事务的行锁，schema_table_lock_waits 显示加字段语句被它挡住，后面又挂了 60 多个普通查询——链式堵塞的证据链齐了。

步骤 2：评估回滚代价，再决定怎么止血

KILL 前先看 trx_rows_modified。经验参考：普通 SSD 上 InnoDB 回滚速度大致在每秒几千行的量级（取决于行宽和索引数），50 万行大概率要 3~8 分钟。这个时间窗口内锁还被持有、业务还在堵，要选好执行时机：

2. -- 查看事务已修改行数
3. SELECT trx_mysql_thread_id, trx_rows_modified
4. FROM information_schema.innodb_trx
5. WHERE TIMESTAMPDIFF(SECOND, trx_started, NOW()) > 300;
7. -- 评估后执行（窗口期内先和应用方确认，必要时切走流量）
8. KILL 4821;

复制代码

如果事务修改行数到了百万级，回滚要几十分钟，比等它自己结束更糟——这时更好的选择是：联系应用方让它超时返回/自行提交，或者用主备切换绕过（kill 老主，事务随实例下线，备库提升后回滚在后台做，不影响新主服务）。

本次案例 trx_rows_modified = 51 万，评估约 5 分钟，安排在流量低峰 KILL，4 分 12 秒完成回滚，锁等待队列立刻清空，MDL 排队消失，DDL 随后 38 秒执行完成。

步骤 3：根因修复——从应用侧消灭长事务

止血之后必须修源头，否则明天还会长出来。按检出频率排三个方向：

方向一：事务里包了外部调用。 本次案例的根因：@Transactional 方法里调用了一个 HTTP 接口，超时 5 分钟，重试 1 次就是 10 分钟，接口再抽风就是小时级。修法是把外部调用挪出事务边界，或把事务拆到"拿到结果之后"的那一段：

2. // 修复前：事务包裹整个方法，包含远程调用
3. @Transactional
4. public void handle(Order o) {
5.     orderDao.update(o);              // 占着锁
6.     String r = httpClient.call(o);   // 挂 5 分钟，事务跟着挂
7.     recordDao.insert(r);
8. }
10. // 修复后：远程调用移出事务
11. public void handle(Order o) {
12.     orderDao.updateInNewTx(o);       // 独立短事务
13.     String r = httpClient.call(o);   // 事务外
14.     recordDao.insertInNewTx(r);
15. }

复制代码

方向二：交互式事务泄漏。 代码开了 BEGIN 但异常路径没有 finally { rollback/commit }，连接归还池时事务还挂着。治本靠代码规范，治标可以在数据库侧设保底：SET GLOBAL innodb_kill_idle_transaction = 60;（Percona 版本参数，杀掉空闲超过 60 秒的事务；官方版 5.7 无此参数，用 pt-kill 兜底）：

2. # pt-kill 兜底：杀掉空闲超过 120 秒且处于事务中的连接，先打日志观察一周再真杀
3. pt-kill --host=127.0.0.1 --user=monitor --password=xxx \
4.   --match-command Sleep --idle-time 120 --victims all \
5.   --print --interval 10
6. # 观察确认无误伤后，把 --print 换成 --kill

复制代码

方向三：只读长事务。 定时导出任务用 START TRANSACTION WITH CONSISTENT SNAPSHOT 后卡住不动。给导出脚本加超时和断点续跑，并限制在备库执行。

步骤 4：监控防线，让长事务活不过 5 分钟

最后一道防线是把它变成指标。核心两条：

2. -- 告警 SQL：每分钟跑一次，检出超过 60 秒的活跃事务
3. SELECT trx_mysql_thread_id, trx_started,
4.        TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS sec,
5.        trx_rows_modified
6. FROM information_schema.innodb_trx
7. WHERE TIMESTAMPDIFF(SECOND, trx_started, NOW()) > 60;

复制代码

配合 shell 采样脚本接到现有告警通道：

2. #!/bin/bash
3. # long_trx_check.sh —— crontab 每分钟执行
4. R=$(mysql -h127.0.0.1 -umonitor -pxxx -N -e \
5.   "SELECT COUNT(*) FROM information_schema.innodb_trx \
6.    WHERE TIMESTAMPDIFF(SECOND, trx_started, NOW()) > 60")
7. if [ "$R" -gt 0 ]; then
8.   echo "$(date '+%F %T') long_trx=$R" >> /var/log/db/long_trx.log
9.   # 对接告警接口（curl 到 alert 网关）
10. fi
11. # history list 同步采样
12. HL=$(mysql -h127.0.0.1 -umonitor -pxxx -N -e \
13.   "SELECT variable_value FROM performance_schema.global_status \
14.    WHERE variable_name='Innodb_history_list_length'" 2>/dev/null)
15. [ -n "$HL" ] && echo "$(date '+%F %T') hist=$HL" >> /var/log/db/history.log

复制代码

告警阈值建议：活跃事务超过 60 秒告 P1；history list length 持续超过 10 万告 P2、超过 100 万告 P1（超过 100 万意味着 purge 已明显滞后，一致性读性能会整体下滑）。

四、实操检查清单

• 每日巡检 information_schema.innodb_trx，最长事务超过 60 秒即告警，超过 300 秒人工介入。
  
• 检查历史链：history list length 常态应低于 1 万，超 10 万排查 purge 滞后与长事务。
  
• 找长事务一律以 trx_started 为准，不要用 processlist 的 Time 列（那只是当前 SQL 的空闲时长）。
  
• KILL 前必查 trx_rows_modified 估算回滚时长；百万行级别改走主备切换，不要硬 KILL。
  
• 排查应用事务边界：事务方法内禁止远程调用、禁止人工等待/重试；导出类任务加超时并放备库。
  
• Percona 版本可开 innodb_kill_idle_transaction（建议 60~120 秒），官方版本用 pt-kill 兜底，先 --print 观察一周再切 --kill。
  
• 5.7 上把 MDL 相关 instrument 打开，确认 sys.schema_table_lock_waits 可用，DDL 卡住时能立刻定位挡路的会话。
  
• 8.0 实例启用独立 undo 表空间并配置 innodb_undo_log_truncate=ON，让 undo 能自动回缩；5.7 升级 8.0 时一并规划 undo 重构。
  
• 每次大事务（超过 10 万行修改的批处理）上线前评估：能否分批提交？是否落在低峰窗口？
  
• 监控备份类任务（mysqldump --single-transaction 等）的执行时长，防止只读长事务悄悄撑历史链。
  

几个容易踩的坑

坑一：把 processlist 的 Time 当事务时长。 Sleep 连接的 Time 是"当前没有 SQL 执行的秒数"，不是事务年龄。一个开了 4 小时、刚刚发过一条查询的事务，Time 可能只有几秒。找长事务只认 innodb_trx.trx_started。

坑二：KILL 之后以为马上恢复。 KILL 触发的是回滚，50 万行的回滚要几分钟，期间锁照旧持有、堵塞照旧。止血动作要选窗口期，并提前告知业务方"接下来几分钟可能仍有卡顿，这是在还债"。

坑三：只盯着写事务。 只读的一致性快照事务（长事务导出、被遗忘的 START TRANSACTION WITH CONSISTENT SNAPSHOT）不持行锁，但同样压制 purge、同样拉长版本链。innodb_trx 里它们照样可见，别因为"它没改数据"就放过。

坑四：认为 autocommit=1 就万事大吉。 框架的声明式事务、连接池的 autocommit=false 初始化设置、批处理框架隐式开事务，都会绕过 autocommit。要在会话参数和代码两个层面都检查。

坑五：5.7 上想收缩 undo。 5.7 的 undo 默认在 ibdata 里，长事务撑大的空间收不回来（独立 undo 参数需要初始化时配置且 truncate 支持不完整）。别在生产 5.7 上尝试"清理 undo"，正确路径是规划升级 8.0。

治理前后对比

指标	治理前	治理后
最长活跃事务	4h37m	< 2min（60s 告警兜底）
history list length	1,820,000	< 8,000（常态）
下午统计脚本 P95	41s	9s（回到上午水位）
MDL 排队致 DDL 失败	每周 2~3 次	0（连续 60 天）
行锁等待队列峰值	27 个会话	0~1 个
长事务平均检出时间	用户报障后 2 天	告警 1 分钟内
误杀/回滚引发的二次故障	无法统计	0（KILL 前强制评估）

长事务治理的本质是把"全库分摊的隐性成本"变成"应用侧的一次显式整改"。指标上去了只是结果，真正的杠杆在事务边界设计：事务里只放数据库操作，其他一切挪出去。