解决SQL更新时内存压力导致事务自动回滚

很多人一见到事务被自动回滚，第一反应就是“内存溢出”。其实大概率不是这么回事——MySQL 本身并不会因为“内存不足”就直接丢一个 ROLLBACK 出来。所谓的“自动回滚”，绝大多数时候是上层应用（比如 Java Spring、PHP 脚本）捕获到具体错误后主动触发的，或者是 mysqld 进程被 Linux 的 OOM Killer 直接杀掉，导致未提交的事务丢失。真正需要盯的，是错误日志里有没有 Out of sort memory、Cannot allocate memory 或者 Killed 这些明确线索。

查错先看日志，别猜配置

翻开 MySQL 的错误日志（一般藏在 /var/log/mysql/error.log 或 mysqld.err），重点搜这几个关键词：

• Out of sort memory → 指向 sort_buffer_size 不足，别急着调参，先优化索引
• Killed（单独一行，没有堆栈）→ 大概率是 Linux OOM Killer 干的，附赠一条命令 dmesg -T | grep -i "killed process"
• Lock wait timeout exceeded → 锁超时，和内存半毛钱关系没有，查阻塞源头
• Unknown error 或静默退出 → 检查是不是误开了 innodb_force_recovery（值 > 0 会跳过 undo 解析，回滚直接废掉）

sort_buffer_size 调多大才算安全？

sort_buffer_size 是每个连接独占的内存，设太大反而容易引发系统级 OOM。调参前必须满足三个条件，缺一个就别动：

• EXPLAIN 显示该 SQL 已经走索引（type 为 ref/range），且 Extra 列不含 Using filesort
• 扫描行数（rows）在 5 万以内，但排序结果集仍然偏大（比如要取 TOP 1000）
• 活跃连接数可控（例如稳定在 50 以内），避免总内存占用突破物理限制

建议从 512K 开始试，逐步加到 2M；一旦超过 4M 就该警惕了——这时候更应该检查是否漏建覆盖索引，而不是继续堆内存。

真遇到大事务回滚卡死，别 KILL，先降温

正在回滚的大事务（尤其是涉及百万级更新的那种），KILL 不仅不能加速，还会让 InnoDB 在后台默默继续清理，同时阻塞新连接。稳妥的做法是：

• 从 INNODB_TRX 查出 trx_mysql_thread_id，执行 KILL 后观察 INNODB_TRX.trx_state 是否变为 ROLLING BACK，确认它确实在动
• 临时降低并发压力：把 innodb_buffer_pool_instances 设为 CPU 核心数（比如 8），减少内部争用
• 允许后台异步清理：对已知要回滚的事务，提前 KILL，InnoDB 会在空闲时分批处理，不阻塞前台
• 禁用事务中任何耗内存操作：SLEEP()、SELECT ... INTO OUTFILE、大结果集 GROUP BY —— 它们会挤占 undo 页缓存，拖慢回滚本身

最容易被跳过的一步：重启前务必确认 innodb_force_recovery 是否还留在配置文件里。哪怕只开过一次 =3，没清掉就重启，undo log 就废了，回滚直接失效。