MySQL UPDATE 8000 万行被 kill 后 rollback 90 分钟主库锁死 3 小时的真实事故复盘:InnoDB 回滚机制 + 拆批策略 + 10 条治理纪律

2026 年 2 月某个周日凌晨 02:50,我被 DBA 同事电话叫醒——"主库 8 小时没释放 lock,从库延迟从 30 秒涨到 5 小时,QPS 直接腰斩"。我冲到电脑前看监控,看到了让人后背发凉的画面:某个数据合规任务在 22:00 启动,跑一个 UPDATE 涉及 8000 万行,跑了 4 小时还没完;运维同事 02:00 觉得"再不结束就要影响早高峰",force kill 了这个事务。force kill 后 MySQL 进入 rollback,而 rollback 比正向执行还慢——8000 万行已经改了 6000 万行,要回滚这 6000 万,系统估算需要 3 小时。这 3 小时主库锁着这些行,业务大面积超时,从库还在等主库的 binlog,延迟越拉越大。

这是一次"教科书级"的大事务事故。我们的故事不是某种黑科技修法,而是面对一个已经在跑的、回滚不可中断的大事务,只能眼睁睁看着系统降级 3 小时——这是我们事后立"大事务零容忍"纪律的直接动机。这篇是完整复盘,涵盖 InnoDB 事务和回滚的工作原理、为什么 force kill 让情况更糟、大事务的 5 种典型场景、拆批策略、监控告警,以及落地的《MySQL 大事务治理纪律》。如果你的团队还在写"一条 SQL 改千万行"的代码,这篇能帮你认识到这是定时炸弹。

故障的代价我先摊给读者:主库锁住核心表 3 小时 12 分钟期间,业务侧累计超时请求约 41 万笔、用户侧"页面打不开"相关投诉 1280 条、被迫切到只读降级影响下单 6800 笔、客服 + SRE + DBA 这条线整夜未眠 9 个人,事后的对账 + 补偿工时大约 80 人时,直接经济损失粗算在 80 万人民币上下。换来的认知本来应该几年前就建立——"单事务改千万行是禁区"——但我们直到这次才把它沉淀进硬规矩。

背景:这次事故的服务环境

事故时间线

因果链:为什么"看起来安全的 kill"反而把事情炸大

把这次事故 7 个关键环节串起来,因果链就出来了:

这张图里每一步看着都"合理",合在一起就是一个不可逆的雪崩。问题不是任何单一环节,是"大事务"这个抽象从设计源头就违反 MySQL 的工作模型:InnoDB 是为"短事务高并发"设计的,所有内部数据结构(undo / redo / lock / binlog)在大事务下都会成为单点瓶颈。

第一反应:"kill 不就行了吗"

事故当时 DBA 的判断没错——眼看从库延迟在涨,继续跑下去早高峰一定崩。但kill 一个改了 3800 万行的事务,代价被严重低估。InnoDB 的 rollback 工作原理:

1. 事务执行 UPDATE 时,InnoDB 在 undo log 里记录每一行的"旧值"
2. commit 时丢弃 undo log(或者保留到事务可见性窗口结束)
3. rollback 时反向执行 — 把 undo log 里的旧值写回每一行

关键问题:rollback 是单线程的。MySQL 没有"并行 rollback"——回滚 3800 万行就是一个一个写回。而且 rollback 期间相关行被锁(undo 写回时持锁),其他事务访问这些行就 timeout。

更糟的是 rollback 的速度慢于正向 UPDATE——大约慢 1.5-2 倍。原因:

• 正向 UPDATE 可以利用 buffer pool(很多页热的)
• rollback 时几小时前修改的页可能已经 flush 到磁盘 + 被 evict,需要重新从磁盘读
• rollback 还要 redo log 写记录(为了崩溃恢复正确)

所以"kill 3800 万行 UPDATE"等同于"再来一遍但更慢"。MySQL 的 information_schema.innodb_trx 表能看 rollback 进度:

SELECT trx_state, trx_query, trx_rows_modified, trx_rows_locked, trx_started
FROM information_schema.innodb_trx;

