企业官网建设流程全解析

MySQL 长事务与大事务：从一次线上雪崩讲透危害、定位与治理

数据库突然不可用，看监控 CPU、IO 都不高，就是所有请求都在转圈——
十次里有八次是长事务干的。

本篇讲清长事务到底会引发什么连锁反应、8.0 里该怎么准确定位（注意锁表的名字已经变了）、
杀连接之前必须想清楚什么，以及应用层该怎么从根上避免。

一、先分清两个概念：长事务 ≠ 大事务

这两个词经常被混用，但它们是两个不同的维度：

现实中它们经常同时出现（一个大事务必然是长事务），但治理手段不同：
前者治的是"事务边界"，后者治的是"批量拆分"。

二、看一次典型的雪崩链路

先用一个最小复现感受一下。会话 A：

BEGIN;SELECT*FROMuser_accountWHEREuser_id=1FORUPDATE;-- 业务代码跑去调支付网关了，30 秒后才回来 COMMIT

会话 B：

UPDATEuser_accountSETbalance=balance+100WHEREuser_id=1;-- 阻塞等待...-- 超过 innodb_lock_wait_timeout（默认 50s）后报：-- ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

看起来只是"第二条 SQL 慢了一点"。真实线上会演变成这样：

① 长事务 A 持有行锁不释放 ↓ ② 所有打到同一行的请求全部进入 LOCK WAIT ↓ ③ 应用侧每个请求都占着一条数据库连接不放 ↓ ④ 连接池耗尽 → 后续请求连不上数据库（哪怕跟这张表无关的请求也失败） ↓ ⑤ 客户端超时重试 → 又来一批请求再占一批连接 ↓ ⑥ max_connections 打满 → 整个实例不可用

一个久不提交的事务，最终能拖垮整个数据库实例，这就是它被称为"数据库杀手"的原因。
更麻烦的是，此时SHOW PROCESSLIST里大部分连接是Sleep或Query状态，
CPU 和磁盘 IO 都很闲，监控上看不出异常——排查的人很容易跑偏。

三、长事务的六宗罪

3.1 锁长时间不释放（最直接）

事务持有的锁只有在COMMIT / ROLLBACK时才释放。跑完 SQL 不等于锁释放了，
所以trx_query可能已经是 NULL（SQL 早执行完了），但锁还在手里。这是最反直觉的一点。

3.2 undo 无法 purge，History List 暴涨

MVCC 需要保留数据的历史版本。只要有一个事务的 ReadView 还活着，
从它开始之后所有被删改的行，其旧版本都不能清理。

SHOWENGINEINNODBSTATUS\G-- History list length 128 ← 平时可能是几十，长事务期间会一路涨到几十万

后果是 undo 表空间持续膨胀、purge 线程跟不上，
而且这类空间即便事务结束也不会自动还给文件系统（要等innodb_max_undo_log_size
触发 truncate，配置不当时可能长期占着几十上百 GB）。

⚠️ 注意：只读长事务也有这个危害。很多人以为"我只 SELECT 没事"，
但在 RR 隔离级别下，BEGIN; SELECT ...;之后不提交，
这个 ReadView 一样会卡住 purge。这也解释了为什么一些只读报表查询能拖慢整个实例。

3.3 主从延迟

主库上一个跑了 10 分钟的事务，会先老老实实执行完，binlog 一次性落盘，
从库再回放这同样的一条大事务——这期间从库就是落后 10 分钟以上。
ROW 格式下，一条改 200 万行的事务在从库也要回放 200 万次行变更，
即使开了并行复制，单事务内部也无法并行。

3.4 回滚代价远超想象

大事务中途失败或被 KILL，回滚要做的工作量约等于正向执行的工作量，
而且回滚期间相关资源依然被占着。一个跑了 20 分钟的批量 DELETE，
回滚可能也要 20 分钟——期间这张表基本不可用。

3.5 阻塞在线 DDL

