CODEWITHSUNDEEP
hibernatespring-boothikaricp
Kiril Mytsykov
Kiril Mytsykov

Reputation: 739

Unable to acquire JDBC Connection

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

Upvotes: 41

Views: 216359

Answers (2)

Dennis Rippinger
Dennis Rippinger

Reputation: 458

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.

Upvotes: 18

Dean Clark
Dean Clark

Reputation: 3868

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

Upvotes: 6

Related Questions