I'm experiencing connection leaks with my spring data jdbc postgres app. Basically for my calls that are not called in a transaction the connection is acquired and released to the pool. However when it comes to transactional calls connections are not released and a connection leaks error comes up and points to the function that is annotated with @transactional. Also the most interesting thing is the pool statistics dont reflect this. they show all connections as idle even when there are unreleased connections.
@Configuration
@EnableJdbcRepositories("com.api.repositories")
public class DBConfig {
@Autowired
private DbProps dbProps;
@Bean
public DataSource readWriteDataSource() {
LOGGER.info("DB Props: {}",dbProps.toString());
PGSimpleDataSource dataSource = new PGSimpleDataSource();
dataSource.setUrl(dbProps.getDbUrl());
dataSource.setUser(dbProps.getDbUser());
dataSource.setPassword(dbProps.getDbPassword());
dataSource.setLogUnclosedConnections(true);
return connectionPoolDataSource(dataSource);
}
protected HikariConfig hikariConfig(DataSource dataSource) {
HikariConfig hikariConfig = new HikariConfig();
int cpuCores = Runtime.getRuntime().availableProcessors();
hikariConfig.setMaximumPoolSize(cpuCores*4);
hikariConfig.setDataSource(dataSource);
hikariConfig.setIdleTimeout(600000);
hikariConfig.setLeakDetectionThreshold(30000);
hikariConfig.setConnectionTimeout(30000);
hikariConfig.setMaxLifetime(1800000);
hikariConfig.setAutoCommit(false);
return hikariConfig;
}
protected HikariDataSource connectionPoolDataSource(
DataSource dataSource) {
return new HikariDataSource(hikariConfig(dataSource));
}
@Bean
public PlatformTransactionManager transactionManager(DataSource readWriteDataSource) {
return new DataSourceTransactionManager(readWriteDataSource);
}
@Bean
public NamedParameterJdbcOperations usersJdbcOperations(DataSource readWriteDataSource) {
return new NamedParameterJdbcTemplate(readWriteDataSource);
}
transaction functions
@Transactional(propagation = Propagation.REQUIRES_NEW, isolation = Isolation.REPEATABLE_READ, rollbackFor = Exception.class)
public String placeBet(PlaceBet placebet) {
try {
Bet unsavedBet = Bet.builder().amount(BigDecimal.valueOf(placebet.amount()))
.coin(placebet.coin())
.game(placebet.game())
.gid(placebet.gid())
.uid(placebet.uid())
.name(placebet.name())
.created(LocalDateTime.now().truncatedTo(ChronoUnit.MILLIS))
.originalOdd(BigDecimal.valueOf(placebet.original_odd())).build();
Bet savedBet = betRepository.save(unsavedBet);
if (null != savedBet) {
LOGGER.info("saved Bet: {}",savedBet.toString());
}
return "success";
} catch (Exception e) {
LOGGER.error("Error {} encountered saving bet: {} changes rolled Back",e.getMessage(),placebet.toString());
return "bet error";
}
}
logs
2023-01-20 00:11:15 WARN ProxyLeakTask.java:84 - Connection leak detection triggered for Pooled connection wrapping physical connection org.postgresql.jdbc.PgConnection@6fae4b49 on thread http-nio-8001-exec-11, stack trace follows
java.lang.Exception: Apparent connection leak detected
at com.zaxxer.hikari.HikariDataSource.getConnection(HikariDataSource.java:100)
at org.springframework.jdbc.datasource.DataSourceTransactionManager.doBegin(DataSourceTransactionManager.java:265)
at org.springframework.transaction.support.AbstractPlatformTransactionManager.startTransaction(AbstractPlatformTransactionManager.java:400)
at org.springframework.transaction.support.AbstractPlatformTransactionManager.getTransaction(AbstractPlatformTransactionManager.java:373)
at org.springframework.transaction.interceptor.TransactionAspectSupport.createTransactionIfNecessary(TransactionAspectSupport.java:595)
at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:382)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:119)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:763)
at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:708)
at com.api.services.BetService$$EnhancerBySpringCGLIB$$23929008.placeBet(<generated>)
at com.api.controllers.BetController.placeNewBet(BetController.java:132)
at jdk.internal.reflect.GeneratedMethodAccessor89.invoke(Unknown Source)
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.base/java.lang.reflect.Method.invoke(Method.java:568)
pool stats
2023-01-20 00:11:58 DEBUG HikariPool.java:421 - HikariPool-1 - Pool stats (total=16, active=0, idle=16, waiting=0)
2023-01-20 00:11:58 DEBUG HikariPool.java:518 - HikariPool-1 - Fill pool skipped, pool is at sufficient level.
it is released some 30secs later yet the query took ms to complete what would be the issue?
2023-01-20 00:11:15 WARN ProxyLeakTask.java:84 - Connection leak detection triggered for Pooled connection wrapping physical connection org.postgresql.jdbc.PgConnection@6fae4b49 on thread http-nio-8001-exec-11, stack trace follows
2023-01-20 00:11:56 DEBUG DataSourceTransactionManager.java:330 - Committing JDBC transaction on Connection [HikariProxyConnection@304420024 wrapping Pooled connection wrapping physical connection org.postgresql.jdbc.PgConnection@6fae4b49]
2023-01-20 00:11:56 DEBUG DataSourceUtils.java:242 - Resetting isolation level of JDBC Connection [HikariProxyConnection@304420024 wrapping Pooled connection wrapping physical connection org.postgresql.jdbc.PgConnection@6fae4b49] to 2
2023-01-20 00:11:56 DEBUG DataSourceTransactionManager.java:389 - Releasing JDBC Connection [HikariProxyConnection@304420024 wrapping Pooled connection wrapping physical connection org.postgresql.jdbc.PgConnection@6fae4b49] after transaction
2023-01-20 00:11:56 INFO ProxyLeakTask.java:91 - Previously reported leaked connection Pooled connection wrapping physical connection org.postgresql.jdbc.PgConnection@6fae4b49 on thread http-nio-8001-exec-11 was returned to the pool (unleaked)