MDL（元数据锁）是另一个隐性杀手。事务只要碰过某张表，就会持有该表的 MDL 读锁直到事务结束。
这时任何ALTER TABLE、OPTIMIZE TABLE、甚至某些TRUNCATE都会被堵在
Waiting for table metadata lock，进而把后续所有访问该表的查询一起堵住。

3.6 binlog cache 溢出到磁盘

事务产生的 binlog 先写在会话级的 binlog cache（binlog_cache_size，默认 32KB）里，
超过阈值就写临时文件。大事务会频繁触发磁盘临时文件，性能明显下降。

四、怎么把它找出来（MySQL 8.0 的正确姿势）

4.1 第一步：看有哪些事务在跑

SELECTtrx_id,trx_state,trx_started,trx_wait_started,trx_mysql_thread_id,trx_rows_locked,trx_rows_modified,trx_weight,trx_isolation_level,trx_query,TIMESTAMPDIFF(SECOND,trx_started,NOW())ASduration_secFROMinformation_schema.INNODB_TRXORDERBYtrx_started\G

重点字段：

trx_state = ROLLING BACK是一个重要信号：说明有人刚把它掐了，正在回滚，
此时你不该再 KILL，耐心等它回滚完。

4.2 第二步：找到它到底执行过什么 SQL

trx_query是 NULL 时最让人抓狂。这时要靠performance_schema：
先由进程 id 找到线程 id，再查该线程的历史语句。

-- ① 由 MySQL 连接 id 找 performance_schema 的 thread_idSELECTthread_id,processlist_id,processlist_user,processlist_hostFROMperformance_schema.threadsWHEREprocesslist_id=22;-- 来自 INNODB_TRX.trx_mysql_thread_id-- ② 查看这个连接执行过的 SQL（含已执行完的）SELECTthread_id,event_id,sql_text,timer_wait/1000000000000ASexec_secFROMperformance_schema.events_statements_historyWHEREthread_id=62-- 上一步查到的 thread_idORDERBYevent_id;

⚠️events_statements_history默认每个线程只保留最近10 条语句，
可以用events_statements_history_long查更长的历史（记得确认 performance_schema 已开启，
performance_schema_consumer_events_statements_history = ON）。

一条更顺手的联合查询（直接找出长事务 + 它最后执行的 SQL）：

SELECTtrx.trx_id,TIMESTAMPDIFF(SECOND,trx.trx_started,NOW())ASduration_sec,p.IDASconn_id,p.USER,p.HOST,p.DB,p.COMMAND,stmt.SQL_TEXTASlast_sqlFROMinformation_schema.INNODB_TRX trxJOINinformation_schema.PROCESSLIST pONp.ID=trx.trx_mysql_thread_idLEFTJOINperformance_schema.threads thONth.PROCESSLIST_ID=p.IDLEFTJOINperformance_schema.events_statements_current stmtONstmt.THREAD_ID=th.THREAD_IDWHERETIMESTAMPDIFF(SECOND,trx.trx_started,NOW())>10ORDERBYduration_secDESC;

4.3 第三步：看锁等待（⚠️ 8.0 表名变了）

这是一个很容易踩的版本差异：

最省事的写法是用 sys schema 视图，它对新版本做过适配：

SELECT*FROMsys.innodb_lock_waits\G-- 直接给出：谁在等(waiting_pid/waiting_query)、谁在堵(blocking_pid/blocking_trx_age)-- 甚至贴好了处置语句 sql_kill_blocking_query / sql_kill_blocking_connection

需要更细的信息时用底层表：

SELECTrequesting_engine_transaction_idASwaiting_trx,blocking_engine_transaction_idASblocking_trx,object_schema,object_name,index_name,lock_type,lock_modeFROMperformance_schema.data_lock_waits;

4.4 别忘了查一个特殊物种：悬挂的 XA / PREPARE 事务