# 看到:
trx_state: ROLLING_BACK
trx_rows_modified: 38217845
# 没有直接的"进度百分比", 要靠 modified 数下降推算

当时我们看到的 trx_rows_modified 一直在缓慢下降,根据下降速率(~ 6000 行/秒),3800 万行需要 105 分钟——和实际的 90 分钟接近。

顺带说一个细节:很多 DBA 第一次面对正在 ROLLBACK 的大事务时会去翻文档找"加速 rollback"的开关。InnoDB 提供了 innodb_force_recovery 之类的参数,但它们都是"启动期"才生效的,在线热改没用;还有人想到改 innodb_rollback_segments,但这个对正在跑的 rollback 同样无效。一旦 rollback 开始就只能等,这是 InnoDB 的硬性限制,不是配置能调出来的。如果硬要重启 mysqld 试图绕过 rollback,启动后 InnoDB crash recovery 仍然要把这个事务回滚完才能开服务——只是把"在线等" 变成了"启服务也等",没有任何收益,反而把整个集群拉下来。事故当晚我们 03:10 一度提议"重启 mysqld 试试",DBA 经验丰富,一句话怼回来:"重启之后 crash recovery 还是要回滚,而且重启过程主库直接不可用,损失更大"。这种"看似激进、实则更糟"的方案,在压力下很容易被提出,SOP 里要明确禁止。

真凶 1:大事务为什么这么慢

"为什么单事务改 8000 万行需要 4 小时"——很多人对这个时长不敏感。其实合理:

理论值 42 分钟,但实际 4 小时——多出来的 3 个多小时是哪来的?主要是:

• buffer pool evict:8000 万行的 undo log 累积超过 buffer pool 大小,频繁 page eviction,磁盘 IO 暴涨
• redo log 刷盘等待:事务 commit 前 redo log 必须刷盘,大事务 redo log 单条几 GB,刷盘慢
• binlog 写入:row format binlog 把每行都写下来,8000 万行 binlog 几十 GB,write 慢
• 主从复制锁:从库 SQL Thread 单线程重放,主库的 binlog 跑得快,从库消化跟不上

这些"二级开销"在小事务里看不出来,大事务里集中爆发。

真凶 2:这次任务"必须"作为单事务跑吗

读任务代码:

BEGIN;
UPDATE user_activity SET region='ASIA'
WHERE country_code IN ('CN', 'JP', 'KR', ...);
COMMIT;

非常简单的一条 SQL。事后讨论"为什么不拆批",写代码的同事说"业务需求是'要么全成,要么全不成'"——这是个误解。"全成或全不成"是 ACID 里的 A(原子性)的要求,但实际业务里几乎没有真正"必须全成"的场景。8000 万行 region 更新如果某 1000 行没改,业务影响是"那 1000 用户的 region 字段还是旧的",可以下次任务补——不是灾难。

"原子性必须"的执念,让很多团队写出大事务。实际工程经验:能拆批就拆批,不需要原子性。补偿和重试可以让"最终一致"接近"原子",而代价远小于真原子事务。

我后来跟那位写代码的同事聊了很久,他原本的担心是:"如果中途失败,部分 region 改了部分没改,数据不一致怎么办?"这是个非常普遍的担心,但解法不在"塞进一个事务",而在"让重试天然幂等":每批 UPDATE 配合一个 last_processed_id 表记录进度,失败后从断点重启即可;最终一致性比"全有或全无"更便宜也更稳。我们后续所有批处理脚本都按这套模板写,中途 OOM、断网、机器重启都能优雅恢复。这种"工程上的最终一致"心态,跟分布式系统里的 Saga 模式本质一样——大数据修改本质上就是一个长事务,长事务不该靠 DBMS 的事务机制硬扛,该靠应用层的状态机扛。

修法:大事务拆批 + 监控 + kill 预案

修法 1:必须按主键 / 索引拆批

// 错误: 一次性 UPDATE 全部
UPDATE user_activity SET region='ASIA'
WHERE country_code IN ('CN', 'JP', 'KR', ...);

// 正确: 按主键范围拆批, 每批 5000-10000 行
DECLARE @batch_size INT = 5000;
DECLARE @last_id BIGINT = 0;

