个人主页 for_ever_love__ 欢迎各位大佬莅临其他栏目: 大模型开发从0到1 其他栏目: iOS项目总结大全 其他栏目: 我想学python了 其他栏目: iOS UI 文章目录MySQL 长事务与大事务从一次线上雪崩讲透危害、定位与治理一、先分清两个概念长事务 ≠ 大事务二、看一次典型的雪崩链路三、长事务的六宗罪3.1 锁长时间不释放最直接3.2 undo 无法 purgeHistory List 暴涨3.3 主从延迟3.4 回滚代价远超想象3.5 阻塞在线 DDL3.6 binlog cache 溢出到磁盘四、怎么把它找出来MySQL 8.0 的正确姿势4.1 第一步看有哪些事务在跑4.2 第二步找到它到底执行过什么 SQL4.3 第三步看锁等待⚠️ 8.0 表名变了4.4 别忘了查一个特殊物种悬挂的 XA / PREPARE 事务五、KILL 之前先想清楚三件事六、应用层治理这才是根本6.1 最常见的坑事务里做不该做的事6.2 大批量操作一定要拆6.3 检查连接池与 autocommit6.4 给足超时兜底6.5 别忽略只读长事务七、监控与告警八、常见误区九、小结MySQL 长事务与大事务从一次线上雪崩讲透危害、定位与治理数据库突然不可用看监控 CPU、IO 都不高就是所有请求都在转圈——十次里有八次是长事务干的。本篇讲清长事务到底会引发什么连锁反应、8.0 里该怎么准确定位注意锁表的名字已经变了、杀连接之前必须想清楚什么以及应用层该怎么从根上避免。一、先分清两个概念长事务 ≠ 大事务这两个词经常被混用但它们是两个不同的维度维度长事务Long Transaction大事务Large Transaction特征执行时间长一直不 COMMIT一次性改动海量行典型场景事务里调了外部接口 / 等人确认 / 连接池泄露DELETE FROM log WHERE create_time ?删几百万行主要危害锁长期不释放、ReadView 常驻导致 undo 无法 purgeredo/undo 暴涨、主从延迟、回滚巨慢举例BEGIN; SELECT ... FOR UPDATE;然后睡觉一条 SQL 影响 300 万行现实中它们经常同时出现一个大事务必然是长事务但治理手段不同前者治的是事务边界后者治的是批量拆分。二、看一次典型的雪崩链路先用一个最小复现感受一下。会话 ABEGIN;SELECT*FROMuser_accountWHEREuser_id1FORUPDATE;-- 业务代码跑去调支付网关了30 秒后才回来 COMMIT会话 BUPDATEuser_accountSETbalancebalance100WHEREuser_id1;-- 阻塞等待...-- 超过 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可能已经是 NULLSQL 早执行完了但锁还在手里。这是最反直觉的一点。3.2 undo 无法 purgeHistory 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 阻塞在线 DDLMDL元数据锁是另一个隐性杀手。事务只要碰过某张表就会持有该表的 MDL 读锁直到事务结束。这时任何ALTER TABLE、OPTIMIZE TABLE、甚至某些TRUNCATE都会被堵在Waiting for table metadata lock进而把后续所有访问该表的查询一起堵住。3.6 binlog cache 溢出到磁盘事务产生的 binlog 先写在会话级的 binlog cachebinlog_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_started事务开始时间与实际时长用它算trx_stateRUNNING/LOCK WAIT/ROLLING BACKtrx_mysql_thread_id对应的 PROCESSLIST 的 idKILL 时用它trx_rows_locked持有多少行锁trx_rows_modified改了多少行判断是大事务还是纯读事务trx_weight事务权重粗略反映撤销代价越大回滚越久trx_query经常是 NULL——SQL 已执行完不代表事务空闲trx_state ROLLING BACK是一个重要信号说明有人刚把它掐了正在回滚此时你不该再 KILL耐心等它回滚完。4.2 第二步找到它到底执行过什么 SQLtrx_query是 NULL 时最让人抓狂。这时要靠performance_schema先由进程 id 找到线程 id再查该线程的历史语句。-- ① 由 MySQL 连接 id 找 performance_schema 的 thread_idSELECTthread_id,processlist_id,processlist_user,processlist_hostFROMperformance_schema.threadsWHEREprocesslist_id22;-- 来自 INNODB_TRX.trx_mysql_thread_id-- ② 查看这个连接执行过的 SQL含已执行完的SELECTthread_id,event_id,sql_text,timer_wait/1000000000000ASexec_secFROMperformance_schema.events_statements_historyWHEREthread_id62-- 上一步查到的 thread_idORDERBYevent_id;⚠️events_statements_history默认每个线程只保留最近10 条语句可以用events_statements_history_long查更长的历史记得确认 performance_schema 已开启performance_schema_consumer_events_statements_history ON。一条更顺手的联合查询直接找出长事务 它最后执行的 SQLSELECTtrx.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.IDtrx.trx_mysql_thread_idLEFTJOINperformance_schema.threads thONth.PROCESSLIST_IDp.IDLEFTJOINperformance_schema.events_statements_current stmtONstmt.THREAD_IDth.THREAD_IDWHERETIMESTAMPDIFF(SECOND,trx.trx_started,NOW())10ORDERBYduration_secDESC;4.3 第三步看锁等待⚠️ 8.0 表名变了这是一个很容易踩的版本差异版本锁等待 / 锁信息表MySQL 5.7information_schema.INNODB_LOCKS、information_schema.INNODB_LOCK_WAITSMySQL 8.0performance_schema.data_locks、performance_schema.data_lock_waits旧表已移除最省事的写法是用 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 状态的事务XAROLLBACKxid值;-- 确认无用后手工回滚很多查不到源头、history list 一直降不下来的诡异案例最后查出来是它。五、KILL 之前先想清楚三件事处置的标准动作是KILL22;-- 掐断连接连接关闭时事务自动回滚KILLQUERY22;-- 只终止当前 SQL事务本身还在可能继续持有已获得的锁但动手前必须确认这个事务在做什么业务用第四节的 SQL 把它的语句历史拉出来和业务方确认能不能断。金融、账户类操作贸然 KILL 可能造成数据需要人工修复。它改了多少行看trx_rows_modified/trx_weight。数值巨大的话回滚本身可能耗时很久且期间资源仍被占用要有心理预期别频繁重复 KILL。KILL 之后会不会立刻再来一次如果是应用的定时脚本或某个接口触发的不修复应用逻辑KILL 一千次也没用。另外注意wait_timeout到期的连接会被断开事务随之回滚。这算是一种保险机制但别指望它救场——默认 8 小时太长了。六、应用层治理这才是根本6.1 最常见的坑事务里做不该做的事Spring 里一个典型的错误写法Transactional// 事务从这里开始publicvoidsettle(LongorderId){OrderoorderMapper.selectForUpdate(orderId);// SELECT ... FOR UPDATE拿锁remotePayService.pay(o);// ❌ 调用外部 HTTP 接口可能几十秒到几分钟remoteSmsService.send(o);// ❌ 又调一个fileStorage.upload(report);// ❌ 再写一个文件orderMapper.updateStatus(o);// 最后才提交}// 事务在这里才 COMMIT —— 锁被持有了整整一轮远程调用改法很简单把 Transactional 收窄到只包住数据库操作远程调用全部挪出去publicvoidsettle(LongorderId){OrderlockedtxExecutor.requireNew(()-{OrderoorderMapper.selectForUpdate(orderId);orderMapper.updateStatus(o);returno;// 事务在此提交锁立刻释放});remotePayService.pay(locked);// ✅ 事务外做远程调用remoteSmsService.send(locked);}铁律事务里不做远程调用、不做文件 IO、不等用户输入、不做大循环计算。6.2 大批量操作一定要拆错误写法DELETEFROMoperation_logWHEREcreate_time2025-01-01;-- 一次性删几百万行正确写法分批 限量 每批独立提交DELETEFROMoperation_logWHEREcreate_time2025-01-01LIMIT5000;-- 每个批次一个事务循环执行批次之间可以 sleep 100ms 平滑 IO或者按主键区间切分更可控、可断点续跑DELETEFROMoperation_logWHEREid100000ANDid200000;批量插入同理INSERT INTO ... VALUES一行里堆 10 万条会产生巨大的 redo / undo 和大事务拆成每批 1000 条一批既快又安全。经验阈值单事务影响行数控制在几千到几万以内执行时间控制在1 秒以内最好超过 3 秒就该警觉。6.3 检查连接池与 autocommit几个容易出事的习惯隐患检查方法连接池/driver 把autocommit关了却没开启事务管理SELECT autocommit;应为 1应用异常后没有 ROLLBACK连接归还池中后事务还挂着看PROCESSLIST里大量Sleep且INNODB_TRX中能查到用了BEGIN但异常路径没提交/回滚代码 review 确认 try/finally事务里跑了很慢的查询还没索引慢 SQL 叠加长事务是灾难组合6.4 给足超时兜底SELECTinnodb_lock_wait_timeout;-- 默认 50 秒SETGLOBALinnodb_lock_wait_timeout15;-- 很多互联网团队设到 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_startedNOW()-INTERVAL30SECOND;建议一起纳入监控的三个指标指标来源异常信号长事务数量 / 最长时间information_schema.INNODB_TRX出现且持续增长History list lengthSHOW ENGINE INNODB STATUS持续上涨不回落锁等待数sys.innodb_lock_waits非空且wait_age_secs增长八、常见误区误区 1trx_query是 NULL 说明事务空闲、很安全——恰恰相反SQL 已执行完但事务未提交才是典型的长事务形态锁依然握着。误区 2只有写事务才危险——RR 下只读长事务的 ReadView 会阻塞 purge导致 undo 膨胀、查询变慢。误区 3KILL 了就万事大吉——大事务回滚可能比正向执行更久且回滚期间资源仍被占用不修应用逻辑还会重演。误区 4把innodb_lock_wait_timeout调大就能解决问题——那只是让请求排队更久线程池/连接池会更快耗尽。应该调小并让熔断机制生效。误区 58.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_waits8.0 已无INNODB_LOCK_WAITS改用data_lock_waitstrx_query NULL不代表事务空闲trx_state ROLLING BACK时别再 KILLKILL 前确认三件事业务能否中断、回滚量多大、源头会不会重来应用层铁律事务里不做远程调用、不等 IO、不做大循环Transactional只包数据库操作批量操作要分批 LIMIT 每批独立提交单事务控制在几万行、秒级完成innodb_lock_wait_timeout建议调到 10~20 秒让它快速失败注意默认只回滚最后一条语句应用层必须显式 ROLLBACK监控告警盯住长事务数、History list length、锁等待时长到这里MySQL 事务这条线就完整了隔离级别决定你看到什么日志决定数据丢不丢内存决定快不快而长事务决定这套系统在高并发下会不会塌。
