使用Spring TransactionTemplate执行长时删除时连接断开问题排查

结合你的配置和代码来看，连接断开的核心原因是长事务触发了连接池的遗弃连接回收，同时可能伴随MySQL层面的超时限制，再加上批量删除效率过低导致耗时过长放大了问题。咱们一步步来解决：

1. 修复DBCP2连接池的遗弃连接配置 #

你当前设置的removeAbandonedTimeout=420（仅7分钟），远短于你的30分钟批量操作时间。连接池会认为这个长时间占用的连接是"遗弃"状态，主动回收它，直接导致连接断开错误。

解决方案： #

调整遗弃连接超时时间，设置为比你的预期最长事务时间更长的值，比如30分钟（1800秒）：

ds.setRemoveAbandonedTimeout(1800);

如果你的业务偶尔会有更长的操作，也可以设置为40分钟（2400秒）。另外，removeAbandonedOnBorrow和removeAbandonedOnMaintenance可以保留开启，但必须确保超时时间足够覆盖你的长事务。

2. 检查并调整MySQL的连接超时参数 #

MySQL默认的wait_timeout和interactive_timeout是8小时，但如果你的数据库被修改为15分钟（900秒），那么即使连接池没问题，MySQL也会主动断开长时间空闲的连接（注：如果你的删除操作一直在执行，这个可能不是主因，但还是要排查）。

解决方案： #

• 登录MySQL执行以下命令查看当前值：

SHOW VARIABLES LIKE '%timeout%';

• 如果wait_timeout或interactive_timeout是900秒，修改配置文件（my.cnf/my.ini）：

wait_timeout=3600
interactive_timeout=3600

• 重启MySQL服务，或者临时在运行时修改（重启后失效）：

SET GLOBAL wait_timeout=3600;
SET GLOBAL interactive_timeout=3600;

3. 优化批量删除的效率（根本解决耗时问题） #

10万+数据一次性删除，不管是Spring Data JPA的deleteByJob还是原生SQL，都可能因为全表扫描、锁表等问题导致耗时极长。分批次删除是解决这类问题的最优方案。

解决方案： #

3.1 分批次删除

修改你的Repository方法，支持批量删除指定数量的记录，循环执行直到删除完毕：

// 在RuleViolationRepo中添加批量删除方法
@Modifying
@Query("DELETE FROM RuleViolation rv WHERE rv.job = :job LIMIT 1000")
int deleteByJobBatch(@Param("job") AnalysisJob job);

然后在事务中循环调用：

// 替换原来的ruleViolationRepo.deleteByJob(analysisJob);
int deletedCount;
do {
    deletedCount = ruleViolationRepo.deleteByJobBatch(analysisJob);
} while (deletedCount > 0);

其他表的删除操作也用同样的方式处理，每次删除1000-5000条（根据你的数据库性能调整），这样单批次操作时间短，不会触发任何超时限制。

3.2 添加索引优化删除速度

确保你的业务表（rule_violation、rule_log、risk_violation等）在job或job_id字段上创建了索引，比如：

CREATE INDEX idx_rule_violation_job ON rule_violation(job_id);

没有索引的话，delete语句会进行全表扫描，速度慢到离谱，这也是导致你耗时超30分钟的核心原因之一。

4. 验证事务和查询超时配置 #

• 你设置的transactionTemplate.setTimeout(3000)是3000秒（50分钟），这个时间足够覆盖你的操作，但要确保DataSourceTransactionManager没有设置更短的默认超时。
• 连接池的setDefaultQueryTimeout(2700)是45分钟，也足够，但如果要更稳妥，可以设置为3600秒（1小时）：

ds.setDefaultQueryTimeout(3600);

总结 #

按优先级执行：

1. 先调整连接池的removeAbandonedTimeout，解决当前连接断开的紧急问题；
2. 给相关表添加索引，大幅提升删除速度；
3. 改成分批次删除，彻底避免长事务超时；
4. 检查MySQL的超时参数，确保环境配置无问题。

内容的提问来源于stack exchange，提问作者user2816525