Hikari连接池连接无法归还问题排查求助

背景与问题现象 #

我们的数据库层基于JdbcTemplate实现，运行一段时间后Hikari连接池出现连接耗尽问题：

• Hikari日志显示：HikariPool-1 - Timeout failure stats (total=100, active=100, idle=0, waiting=2)
• 大量连接泄漏检测日志，最终出现连接超时：HikariPool-1 - Connection is not available, request timed out after 30000ms
• DBA确认最长查询仅约1秒，排除慢查询导致的连接占用。

初始实现代码 #

DAO层核心代码 #

@Override
@Transactional
protected SomeObj insert(SomeObj obj) {
    this.getNamedParameterJdbcTemplate().update(INSERT_QUERY, MyJdbcUtils.prepareQueryParams(obj));
    synchronized (obj.getSomeList()) { 
        decidable.getReasons().forEach(someItem -> { 
            if (someItem != null) getJdbcTemplate().update(INSERT_ITEM_QUERY, obj.getId(), someItem ); 
        }); 
    }
    return obj;
}

@Override
@Transactional
public SomeObj update(SomeObj obj) {
    this.getNamedParameterJdbcTemplate().update(UPDATE_QUERY, MyJdbcUtils.prepareQueryParams(obj));
    synchronized (obj.getSomeList()) { 
        decidable.getReasons().forEach(someItem -> { 
            if (someItem != null) getJdbcTemplate().update(UPDATE_ITEM_QUERY, obj.getId(), someItem ); 
        }); 
    }
    return obj;
}

@Override
public SomeObj upsert(SomeObj obj) {
    try {
        return this.insert(obj);
    } catch (DuplicateKeyException exception) {
        final SomeObj existingObj = this.find(obj.getSomeId()).get(0);
        // 从已有对象填充部分字段
    }
    return this.update(transaction);
}

MyJdbcUtils工具类 #

public static Map<String, ?> prepareQueryParams(SomeObj obj) {
    return new HashMap<String, Object>() {{
        put("someColumn", obj.getSomeColumn());
        // 其他字段映射
    }};
}

数据库连接池配置（application.properties） #

spring.datasource.jdbc-url=...
spring.datasource.username=...
spring.datasource.password=...
spring.datasource.initialization-mode=never
spring.datasource.driver-class-name=org.postgresql.Driver
spring.datasource.leakDetectionThreshold=5000
spring.datasource.maximumPoolSize=100
spring.datasource.minimumIdle=20
spring.datasource.idleTimeout=30000

泄漏检测堆栈日志 #

Connection leak detection triggered for org.postgresql.jdbc.PgConnection@17b19afa on thread http-nio-8080-exec-100, stack trace follows