WHILE 1=1 BEGIN
    UPDATE user_activity
    SET region = 'ASIA'
    WHERE country_code IN ('CN', 'JP', 'KR', ...)
      AND id > @last_id
      AND id <= @last_id + @batch_size;

    -- 看本次 affected 行数, 0 就是没了
    DECLARE @affected INT = ROW_COUNT();
    IF @affected = 0 BREAK;

    SET @last_id = @last_id + @batch_size;
    COMMIT;
    SLEEP 0.05;    -- 让从库消化, 避免延迟
END;

关键点:

• 按主键范围拆(不是 LIMIT + OFFSET,后者慢)
• 每批 5000-10000 行(经验值,小到 1000 也可,大到 50000 不建议)
• 每批独立 COMMIT,事务小,锁时间短,从库 binlog 也是小事务好消化
• 批间 sleep,让从库追上,避免延迟陡升

同样的 8000 万行更新,用这种拆批方式跑大约 4 小时(几乎一样),但整个过程从库延迟 < 5 秒,可以随时 kill 不需要漫长 rollback。本质区别:把"一个 4 小时的事务"变成"8000 个 1.8 秒的事务",任何时候 kill 损失的只是当前那批。

用 Python 脚本封装这种"断点续跑 + 限速 + 监控从库延迟"的批处理框架,长这样:

import time
import logging
import pymysql
from contextlib import contextmanager

logger = logging.getLogger(__name__)

class SafeBatchUpdater:
    """大表更新的安全批处理框架:断点续跑 + 限速 + 从库延迟自适应"""

    def __init__(self, conn_master, conn_replica, batch_size=5000,
                 sleep_ms=50, max_lag_seconds=10):
        self.master = conn_master
        self.replica = conn_replica
        self.batch_size = batch_size
        self.sleep_ms = sleep_ms
        self.max_lag = max_lag_seconds
        self.progress_table = "batch_progress"

    def _get_last_id(self, job_name: str) -> int:
        with self.master.cursor() as cur:
            cur.execute(
                f"SELECT last_id FROM {self.progress_table} WHERE job=%s",
                (job_name,),
            )
            row = cur.fetchone()
            return row[0] if row else 0

    def _save_progress(self, job_name: str, last_id: int):
        with self.master.cursor() as cur:
            cur.execute(
                f"INSERT INTO {self.progress_table}(job,last_id) VALUES(%s,%s) "
                "ON DUPLICATE KEY UPDATE last_id=VALUES(last_id)",
                (job_name, last_id),
            )
        self.master.commit()

    def _wait_replica_catchup(self):
        # 从库延迟过高时主动暂停,避免主从断裂
        while True:
            with self.replica.cursor() as cur:
                cur.execute("SHOW SLAVE STATUS")
                row = cur.fetchone()
                # Seconds_Behind_Master 字段位置随版本变,实际生产用 dict cursor 取
                lag = row[32] if row else 0
            if lag is None or lag <= self.max_lag:
                return
            logger.warning("replica lag %ds > %ds, sleep 5s", lag, self.max_lag)
            time.sleep(5)

    def run(self, job_name: str, sql_template: str, max_id: int):
        """sql_template 必须含 :start_id 和 :end_id 占位"""
        last_id = self._get_last_id(job_name)
        total_affected = 0
        rounds = 0
        t_start = time.monotonic()

        while last_id < max_id:
            end_id = last_id + self.batch_size
            with self.master.cursor() as cur:
                cur.execute(
                    sql_template,
                    {"start_id": last_id, "end_id": end_id},
                )
                affected = cur.rowcount
            self.master.commit()

            self._save_progress(job_name, end_id)
            total_affected += affected
            rounds += 1
            last_id = end_id

            if rounds % 50 == 0:
                logger.info(
                    "job=%s rounds=%d last_id=%d total_affected=%d elapsed=%.1fs",
                    job_name, rounds, last_id, total_affected,
                    time.monotonic() - t_start,
                )
                self._wait_replica_catchup()

            time.sleep(self.sleep_ms / 1000)

        logger.info("job=%s DONE rounds=%d total=%d", job_name, rounds, total_affected)
        return total_affected

