HikariCP is running out of database connections

I have encountered the following problem:

Could not open JDBC Connection for transaction; nested exception is java.sql.SQLTransientConnectionException: HikariPool-1 - Connection is not available, request timed out after 30000ms.

Nasty thing. It says, that HikariCP can not acquire a new database connection and it gave up after some 30 seconds. Where is the problem?

Let’s try to simulate the problem. We want to query database and then make some REST call. Code would look something like this:

public Integer getEntry() throws InterruptedException {
        var result = repository.findById(1);
        // Simulating external REST call
        Thread.sleep(5_000);
        var result2 = repository.findById(1);
        // Simulating external REST call
        Thread.sleep(5_000);

        return result.get().getId();
}

If we call the code above multiple times in parallel, we might easily run out of database connections, especially when we have the following warning in the logs:

spring.jpa.open-in-view is enabled by default. Therefore, database queries may be performed during view rendering. Explicitly configure spring.jpa.open-in-view to disable this warning

Long story short, the property spring.jpa.open-in-view creates a Hibernate session at the start of request. If, (default value) spring.jpa.open-in-view=true, the code above will hold the database connection until end of method execution. This can easily cause problems and you might run out of database connections.

Therefore, make sure, that you have set:

spring.jpa.open-in-view=false

in your code.

Bonus question: how many database connections the code above will use?

If we have spring.jpa.open-in-view=true, the code will acquire 1 database connection and release after we exit from the method.

If we have spring.jpa.open-in-view=false, the code above will acquire 1 database connection, uses it to perform SELECT query, returns it back to the pool without waiting for the method to finish its execution.

Useful article: https://www.baeldung.com/spring-open-session-in-view