java.lang.Exception: Apparent connection leak detected
    at com.zaxxer.hikari.HikariDataSource.getConnection(HikariDataSource.java:128) ~[HikariCP-3.4.1.jar!/:?]
    at org.hibernate.engine.jdbc.connections.internal.DatasourceConnectionProviderImpl.getConnection(DatasourceConnectionProviderImpl.java:122) ~[hibernate-core-5.4.6.Final.jar!/:5.4.6.Final]
    at org.hibernate.internal.NonContextualJdbcConnectionAccess.obtainConnection(NonContextualJdbcConnectionAccess.java:38) ~[hibernate-core-5.4.6.Final.jar!/:5.4.6.Final]
    at org.hibernate.resource.jdbc.internal.LogicalConnectionManagedImpl.acquireConnectionIfNeeded(LogicalConnectionManagedImpl.java:104) ~[hibernate-core-5.4.6.Final.jar!/:5.4.6.Final]
    at org.hibernate.resource.jdbc.internal.LogicalConnectionManagedImpl.getPhysicalConnection(LogicalConnectionManagedImpl.java:134) ~[hibernate-core-5.4.6.Final.jar!/:5.4.6.Final]
    at org.hibernate.resource.jdbc.internal.LogicalConnectionManagedImpl.getConnectionForTransactionManagement(LogicalConnectionManagedImpl.java:250) ~[hibernate-core-5.4.6.Final.jar!/:5.4.6.Final]
    at org.hibernate.resource.jdbc.internal.LogicalConnectionManagedImpl.begin(LogicalConnectionManagedImpl.java:258) ~[hibernate-core-5.4.6.Final.jar!/:5.4.6.Final]
    at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl$TransactionDriverControlImpl.begin(JdbcResourceLocalTransactionCoordinatorImpl.java:246) ~[hibernate-core-5.4.6.Final.jar!/:5.4.6.Final]
    at org.hibernate.engine.transaction.internal.TransactionImpl.begin(TransactionImpl.java:83) ~[hibernate-core-5.4.6.Final.jar!/:5.4.6.Final]
    at org.springframework.orm.jpa.vendor.HibernateJpaDialect.beginTransaction(HibernateJpaDialect.java:184) ~[spring-orm-5.2.0.RELEASE.jar!/:5.2.0.RELEASE]
    at org.springframework.orm.jpa.JpaTransactionManager.doBegin(JpaTransactionManager.java:402) ~[spring-orm-5.2.0.RELEASE.jar!/:5.2.0.RELEASE]
    at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:747) ~[spring-aop-5.2.0.RELEASE.jar!/:5.2.0.RELEASE]
    at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:689) ~[spring-aop-5.2.0.RELEASE.jar!/:5.2.0.RELEASE]
    at com.company.project.core.domain.transaction.JdbcTransactionDaoImpl$$EnhancerBySpringCGLIB$$87fab16e.update(<generated>) ~[classes!/:?]
    at com.company.project.core.integration.spring.TransactionIntegrationConfiguration.asyncPersistenceService(TransactionIntegrationConfiguration.java:208) ~[classes!/:?]
    at sun.reflect.GeneratedMethodAccessor326.invoke(Unknown Source) ~[?:?]
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:1.8.0_212]
    at java.lang.reflect.Method.invoke(Method.java:498) ~[?:1.8.0_212]
    at org.springframework.messaging.handler.invocation.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:171) ~[spring-messaging-5.2.0.RELEASE.jar!/:5.2.0.RELEASE]
    at org.springframework.messaging.handler.invocation.InvocableHandlerMethod.invoke(InvocableHandlerMethod.java:120) ~[spring-messaging-5.2.0.RELEASE.jar!/:5.2.0.RELEASE]
    at org.springframework.integration.gateway.GatewayProxyFactoryBean.invokeGatewayMethod(GatewayProxyFactoryBean.java:508) ~[spring-integration-core-5.2.0.RELEASE.jar!/:5.2.0.RELEASE]
    at org.springframework.integration.gateway.GatewayProxyFactoryBean.doInvoke(GatewayProxyFactoryBean.java:478) ~[spring-integration-core-5.2.0.RELEASE.jar!/:5.2.0.RELEASE]
    at org.springframework.integration.gateway.GatewayProxyFactoryBean$Invoker.get(GatewayProxyFactoryBean.java:1012) ~[spring-integration-core-5.2.0.RELEASE.jar!/:5.2.0.RELEASE]
    at java.util.concurrent.FutureTask.run(FutureTask.java:266) ~[?:1.8.0_212]
    at java.util.concurrent.AbstractExecutorService.submit(AbstractExecutorService.java:134) ~[?:1.8.0_212]
    at org.springframework.scheduling.concurrent.ThreadPoolTaskExecutor.submit(ThreadPoolTaskExecutor.java:341) ~[spring-context-5.2.0.RELEASE.jar!/:5.2.0.RELEASE]
    at org.springframework.integration.gateway.GatewayProxyFactoryBean.invoke(GatewayProxyFactoryBean.java:451) ~[spring-integration-core-5.2.0.RELEASE.jar!/:5.2.0.RELEASE]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.2.0.RELEASE.jar!/:5.2.0.RELEASE]
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:212) ~[spring-aop-5.2.0.RELEASE.jar!/:5.2.0.RELEASE]
    at com.sun.proxy.$Proxy181.processTransaction(Unknown Source) ~[?:?]
    at com.company.project.core.services.transaction.TransactionServiceImpl.async(TransactionServiceImpl.java:101) ~[classes!/:?]
    at com.company.project.core.services.transaction.TransactionServiceImpl$$FastClassBySpringCGLIB$$73b9cf27.invoke(<generated>) ~[classes!/:?]
    at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218) ~[spring-core-5.2.0.RELEASE.jar!/:5.2.0.RELEASE]
    at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:769) ~[spring-aop-5.2.0.RELEASE.jar!/:5.2.0.RELEASE]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163) ~[spring-aop-5.2.0.RELEASE.jar!/:5.2.0.RELEASE]
    at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:747) ~[spring-aop-5.2.0.RELEASE.jar!/:5.2.0.RELEASE]
    at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:93) ~[spring-aop-5.2.0.RELEASE.jar!/:5.2.0.RELEASE]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.2.0.RELEASE.jar!/:5.2.0.RELEASE]
    at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:747) ~[spring-aop-5.2.0.RELEASE.jar!/:5.2.0.RELEASE]
    at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:689) ~[spring-aop-5.2.0.RELEASE.jar!/:5.2.0.RELEASE]
    at com.company.project.core.services.transaction.TransactionServiceImpl$$EnhancerBySpringCGLIB$$eae305f2.async(<generated>) ~[classes!/:?]
    at com.company.project.core.controllers.TransactionController.async(TransactionController.java:52) ~[classes!/:?]
    at sun.reflect.GeneratedMethodAccessor323.invoke(Unknown Source) ~[?:?]
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:1.8.0_212]
    at java.lang.reflect.Method.invoke(Method.java:498) ~[?:1.8.0_212]
    at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:190) ~[spring-web-5.2.0.RELEASE.jar!/:5.2.0.RELEASE]
    at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:138) ~[spring-web-5.2.0.RELEASE.jar!/:5.2.0.RELEASE]
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119) ~[spring-web-5.2.0.RELEASE.jar!/:5.2.0.RELEASE]
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) [?:1.8.0_212]
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) [?:1.8.0_212]
    at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61) ~[tomcat-embed-core-9.0.27.jar!/:9.0.27]
    at java.lang.Thread.run(Thread.java:748) [?:1.8.0_212]

