Spring Batch任务耗时超1小时触发PostgreSQL连接超时异常求助

我们有一个Spring Batch批处理任务，与PostgreSQL数据库交互时出现超时问题：任务耗时20或40分钟时运行正常，但耗时超过1小时时，服务抛出异常。

代码片段 #

FileListener 类 #

public class FileListener {

    @Autowired
    private ApplicationContext applicationContext;

    @Autowired
    private JobLauncher jobLauncher;

    public void onEvent(Event event) {
        BatchJobEvent batchJobEvent = buildBatchJobEvent(event);
        BatchUtil.launchJob(batchJobEvent, applicationContext, jobLauncher);

    }
}

BatchUtil 类 #

public class BatchUtil {

    public static void launchJob(BatchJobEvent batchJobEvent, ApplicationContext applicationContext,
            JobLauncher jobLauncher) {
        Job job = (Job) applicationContext.getBean(Utils.getValue(batchJobEvent.getJobName()));
        JobParametersBuilder jobParametersBuilder = new JobParametersBuilder();

        JobParameters jobParameters = jobParametersBuilder.toJobParameters();
        try {
            log.info("foobar: Trying to launch Job [{}], with parameters [{}]", job.getName(), jobParameters.toString());
            jobLauncher.run(job, jobParameters);
        }
    }
}

异常栈信息 #

{"@timestamp":"2023-03-06T16:59:01.366+00:00","@version":1,"message":"Error while extracting database name - falling back to empty error codes","logger_name":"org.springframework.jdbc.support.SQLErrorCodesFactory","thread_name":"pool-10-thread-3","level":"WARN","level_value":30000,"stack_trace":"org.springframework.jdbc.support.MetaDataAccessException: Error while extracting DatabaseMetaData; nested exception is java.sql.SQLException: Connection is closed\n\tat org.springframework.jdbc.support.JdbcUtils.extractDatabaseMetaData(JdbcUtils.java:330)\n\tat org.springframework.jdbc.support.JdbcUtils.extractDatabaseMetaData(JdbcUtils.java:355)\n\tat org.springframework.batch.core.repository.dao.JdbcExecutionContextDao.persistSerializedContext(JdbcExecutionContextDao.java:233)\n\tat org.springframework.batch.core.repository.dao.JdbcExecutionContextDao.updateExecutionContext(JdbcExecutionContextDao.java:161)\n\tat org.springframework.batch.core.repository.support.SimpleJobRepository.updateExecutionContext(SimpleJobRepository.java:209)\n\tat org.springframework.batch.core.step.tasklet.TaskletStep$ChunkTransactionCallback.doInTransaction(TaskletStep.java:451)\n\tat org.springframework.batch.core.step.tasklet.TaskletStep$ChunkTransactionCallback.doInTransaction(TaskletStep.java:330)\n\tat org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:140)\n\tat org.springframework.batch.core.step.tasklet.TaskletStep$2.doInChunkContext(TaskletStep.java:272)\n\tat org.springframework.batch.core.scope.context.StepContextRepeatCallback.doInIteration(StepContextRepeatCallback.java:81)\n\tat org.springframework.batch.repeat.support.RepeatTemplate.getNextResult(RepeatTemplate.java:375)\n\tat org.springframework.batch.repeat.support.RepeatTemplate.executeInternal(RepeatTemplate.java:215)\n\tat java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)\n\tat java.util.concurrent.FutureTask.run(FutureTask.java:266)\n\tat java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)\n\tat java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)\n\tat java.lang.Thread.run(Thread.java:748)\nCaused by: java.sql.SQLException: Connection is closed\n\tat com.zaxxer.hikari.pool.ProxyConnection$ClosedConnection.lambda$getClosedConnection$0(ProxyConnection.java:490)\n\tat com.sun.proxy.$Proxy163.getMetaData(Unknown Source)\n\tat com.zaxxer.hikari.pool.ProxyConnection.getMetaData(ProxyConnection.java:361)\n\tat com.zaxxer.hikari.pool.HikariProxyConnection.getMetaData(HikariProxyConnection.java)\n\tat org.springframework.batch.repeat.support.RepeatTemplate.iterate(RepeatTemplate.java:145)\n\tat org.springframework.batch.core.step.tasklet.TaskletStep.doExecute(TaskletStep.java:257)\n\tat org.springframework.batch.core.step.AbstractStep.execute(AbstractStep.java:200)\n\tat org.springframework.batch.core.job.SimpleStepHandler.handleStep(SimpleStepHandler.java:148)\n\tat org.springframework.batch.core.job.AbstractJob.handleStep(AbstractJob.java:394)\n\tat org.springframework.batch.core.job.SimpleJob.doExecute(SimpleJob.java:135)\n\tat org.springframework.batch.core.job.AbstractJob.execute(AbstractJob.java:308)\n\tat org.springframework.batch.core.launch.support.SimpleJobLauncher$1.run(SimpleJobLauncher.java:141)\n\tat org.springframework.core.task.SyncTaskExecutor.execute(SyncTaskExecutor.java:50)\n\tat java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)\n\tat java.util.concurrent.FutureTask.run(FutureTask.java:266)\n\tat java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)\n\tat java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)\n\tat java.lang.Thread.run(Thread.java:748)\nCaused by: org.postgresql.util.PSQLException: An I/O error occurred while sending to the backend.\n\tat org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:333)\n\tat org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:441)\n\tat org.postgresql.jdbc.PgStatement.execute(PgStatement.java:365)\n\tat org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:155)\n\tat org.postgresql.jdbc.PgPreparedStatement.executeUpdate(PgPreparedStatement.java:132)\n\tat com.zaxxer.hikari.pool.ProxyPreparedStatement.executeUpdate(ProxyPreparedStatement.java:61)\n\tat com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeUpdate(HikariProxyPreparedStatement.java)\n\tat org.springframework.jdbc.core.JdbcTemplate.lambda$update$0(JdbcTemplate.java:855)\n\tat org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:605)\n\t... 69 common frames omitted\nCaused by: java.io.EOFException: null\n\tat org.postgresql.core.PGStream.receiveChar(PGStream.java:295)\n\tat org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1947)\n\tat org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:306)\n\t... 77 common frames omitted"}