分布式事务走到XA PREPARE之后如果连接断开，
这个事务会永久停留在 prepared 状态，既占 undo 又占锁，普通KILL干不掉它。

XA RECOVER;-- 列出所有 prepared 状态的事务XAROLLBACK'xid值';-- 确认无用后手工回滚

很多"查不到源头、history list 一直降不下来"的诡异案例，最后查出来是它。

五、KILL 之前，先想清楚三件事

处置的标准动作是：

KILL22;-- 掐断连接，连接关闭时事务自动回滚KILLQUERY22;-- 只终止当前 SQL，事务本身还在（可能继续持有已获得的锁）

但动手前必须确认：

1. 这个事务在做什么业务？用第四节的 SQL 把它的语句历史拉出来，
   和业务方确认能不能断。金融、账户类操作贸然 KILL 可能造成数据需要人工修复。
2. 它改了多少行？看trx_rows_modified/trx_weight。
   数值巨大的话，回滚本身可能耗时很久且期间资源仍被占用，要有心理预期，别频繁重复 KILL。
3. KILL 之后会不会立刻再来一次？如果是应用的定时脚本或某个接口触发的，
   不修复应用逻辑，KILL 一千次也没用。

另外注意：wait_timeout到期的连接会被断开，事务随之回滚。
这算是一种保险机制，但别指望它救场——默认 8 小时太长了。

六、应用层治理：这才是根本

6.1 最常见的坑：事务里做不该做的事

Spring 里一个典型的错误写法：

@Transactional// 事务从这里开始publicvoidsettle(LongorderId){Ordero=orderMapper.selectForUpdate(orderId);// SELECT ... FOR UPDATE，拿锁remotePayService.pay(o);// ❌ 调用外部 HTTP 接口，可能几十秒到几分钟remoteSmsService.send(o);// ❌ 又调一个fileStorage.upload(report);// ❌ 再写一个文件orderMapper.updateStatus(o);// 最后才提交}// 事务在这里才 COMMIT —— 锁被持有了整整一轮远程调用

改法很简单：把 @Transactional 收窄到只包住数据库操作，远程调用全部挪出去：

