Springboot JPA Repository not releasing Hikari DB Connection

Question

I have a rest API in Springboot using Hikari for connection pooling. Hikari is used with default configurations (10 connections in the pool, 30 sec timeout waiting for a connection). The API itself is very simple

It first makes a JPA repository query to fetch some data from a PostgresDB. This part takes about 15-20milliseconds.
It then sends this data to a remote REST API that is slow and can take upwards of 120seconds.
Once the remote API responds, my API returns the result back to the client. A simplified version is shown below.

    public ResponseEntity analyseData(int companyId) {
        Company company = companyRepository.findById(companyId);//takes 20ms
        Analysis analysis = callRemoteRestAPI(company.data) //takes 120seconds
       return ResponseEntity.status(200).body(analysis);

    }

The code does not have any @Transactional annotations. I find that the JDBC connection is held for the entire duration of my API (i.e ~ 120s). And hence if we get more than 10 requests they timeout waiting on the hikari connection pool (30s). But strictly speaking my API does not need the connection after the JPA query is done (Step 1 above).

Is there a way to get spring to release this connection immediately after the query instead of holding it till the entire API finishes processing ? Can Spring be configured to get a connection for every JPA request ? That way if I have multiple JPA queries interspersed with very slow operations the server throughput is not affected and it can handle more than 10 concurrent API requests. .

please do not describe your code... post it – Toerktumlare Dec 22 '21 at 18:45 — Toerktumlare, Dec 22 '21 at 18:45

score 4 · Answer 1 · answered Dec 23 '21 at 03:53

Essentially the problem is caused by the Spring OpenSessionInViewFilter that "binds a Hibernate Session to the thread for the entire processing of the request" This essentially acquires a connection from the pool when the first JPA query is executed and then holds on to it till the request is processed.

This page - https://www.baeldung.com/spring-open-session-in-view provides a clear and concise explanation about this feature. It has its pros and cons and the current opinion seems to be divided on its use.

score 0 · Answer 2 · answered Mar 24 '23 at 14:53

I was facing the same issue. I did not have any @Transactional (or so I thought), and OpenSessionInViewFilter was turned off. All of the API was working fine except one endpoint which had not even anything to do with JDBC since it was doing LDAP calls.

When looking at Hikari trace, I could see that each time I called this endpoint, 8 connections were established and never released back to the pool.

But then, I found out that this endpoint was using ContextSourceTransactionManager, which is an emulation of transactions for LDAP... I removed it and the problem went away!

Springboot JPA Repository not releasing Hikari DB Connection

2 Answers2