# 用法
updater = SafeBatchUpdater(master_conn, replica_conn, batch_size=5000)
updater.run(
    job_name="update_region_asia_20260208",
    sql_template=(
        "UPDATE user_activity SET region='ASIA' "
        "WHERE country_code IN ('CN','JP','KR') "
        "AND id > %(start_id)s AND id <= %(end_id)s"
    ),
    max_id=420_000_000,
)

这个框架的几个隐性收益往往被低估:进度落表后任何时候 kill 进程都安全,重启从断点继续;从库延迟超阈值时自动暂停,避免主从断裂;rounds 计数 + 日志频次控制让监控同事能精确判断"任务还剩多久"。我们把这套框架沉淀为内部 pip 包 safe-batch,后续任何 UPDATE/DELETE 涉及 > 1 万行的脚本一律强制走它,绕过这层 review 不批。

修法 2:监控大事务

-- 每分钟跑一次, 发现长事务告警
SELECT
    trx_id,
    trx_started,
    NOW() - trx_started AS duration,
    trx_rows_modified,
    trx_rows_locked,
    LEFT(trx_query, 200) AS query
FROM information_schema.innodb_trx
WHERE trx_started < NOW() - INTERVAL 30 SECOND
ORDER BY trx_started ASC;

告警阈值:

• 事务跑 > 30 秒:P3 通知
• 事务跑 > 5 分钟:P2 警告 oncall
• 事务 rows_modified > 10 万:P2 警告(可能是大事务)
• 事务 rows_modified > 100 万:P1 紧急,oncall 立刻介入

修法 3:Kill 预案 — 不要轻易 kill 大事务

面对"已经在跑的大事务",标准 SOP:

1. 能等就等:让事务跑完,如果业务影响可接受。Commit 比 rollback 快
2. 评估业务影响 vs kill 代价:计算 rollback 预期时间(rows_modified 数 ÷ 6000 行/秒)
3. 如果 rollback 时间 > 让事务跑完的剩余时间,选等
4. 如果必须 kill(业务已经在崩),做好"长 rollback"预案,提前切到读从库降级模式
5. kill 之后,持续监控 rollback 进度,业务方告知预期恢复时间

这次事故 DBA 选 kill 的判断,事后看是错的——继续跑大约还需 30 分钟完成,kill 后 rollback 用了 90 分钟,反而更糟。这种"是否 kill"的决策权重,应该写进 SOP,不靠值班直觉。

这套阈值表是经验值,不是死定律。规模小的库可以放宽,规模大的核心库要更严。关键是把阈值写进监控,而不是依赖人盯——这次事故 22:00 到 02:30 整整 4 个半小时,DBA 同事其实在值班,但他没看到任何告警提示"主库正在跑一个 30 分钟+的事务",因为我们的告警阈值是"5 分钟",而 30 秒到 5 分钟之间是个监控盲区,大事务恰好在这个盲区里"正常"地积累了影响。事故后我们把通知阈值压到 30 秒,5 分钟改成 P1 强制叫醒,这一改让后续 6 个月里至少 4 次潜在大事务被早期截停。

顺带一个细节:监控这条 SQL SELECT * FROM information_schema.innodb_trx 本身是读 INFORMATION_SCHEMA 的轻量元数据,几乎零开销,即使每分钟跑也不影响主库;有些团队为了避免"打扰 DBMS"而选择间隔 5 分钟跑——这是过度谨慎,把监控反应时间砍到了不必要的程度。后来我跟 DBA 同事讨论清楚 information_schema 的实现机制后,把频率拉到每 30 秒一次,反应窗口从 5 分钟压到 30 秒,长事务能更早被发现。

修法 4:批处理任务必须经 review

所有"UPDATE / DELETE 涉及行数 > 1 万"的任务必须:

• 提前评估影响行数(EXPLAIN + COUNT)
• 必须用拆批模式实现,代码 review
• 在测试环境跑过完整任务,记录实际耗时
• 生产执行前通知 DBA + oncall + 业务
• 生产执行时实时盯监控,异常立刻停