Hikari 连接池配置 #

hikari:
      connection-init-sql: SELECT 1
      connection-test-query: SELECT 1
      connection-timeout: 30000
      idle-timeout: 30000
      maximum-pool-size: 20
      minimum-idle: 1
      pool-name: hikari
      validation-timeout: 300000

排查与解决方案 #

1. 核心问题定位 #

从异常栈可判断，本质是数据库连接被提前关闭：任务运行超过1小时后，PostgreSQL端或中间件（防火墙/负载均衡）主动断开了连接，但Hikari连接池未检测到连接失效，仍将其分配给Spring Batch任务，导致更新执行上下文时抛出连接关闭异常。

2. 针对性配置调整 #

（1）适配Hikari连接池与数据库超时

确保Hikari的连接回收逻辑优先于远端断开动作，同时开启连接有效性检测：

hikari:
      connection-init-sql: SELECT 1
      # 调整空闲超时为55分钟（小于1小时，避免远端先断开）
      idle-timeout: 3300000
      maximum-pool-size: 20
      minimum-idle: 1
      pool-name: hikari
      validation-timeout: 300000
      # 开启连接泄漏检测，超时1分钟告警
      leak-detection-threshold: 60000
      # 移除connection-test-query，PostgreSQL 9.4+支持JDBC4的isValid()方法，性能更优
      validation-interval: 300000 # 每5分钟验证一次连接有效性

（2）优化Spring Batch事务配置

避免长任务持有连接过久，调整事务超时并确保Chunk级事务及时释放连接：

@Bean
public Step dataProcessStep(ItemReader<?> reader, ItemProcessor<?, ?> processor, ItemWriter<?> writer, PlatformTransactionManager transactionManager) {
    return stepBuilderFactory.get("dataProcessStep")
            .<Input, Output>chunk(1000)
            .reader(reader)
            .processor(processor)
            .writer(writer)
            .transactionManager(transactionManager)
            // 设置事务超时为2小时，覆盖默认短超时
            .transactionAttribute(new DefaultTransactionAttribute() {{
                setTimeout(7200);
            }})
            .build();
}

（3）检查并调整PostgreSQL端超时

登录PostgreSQL执行以下命令，确保数据库端超时设置适配任务时长：

-- 查看当前空闲事务超时设置
SHOW idle_in_transaction_session_timeout;
-- 查看TCP保活配置
SHOW tcp_keepalives_idle;
SHOW tcp_keepalives_interval;

-- 若需要，设置空闲事务超时为1.5小时（单位：毫秒）
SET idle_in_transaction_session_timeout = '5400000';

3. 额外优化建议 #

• 拆分长任务：将大文件拆分为多个小文件，降低单任务运行时长，减少连接持有时间。
• 启用异步JobLauncher：替换默认同步执行器，避免主线程阻塞并优化线程资源利用：

@Bean
public JobLauncher asyncJobLauncher(JobRepository jobRepository) {
    SimpleJobLauncher jobLauncher = new SimpleJobLauncher();
    jobLauncher.setJobRepository(jobRepository);
    jobLauncher.setTaskExecutor(new SimpleAsyncTaskExecutor("batch-executor-"));
    return jobLauncher;
}

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