大事务的“事前预防”：监控、拦截、Kill，四层防线一次讲透

关键词：大事务；长事务；事前预防；pt-kill；监控告警；Undo Log

大家好，我是小耶，写功课只是为了我踩过的坑，你们别再踩了！

上周讲了“大事务导致从库延迟怎么查”，评论区有人问：“道理我都懂，但怎么在它发生之前就拦住？”

这个问题问到点子上了。上周那篇讲的是“出了问题怎么查”，今天这篇讲的是“怎么在出问题之前拦住它”。

大事务是数据库性能的“头号杀手”——从库延迟、锁等待、Undo Log膨胀，追根溯源往往就是一条SQL。但大多数团队的做法是“等它发生了再救火”：监控告警响了，DBA爬起来查INNODB_TRX，找到长事务，Kill掉，然后写复盘。救火救得好，不代表火不会再来。

今天从四个层面，把大事务的“事前预防”彻底拆开讲一遍。

一、先搞清楚：大事务是怎么进来的？

在谈预防之前，先搞清楚大事务的来源。根据生产环境经验，大事务通常来自三个渠道：

1. 应用层代码缺陷

@Transactional注解标注了一个方法，方法里调用了外部API。外部API超时了，事务一直没提交，锁一直持有——这种问题的根源是“事务边界划得太宽”。

2. 批量操作不当

业务方要清理历史数据，DBA写了一条DELETE FROM orders WHERE create_time < ‘2025-01-01’，一次性删了几十万行。主库跑了3分钟，从库延迟飙到5分钟——这种问题的根源是“没有分批”。

3. 运维误操作

运维人员执行了一个没有WHERE条件的UPDATE，或者索引失效导致全表扫描的DELETE——这种问题的根源是“操作前没有评估影响范围”。

核心认知：大事务的根因不在数据库层，在应用层和流程层。只靠数据库参数拦不住所有大事务。

二、识别：如何提前发现大事务的“苗头”？

预防的第一步是“能看见”。不能等大事务已经跑起来了才发现。

监控两个硬指标：

定位长事务的唯一可靠方式：

SELECT 
    trx_id,
    trx_state,
    trx_started,
    TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS duration_sec,
    trx_mysql_thread_id,
    trx_query,
    trx_rows_locked,
    trx_rows_modified
FROM information_schema.INNODB_TRX
WHERE trx_state = 'RUNNING'
  AND TIMESTAMPDIFF(SECOND, trx_started, NOW()) > 10
ORDER BY trx_started;

⚠️ 注意：SHOW PROCESSLIST的Time字段只统计当前语句执行时长，不是事务总时长。一个事务可能已经跑了5分钟，但当前执行的SQL只跑了0.1秒，SHOW PROCESSLIST看不出来。定位长事务必须查INNODB_TRX。

监控必须固化成自动告警，不能靠人工巡检。建议设置两个告警规则：

• 事务持续时间超过10秒 → 告警（可观察）

• 事务持续时间超过30秒 → 告警（需处理）

三、应用层拦截：把大事务堵在门外

这是最根本的防线——不让大事务进入数据库。

1. 代码Review的检查点

在Code Review阶段，针对数据库操作设置明确的检查规则：

• 事务方法中不能调用外部API、RPC、HTTP请求

• 事务方法中不能执行循环内的数据库操作

• 事务方法中不能有用户交互等待

2. 设置执行超时

MySQL 5.7.8+支持MAX_EXECUTION_TIME提示，限制单条SQL的执行时间：

SELECT /*+ MAX_EXECUTION_TIME(30000) */ * FROM orders WHERE create_time < '2025-01-01';

超过30秒自动中断。注意这个提示只限制查询的执行时间，对已执行但未提交的事务无效。

3. 事务边界设计原则

• 事务要“短、平、快”——尽量缩短事务持有锁的时间

• 批量操作必须分批，每批1000-5000行，每批之间加COMMIT

• 查询类操作不需要事务，用@Transactional(readOnly=true)明确标记

四、数据库层限制：让数据库“拒绝”大事务

应用层拦不住的时候，数据库层要能兜底。

1. 参数限制

注意：max_execution_time对存储过程和触发器的SQL无效。innodb_lock_wait_timeout只能控制等待锁的时间，不能控制事务本身的执行时间。

2. 自动Kill工具：pt-kill

Percona Toolkit的pt-kill是生产环境最常用的长事务自动清理工具。它可以自动Kill超过指定时间的查询或连接。

配置示例：

pt-kill \
  --host=127.0.0.1 \
  --user=root \
  --password=secret \
  --busy-time=60 \          # 执行超过60秒的查询
  --interval=10 \            # 每10秒检查一次
  --kill-query \             # 只Kill查询，不断开连接
  --print                    # 先打印匹配的查询，不实际Kill

生产环境部署建议：

1. 先用--dry-run --print观察10分钟，确认匹配逻辑无误

2. 再启用--kill或--kill-query

3. 将查杀结果记录在日志中，定期推送被Kill的SQL给相关人员

五、实战案例：一次大事务的“事前拦截”

某电商平台的订单表有5000万行数据，业务方需要清理3年前的已关闭订单。开发同学准备直接执行：

DELETE FROM orders WHERE status = 'CLOSED' AND create_time < '2023-01-01';

事前评估发现的问题：

• 该表有5个二级索引，删除50万行需要维护250万个索引条目

• 单次DELETE会产生大量Undo Log，从库延迟风险极高

• 锁持有时间预计超过3分钟

事前拦截方案：

1. Code Review阶段发现该SQL没有分批，直接打回

2. 要求改为分批删除，每批1000行，每批间隔0.5秒

3. 配置pt-kill守护进程，防止意外长事务漏网

效果：分批删除在业务低峰期执行，从库延迟控制在5秒以内，用户无感知。

六、总结

大事务的“事前预防”需要四层防线协同工作：

上周讲的“事后排查”是DBA的基本功，今天讲的“事前预防”是DBA的进阶能力。前者让你能在故障后快速恢复，后者让你根本不用半夜爬起来。

小耶在手，SQL 不愁

还有什么想了解的，欢迎留言！小耶一定知无不言言无不尽……我们下次见~