立的《MySQL 大事务治理纪律》

• 禁止单事务影响行数 > 1 万。这是硬约束,不允许特例。
• 批处理必须按主键 / 索引范围拆批,每批 1000-10000 行,批间 sleep。
• 所有数据修改 SQL 必须经过 EXPLAIN,确认影响行数符合预期。
• 必须监控长事务,> 30 秒告警,> 5 分钟紧急,> 100 万行 P1。
• 慎用 KILL:大事务 kill 后 rollback 更慢,应该评估"等 vs kill"的代价。
• 从库延迟监控:延迟 > 30 秒告警,> 5 分钟 P1。延迟陡升通常是主库有大事务。
• 批处理脚本必须在测试环境完整跑过一遍,记录实际耗时和影响范围,生产执行前 review。
• 大批量数据修改优先用 pt-online-schema-change / gh-ost(在线 DDL 工具),它们天然就是拆批 + 触发器 + 增量,不会产生大事务。
• 新人 onboarding 必须过 InnoDB 事务课。所有新入职 backend / DBA 同学必须在前三个月内完整跑过一遍"在压测库里手写一条大事务,观察 undo / redo / binlog / 从库延迟变化"的实验,亲自看一遍数据,远胜于讲十遍 PPT。
• 批处理任务必须接入"进度落表"机制,任何时候 kill 都能续跑。不允许出现"必须从头跑"的脆弱脚本。

事故复盘里走错的 4 个判断

1. "凌晨低峰就不算事" — 我们事故任务安排在周日 22:00,默认低峰期"出问题影响小"。实际从库延迟 5 小时意味着周一早高峰从库还在追,影响完全没躲过低峰窗口。低峰时段对"短事务"成立,对"大事务"不成立——大事务的影响时间窗超出了"夜间"这个概念。
2. "DBA 监控会兜底" — 任务执行前没人通知 DBA "这个 SQL 影响 8000 万行",DBA 看到的只是"22:00 后慢 SQL 列表多了一条还没完",误以为是正常 batch。不能让 DBA 靠"事后发现"兜底,必须事前知情、事中盯监控、事后归档。
3. "kill 比让它跑完更快" — 这是最直接的判断错误。DBA 在 02:30 决定 kill 时没有量化"还需要多久能跑完 vs rollback 要多久"。事后看 trx_rows_modified 增长曲线,正向 UPDATE 还需 30 分钟就完了,kill 后 rollback 反而花了 90 分钟。这条数据应该 SOP 化:任何 kill 决策前必须算这道账。
4. "任务 review 流于形式" — 这次任务的代码 review 是 OK 的(SQL 逻辑没错、影响范围估算大致对),但缺一道"压测环境完整跑过一遍"的环节。压测库小一个数量级,本来应该提前暴露"4 小时太长"的问题,但因为我们的压测库只有 5000 万行(比生产小 8 倍),整个任务 30 分钟跑完——这个数字给了所有人一种"任务可控"的错觉。压测库的规模如果不接近生产,任何耗时类压测数据都不可信。

更深一层:大事务的 5 种典型出现场景

团队后续做"大事务扫雷"时,我们梳理了过去 3 年代码库里所有可能产生大事务的代码路径,归纳出 5 种模式,记下来防新人再踩:

1. 批量 UPDATE / DELETE(本次事故):"一条 SQL 改一片"。这是最常见、最容易出事的模式。
2. 循环里没 commit:for 循环里跑 10 万次 UPDATE,但忘了在循环内 commit,变成一个 10 万行的大事务。这个 bug 在 ORM(如 Hibernate / SQLAlchemy)的 session.flush vs commit 概念混淆时尤其常见。
3. 长事务 + 大量小修改:事务 BEGIN 后调了一堆外部 RPC(HTTP / RPC / Kafka),期间事务一直开着。RPC 慢或超时,事务时间从预期 100ms 涨到 30 秒,行锁持有时间相应放大,期间任何其他事务访问这些行都阻塞。
4. SELECT FOR UPDATE 锁范围过大:本来想锁几行,WHERE 条件不严谨锁住一片(比如忘加索引导致全表扫 + 全表锁)。这种锁不写 undo,但锁本身就是大事务的另一种形态。
5. DDL 隐性大事务:8.0 之前的某些 ALTER TABLE 操作会全表重建,期间整表锁;即使 8.0 的 INSTANT DDL 也有边界条件,大表上做错了一样卡几小时。这就是为什么 pt-online-schema-change / gh-ost 这类工具不可或缺。

