Unable to acquire JDBC Connection

Question

I have a Spring Boot project with Hibernate and Hikari DataSource. If i have some functionality with injected SessionFactory object to get session object, in few days I have such exception for any methods assosiated with db operations (only restarting solves this problem):

org.springframework.transaction.CannotCreateTransactionException:  Could not open JPA EntityManager for transaction; nested exception is  javax.persistence.PersistenceException:  org.hibernate.exception.JDBCConnectionException: Unable to acquire JDBC Connection at ...... Caused by: java.sql.SQLTransientConnectionException: HikariPool-1 -  Connection is not available, request timed out after 30001ms. 

It seems to manual used session makes this problem. (I have similar project with the same configs and functionality, but without injected SessionFactory and Session...and I don't have such problem at all)

application.yaml:

spring:   jpa:     properties:       hibernate:         dialect : org.hibernate.dialect.PostgreSQLDialect         current_session_context_class: org.springframework.orm.hibernate5.SpringSessionContext 

DataSourceConfig

@EnableJpaRepositories("com.my.project.config") @Configuration public class DataSourceConfig {      @Inject     private AppProperties properties;      @Bean(name = "dataSource")     public DataSource dataSource() {         AppProperties.DatabaseProperties dbProps = properties.getDatabaseProperties();         HikariDataSource dataSource = new HikariDataSource();         dataSource.setDriverClassName(org.postgresql.Driver.class.getName());         dataSource.setJdbcUrl(             dbProps.getProtocol().concat("://")                 .concat(dbProps.getDbHost()).concat(":")                 .concat(dbProps.getDbPort()).concat("/")                 .concat(dbProps.getDbname())         );         dataSource.setUsername(dbProps.getUsername());         dataSource.setPassword(dbProps.getPassword());         dataSource.setMaximumPoolSize(30);         dataSource.setMinimumIdle(30);          return dataSource;     }      @Bean     public SessionFactory sessionFactory(HibernateEntityManagerFactory hemf)   {         return hemf.getSessionFactory();     } } 

LogRepositoryImpl

@Repository public class LogRepositoryImpl implements LogRepository {      @Autowired     private SessionFactory sessionFactory;      @Override     public List<Log> getLogs(int offset, int count) {         Criteria criteria = getSession().createCriteria(Log.class);         return criteria.setFirstResult(offset).setMaxResults(count).list();     }      @Override     public void save(Log log) {         getSession().save(log);     }      private Session getSession() {         return sessionFactory.getCurrentSession();     } } 

dataSource.setMaximumPoolSize(30), dataSource.setMinimumIdle(); didn't solve this problem

Answer 1

We recently had this issue, but the Connection Pool Error message was eventually just baiting.

The real problem was a third-party system that did not accept more messages, thus blocking each thread. The connection never returned to the Pool if the thread had previously acquired a Connection from HikariCP. Hence, we had as many successful connections as Pooled Connections.

In such a case: Inspect the number of blocked Threads on the JVM with a tool of choice (any APM, VisualVM, or alike), and inspect where the blocked threads are halting with a Thread Dump.

Answer 2

Sounds to me like an issue with your transaction boundaries, which aren't releasing the connections back to the pool. Could you try putting @Transactional on your LogRepositoryImpl class?

@Repository @Transactional public class LogRepositoryImpl implements LogRepository {     . . .  }