publicvoidsettle(LongorderId){Orderlocked=txExecutor.requireNew(()->{Ordero=orderMapper.selectForUpdate(orderId);orderMapper.updateStatus(o);returno;// 事务在此提交，锁立刻释放});remotePayService.pay(locked);// ✅ 事务外做远程调用remoteSmsService.send(locked);}

铁律：事务里不做远程调用、不做文件 IO、不等用户输入、不做大循环计算。

6.2 大批量操作一定要拆

错误写法：

DELETEFROMoperation_logWHEREcreate_time<'2025-01-01';-- 一次性删几百万行

正确写法（分批 + 限量 + 每批独立提交）：

DELETEFROMoperation_logWHEREcreate_time<'2025-01-01'LIMIT5000;-- 每个批次一个事务，循环执行，批次之间可以 sleep 100ms 平滑 IO

或者按主键区间切分（更可控、可断点续跑）：

DELETEFROMoperation_logWHEREid>=100000ANDid<200000;

批量插入同理，INSERT INTO ... VALUES一行里堆 10 万条会产生巨大的 redo / undo 和大事务，
拆成每批 1000 条一批，既快又安全。

经验阈值：单事务影响行数控制在几千到几万以内；执行时间控制在1 秒以内最好，
超过 3 秒就该警觉。

6.3 检查连接池与 autocommit

几个容易出事的习惯：

6.4 给足超时兜底

SELECT@@innodb_lock_wait_timeout;-- 默认 50 秒SETGLOBALinnodb_lock_wait_timeout=15;-- 很多互联网团队设到 10~20 秒

它的意义是让问题快速暴露而不是无限堆积：等 50 秒意味着一个慢锁能让连接池很快打满，
等 15 秒则能更早返回错误、触发熔断。

只读查询还可以设置执行时间上限：

SELECT/*+ MAX_EXECUTION_TIME(2000) */COUNT(*)FROMhuge_table;-- 毫秒，超时自动中止

⚠️ 注意innodb_rollback_on_timeout默认是 OFF：
超时只会回滚最后那条语句，不会回滚整个事务。
应用层捕获Lock wait timeout后必须显式 ROLLBACK，
否则带着半成品继续往下执行会造成更难查的数据问题。

6.5 别忽略只读长事务

• 报表/导出类查询单独打到从库，且不要放进显式事务里
• 备份场景用mysqldump --single-transaction时，它会开一个一致性快照事务，
  注意别在业务高峰期跑到几十分钟
• 交互式客户端（Navicat、DataGrip）开着自动事务没提交，也可能一挂就是几小时——
  养成执行完立刻COMMIT或ROLLBACK的习惯

七、监控与告警

把下面这条作为巡检/告警的基础，按实际阈值调整（比如 > 30 秒告警、> 120 秒严重告警）：

SELECTCOUNT(*)ASlong_trx_cnt,MAX(TIMESTAMPDIFF(SECOND,trx_started,NOW()))ASmax_secFROMinformation_schema.INNODB_TRXWHEREtrx_started<NOW()-INTERVAL30SECOND;

建议一起纳入监控的三个指标：

八、常见误区

误区 1：trx_query是 NULL 说明事务空闲、很安全
——恰恰相反，SQL 已执行完但事务未提交才是典型的长事务形态，锁依然握着。

误区 2：只有写事务才危险
——RR 下只读长事务的 ReadView 会阻塞 purge，导致 undo 膨胀、查询变慢。

误区 3：KILL 了就万事大吉
——大事务回滚可能比正向执行更久，且回滚期间资源仍被占用；不修应用逻辑还会重演。

误区 4：把innodb_lock_wait_timeout调大就能解决问题
——那只是让请求"排队更久"，线程池/连接池会更快耗尽。应该调小并让熔断机制生效。

误区 5：8.0 里还去查INNODB_LOCK_WAITS
——这张表在 8.0 已被移除，改用performance_schema.data_lock_waits或sys.innodb_lock_waits。

误区 6：只要 SQL 跑得快就不会有长事务
——快慢是 SQL 的事；长事务是事务边界的事。ORM 自动开事务、
连接池连接泄露、交互式客户端忘提交，都能造出长事务。

九、小结

• 长事务（时间长不提交）和大事务（一次性改海量行）是不同维度，
  分别治的是"事务边界"和"批量拆分"
• 危害链路：持锁不释放 → 锁等待雪崩 →连接池 / max_connections 打满 → 整个实例不可用，
  而此时 CPU、IO 看起来可能完全正常
• undo 无法 purge、History list length暴涨、主从延迟、回滚耗时、阻塞 MDL 都是它的派生伤害
• 只读长事务同样有害：RR 下常驻的 ReadView 会卡住 purge
• 排查三板斧：information_schema.INNODB_TRX→performance_schema.events_statements_history
  →sys.innodb_lock_waits；8.0 已无INNODB_LOCK_WAITS，改用data_lock_waits
• trx_query = NULL不代表事务空闲；trx_state = ROLLING BACK时别再 KILL
• KILL 前确认三件事：业务能否中断、回滚量多大、源头会不会重来
• 应用层铁律：事务里不做远程调用、不等 IO、不做大循环；@Transactional只包数据库操作
• 批量操作要分批 + LIMIT + 每批独立提交；单事务控制在几万行、秒级完成
• innodb_lock_wait_timeout建议调到 10~20 秒让它快速失败；注意默认只回滚最后一条语句，
  应用层必须显式 ROLLBACK
• 监控告警盯住：长事务数、History list length、锁等待时长

到这里，MySQL 事务这条线就完整了：隔离级别决定你看到什么，日志决定数据丢不丢，
内存决定快不快，而长事务决定这套系统在高并发下会不会塌。