把这 5 种模式整理成内部 wiki 之后,我们做了一次代码扫描,扫出来 47 处疑似大事务点位,逐个 review,改了其中 31 处。这次扫雷过程本身就是事故复盘最大的回报——比单点修一个 bug 价值高得多。事故的真正价值不是"修这一个 bug",是把同类问题在系统范围内一次性扫干净。

给读者的几条自查清单

1. 跑 SELECT trx_started, trx_rows_modified, LEFT(trx_query, 200) FROM information_schema.innodb_trx; 看你 MySQL 现在有没有"大事务在跑"。
2. 找你团队的"定期批处理脚本"(每天 / 每周 cron 跑的),看实现是不是"一条 UPDATE 改一堆行"。是的话立刻改成拆批。
3. 有没有"大事务长事务告警"?没有的话先建立,这是最基本的 MySQL 监控。
4. 看你 binlog 大小变化曲线,如果某个时刻陡增几个 GB,那时刻一定有大事务在跑——回溯日志找 SQL。
5. 从库延迟 Seconds_Behind_Master 监控有没有?没有的话,任何主从复制系统都该有这个监控。
6. 团队对"kill 大事务"的态度——如果是"出问题就 kill",改成"评估 rollback 代价再决定",写进 SOP。

这次事故让我对"事务"这个抽象有了更深的敬畏:它在小数据量时是免费的抽象,在大数据量时代价巨大,且代价不对称——commit 还能并行 + buffer pool 优化,rollback 单线程 + 磁盘密集。MySQL 的设计者们没办法做出"大事务零成本"的抽象,这是物理限制。

另一个心得:"原子性"的执念是大事务的根源。很多团队为了"要么全成或全不成"的纯净需求,写出"一条 SQL 改千万行"的代码,然后等着出事。实际工程经验:几乎所有"业务需求是原子"的场景,都可以重新设计成"幂等批处理 + 失败重试"——后者是工程上更健康的范式。下次有人说"这必须是一个事务",问他"为什么"——通常追问两层就发现可以拆。

事故落幕后的几周里,我反复思考一件事:这次事故的所有技术点位,InnoDB 的 rollback 机制、undo log 设计、行锁持有时间、binlog 单线程消化,都是 MySQL DBA 圈子里 15 年的"老知识"。但这些知识没在我们 backend 团队里沉淀。我们写代码的同事多数读过《高性能 MySQL》第三版,知道"避免大事务"这句话,但没人在写下"一条 UPDATE 改 8000 万行"的代码时把这句话联想到自己写的这行 SQL 上。这就是"宣言式知识"和"行为式知识"的差距——读到、知道、写得出 PPT,跟"在键盘上敲那一行时本能地拒绝它"是两回事。组织里要做的事不是"再讲一遍",是把知识嵌入到工具链(代码 review checklist、CI 静态扫描、压测脚手架)里,让人不需要"记得"也能被工具挡住。这次事故之后,我们在 CI 里加了一个 SQL 复杂度静态扫描,任何 UPDATE/DELETE 缺 LIMIT 或 WHERE 主键范围的代码直接挂 review block——这层硬墙比任何"宣言"都好使。

最后一句给所有 backend 同学:数据库不是免费的抽象,事务也不是。每一次 BEGIN 都是在跟 InnoDB 说"我接下来要持有锁、产生 undo、写 redo、扩 binlog,请你照单全收",当你说出的体量超过 InnoDB 的设计预期,它没办法拒绝你,只能慢慢被你拖垮。下次你写 BEGIN 之前,把这句话默念一遍。如果你正在 review 一条影响超过 1 万行的 SQL,把这段话甩给作者读一遍,大概率比任何空洞的"注意大事务"提醒都管用。

—— 别看了 · 2026