0

I have spring-boot application with JPA and Hibernate as ORM. I am using Hikaricp for connection pooling. So I have an api that fetch some records form the database apply some business logic and returns the result to client. Since the business logic have some heavy task(calling another api) to complete.

I was expecting the database connection should be return back to pool since records fetched successfully and nothing to do with that connection but the connection is still open (getting connection leak by HikariCP (leakDetectionThreshold=5000))

Hikari config.

hikari: idleTimeout: 10000 connectionTimeout: 60000 maximumPoolSize: 10 minimumIdle: 2 poolName: gor-srms leakDetectionThreshold: 5000

Database configuration class

@Configuration @EnableTransactionManagement @EnableJpaRepositories(entityManagerFactoryRef = "srmsEntityManagerFactory", transactionManagerRef = "srmsTransactionManager", basePackages = {"com.gor.platform.srms.service.dao"})

public class SrmsDatabaseConfig {
@Autowired
private Environment environment;

@Autowired
JpaVendorAdapter jpaVendorAdapter;

@Bean(name = "srmsDataSource")
public DataSource dataSource() {
    final HikariDataSource dataSource = new HikariDataSource();
    dataSource.setDriverClassName(environment.getProperty("srms.db.driver"));
    dataSource.setJdbcUrl(environment.getProperty("srms.db.url"));
    dataSource.setUsername(environment.getProperty("srms.db.username"));
    dataSource.setPassword(environment.getProperty("srms.db.password"));
    dataSource.setIdleTimeout(Long.valueOf(environment.getProperty("hikari.idleTimeout")));
    dataSource.setConnectionTimeout(Long.valueOf(environment.getProperty("hikari.connectionTimeout")));
    dataSource.setMaximumPoolSize(Integer.parseInt(environment.getProperty("hikari.maximumPoolSize")));
    dataSource.setPoolName(environment.getProperty("hikari.poolName"));
    dataSource.setMinimumIdle(Integer.parseInt(environment.getProperty("hikari.minimumIdle")));
    dataSource.setLeakDetectionThreshold(Long.valueOf(environment.getProperty("hikari.leakDetectionThreshold")));

    return dataSource;
}

@Bean(name = "srmsEntityManagerFactory")
public EntityManagerFactory entityManagerFactory() {
    final LocalContainerEntityManagerFactoryBean lef = new LocalContainerEntityManagerFactoryBean();
    lef.setDataSource(dataSource());
    lef.setJpaVendorAdapter(jpaVendorAdapter);
    lef.setPackagesToScan(environment.getProperty("srms.entitymanager.packagesToScan"));
    lef.setPersistenceUnitName(environment.getProperty("srms.hibernate.unit_name"));
    lef.afterPropertiesSet();
    return lef.getObject();
}

@Bean(name = "srmsTransactionManager")
public PlatformTransactionManager transactionManager() {
    return new JpaTransactionManager(entityManagerFactory());
}

@Bean(name = "srmsEntityManager")
public EntityManager entityManager() {
    return entityManagerFactory().createEntityManager();
}

}

Repository class 

public interface ServiceRequestDao extends Dao<MServiceRequest, Long> {
@Query(value = "Native query.....", nativeQuery = true)
Long getParentId(@Param("childId") Long childId);

}

Vivek Patel
  • 969
  • 5
  • 4
  • Show/see your code so we/you can find the leak – Ori Marko Feb 04 '19 at 06:06
  • How are using using the connection from pool ? I believe you are using `@Transactional`,In this case the connection will be released only after the method is returned. The way to fix this is create a separate method which just does database operations and add transaction to it. If that's not possible use programatic transaction and commit when you are done with database operations. – Sairam Cherupally Feb 04 '19 at 06:43
  • @user7294900 let me know if anything else required. Just for info even when I tested by calling JPA's findOne() method I end up with same issue. – Vivek Patel Feb 04 '19 at 06:45
  • Where is the code that execute the statement? – Ori Marko Feb 04 '19 at 06:47
  • I am not using @Transactional. I did create a separate method but not worked. – Vivek Patel Feb 04 '19 at 06:53
  • Your lead detection threshold is 5 seconds. If a connection is in use for more then 5 seconds you will get a warning. Hikari can only see if a connection is checked out from the pool not if it is actually being used. So you probably want/need to increase the threshold for leak detection. – M. Deinum Feb 04 '19 at 07:09
  • @M.Deinum you are right. Fetching record form database is just taking few millis but the problem is why the connection is not returned to pool? – Vivek Patel Feb 04 '19 at 08:48
  • Because it remains open for the duration of the transaction... Everything is part of the transaction not only fetching the data. – M. Deinum Feb 04 '19 at 08:58
  • See here: https://stackoverflow.com/questions/30549489/what-is-this-spring-jpa-open-in-view-true-property-in-spring-boot – Alan Hay Feb 04 '19 at 09:55
  • @AlanHay already seen, didn't help. – Vivek Patel Feb 04 '19 at 12:24

0 Answers0