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

Does it work for initial operations but then start timing out after some successful selects/saves? — Dean Clark, Jun 07 '17 at 16:16
Yes, the first time after starting the application, everything is fine. — Kiril Mytsykov, Jun 08 '17 at 07:49

score 14 · Answer 1 · answered Aug 10 '21 at 09:26

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.

score 5 · Answer 2 · answered Jun 08 '17 at 13:03

5

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 {
    . . . 
}

answered Jun 08 '17 at 13:03

Dean Clark

3,770
1
11
26

I do not use the LogRepositoryImpl directly. I have LogService and its implementation annotated with @Transactional with injected logRepository – Kiril Mytsykov Jun 09 '17 at 08:16
Ah, could not have known that based on the info in your question. Are you running in a highly concurrent environment? You could try to increase your pool size if your app needs a lot of connections at once. – Dean Clark Jun 09 '17 at 12:33
By default Hikari datasource is configured to have maximumPoolSize=10, I didn't solve my problem after setting maximumPoolSize to 30. I think, I have the situation when in few days connectionPool doesn't have free connections at all (beacause as soon as I get this Exception, I can not get connection at all - only restarting solve this problem). I have 5 scheduled methods and a lot of rest controllers. – Kiril Mytsykov Jun 09 '17 at 13:22
1

That implies to me that you definitely have some operation within your application that isn't release connections back to the pool. Do you use the datasource anywhere else? – Dean Clark Jun 09 '17 at 13:23
2

I have the exact same issue, everyday I have to restart the server and it's driving me crazy! I can't figure out the leak! – Ronny Shibley Aug 13 '18 at 17:10
I'm facing the same issue, any solutions? – wannix Jul 29 '19 at 10:18
1

Add com.zaxxer.hikari: DEBUG log level which will add pg-pool - Pool stats (total=100, active=0, idle=100, waiting=0) connection logs – NIrav Modi Nov 10 '20 at 12:38
Correct me if I am wrong, but spring jpa by default has @Transactional right? that is If I use `implements JpaRepository` – theprogrammer Apr 05 '21 at 17:25

Unable to acquire JDBC Connection

2 Answers2