优化尝试（未解决问题） #

改用TransactionTemplate管理事务，但连接泄漏问题依然存在：

private final TransactionTemplate transactionTemplate;

@Autowired
public MyDao (PlatformTransactionManager transactionManager, DataSource dataSource) {
    setDataSource(dataSource); // 类继承自NamedParameterJdbcDaoSupport
    this.transactionTemplate = new TransactionTemplate(transactionManager);
}

@Override
protected SomeObj insert(SomeObj obj) {
    return this.transactionTemplate.execute(status -> {
        this.getNamedParameterJdbcTemplate().update(INSERT_QUERY, MyJdbcUtils.prepareQueryParams(obj));
        for (String someItem : obj.getSomeList()) {
            if (someItem != null) {
                getJdbcTemplate().update(INSERT_ITEM_QUERY, obj.getId(), someItem );
            }
        }
        return obj;
    });        
}

@Override
public SomeObj update(SomeObj obj) {
    return this.transactionTemplate.execute(status -> {
        this.getNamedParameterJdbcTemplate().update(UPDATE_QUERY, MyJdbcUtils.prepareQueryParams(obj));
        for (String someItem : obj.getSomeList()) {
            if (someItem != null) {
                getJdbcTemplate().update(UPDATE_ITEM_QUERY, obj.getId(), someItem ); 
            }
        }
        return obj;
    });
}

问题分析与解决方案 #

核心原因 #

从堆栈日志可以看到，事务管理实际使用的是JpaTransactionManager，但DAO是基于JdbcTemplate实现的，同时业务逻辑在异步线程中执行（ThreadPoolTaskExecutor.submit）。

关键问题点：

1. 事务上下文跨线程丢失：Spring的@Transactional和TransactionTemplate默认依赖线程绑定的事务上下文（ThreadLocal），异步执行时原线程的事务上下文不会自动传递到新线程，导致新线程重新创建事务但资源无法正确释放。
2. 混合使用JPA事务管理器与JdbcTemplate：JpaTransactionManager为JPA设计，兼容JdbcTemplate时在异步场景下资源释放逻辑异常，导致连接无法归还到池。
3. upsert方法无事务边界：upsert未加事务注解，调用insert和update时会开启独立事务，额外占用连接。

解决方案 #

1. 替换为正确的事务管理器：改用DataSourceTransactionManager专门管理JdbcTemplate事务，避免JPA事务管理器的适配问题：

   @Bean
   public PlatformTransactionManager transactionManager(DataSource dataSource) {
       return new DataSourceTransactionManager(dataSource);
   }
   

2. 异步线程传递事务上下文：如果必须异步执行，配置支持事务传递的线程池，手动在异步任务中管理事务：

   @Bean
   public TaskExecutor asyncTaskExecutor(PlatformTransactionManager transactionManager) {
       ThreadPoolTaskExecutor executor = new ThreadPoolTaskExecutor();
       executor.setCorePoolSize(20);
       executor.setMaxPoolSize(100);
       executor.setQueueCapacity(500);
       executor.setThreadNamePrefix("async-task-");
       // 装饰任务，传递事务上下文
       executor.setTaskDecorator(runnable -> {
           TransactionStatus status = transactionManager.getTransaction(new DefaultTransactionDefinition());
           return () -> {
               try {
                   runnable.run();
                   transactionManager.commit(status);
               } catch (Exception e) {
                   transactionManager.rollback(status);
                   throw e;
               }
           };
       });
       executor.initialize();
       return executor;
   }
   

3. 给upsert添加事务边界：确保整个upsert过程在单个事务内，减少连接占用：

   @Override
   @Transactional
   public SomeObj upsert(SomeObj obj) {
       try {
           return this.insert(obj);
       } catch (DuplicateKeyException exception) {
           final SomeObj existingObj = this.find(obj.getSomeId()).get(0);
           // 从已有对象填充部分字段
       }
       return this.update(obj);
   }
   

4. 批量操作优化：将循环插入/更新改为批量操作，减少连接的频繁获取释放：

   // 批量插入示例
   getJdbcTemplate().batchUpdate(INSERT_ITEM_QUERY, 
       new BatchPreparedStatementSetter() {
           @Override
           public void setValues(PreparedStatement ps, int i) throws SQLException {
               String item = obj.getSomeList().get(i);
               ps.setLong(1, obj.getId());
               ps.setString(2, item);
           }
           @Override
           public int getBatchSize() {
               return obj.getSomeList().size();
           }
       });