Why is my DB connection leaking without @Transactional?

user_2020332 0
Viewed 4034

I'm using Spring JPA in Spring-boot project. I have a simple named query. The proc gets invoked every 10 seconds. Without the @Transactional annotated over my repository interface, the connection in the connection pool runs out. I get "Timeout: Pool empty. Unable to fetch a connection in 30 seconds, none available[size:100; busy:100; idle:0; lastwait:30000]."

However, after I added @Transactional annotation, it works just fine. What is the relation between the annotation and the connections being released back to the pool? I thought Spring JPA would automatically release the connection after the method invocation is complete. 

 @Repository
 @Transactional
 public interface StoredProcedureRepository extends  CrudRepository<StoredProcedureDO, Long> {          
        @Procedure(name = "invokeStoredProc")
        void invokeStoredProc(@Param("id") Long id, @Param("date") java.sql.Date date);
    }

 @Entity    
 @NamedStoredProcedureQuery(name ="invokeStoredProc",
            procedureName = "schema.storedProc",
            parameters = {
                    @StoredProcedureParameter(mode = ParameterMode.IN, name = "id", type = Long.class),
                    @StoredProcedureParameter(mode = ParameterMode.IN, name = "date", type = java.sql.Date.class)})
    public class StoredProcedureDO implements Serializable {
spring-data-jpa spring-boot jpa spring java
1 Answers

I had a similar problem in my project. I was using OracleDB+Springboot+hikari+hibernate. Spend a lot of time to debug the issue and finally found that the cause was that my threads were not holding the connection till the thread exits even after DB operations are executed.

In one request i have 3 different DB calls with 2 remote calls in between. If my remote calls are taking time to return, the threads keeps holding the DB connection even if it is not operating on the DB. This resulted in other threads to starve and depleted my API performance resulting in timeouts.

I found below article that suggested to use

spring.jpa.open-in-view=false

https://raul8804.wordpress.com/2019/03/31/spring-boot-project-db-connection-leak-investigation/

After adding this config i see that connection is being takes and returned to the pool just before making and returning from the DB call. After that I hardly see 3 active connections from Hikari debug logs for the same load it was failing earlier.

user_24359420
Related