I have a problem with a database connection pool in Spring Boot 2, where connections are not returned to the pool even though they are neatly wrapped in @Transactional.

Upon opening the home page, I run a query and then open an SSE stream:

    @GetMapping("")
    public SseEmitter home() {
      derpService.derp();
      return obs.subscribeSse();
    }

The derp() call looks like this:

    @Transactional
    void derp() {
        derpRepository.derp();
    }

Which leads to:

    @Query(value = "SELECT 'derp'", nativeQuery = true)
    void derp();

As long as the SSE stream is open, the connection from the derp() call is not released. This goes against my intuition, because I assumed @Transactional would return the connection as soon as the flow exits the transaction scope.

Is this a bug in Spring?

Steps to reproduce

  1. Open the minimal example (link below)
  2. Run docker-compose up -d --build
  3. Start the application
  4. Open http://localhost:8088 in six tabs
  5. The sixth tab will not load because all connections are in use.

Resources

  • Minimal example on https://github.com/Oduig/connectionleak.
  • SO question on https://stackoverflow.com/questions/49071931/database-connection-leak-in-spring-boot-with-sseemitter

Comment From: wilkinsona

In the interests of keeping the discussion in one place, the question on Stack Overflow is sufficient.

Comment From: janickr

I experienced the same problem. I could not find anything in the spring and spring boot reference manual that warned against the use of @Transactional in combination with SSEEmitter and open entitymanager in view.

Unless I'm mistaken we could fix the issue in OpenEntityManagerInViewInterceptor and OpenSessionInViewInterceptor (and probably also the filters) by closing the entitymanager in afterConcurrentHandlingStarted (as is done in afterCompletion). I don't see any reason to keep the entitymanager open at the moment: is this a bug or is there a use case for this that I'm unaware of?

Comment From: Oduig

@janickr This is closed, I found the solution myself and posted it on SO. Did you try adding spring.jpa.open-in-view=true to your application config?

Comment From: janickr

@Oduig Hi thanks for the reply!

I know, I read the SO thread before I found this bug ticket. But only after spending more than a day figuring out why my connection pool kept exhausting after a while. IMO this is still a bug (unless I'm missing something about the nature of async web requests and entity managers)

The reason I commented on this ticket is this: it is really not that obvious that @Transactional in combination with spring.jpa.open-in-view and SseEmitter can lead to connection leaks. If this is expected (and intended) behaviour, it would be desirable that it is documented in the spring reference here : https://docs.spring.io/spring-framework/docs/current/reference/html/web.html#mvc-ann-async-sse (as such it is a documentation bug) If not, it is a bug in the framework code, and we should try to fix this. So that is why I wondered why afterConcurrentHandlingStarted would unbind the entityManager without actually closing it.

Comment From: bclozel

@janickr feel free to contribute a PR to the Spring Framework project, or start a discussion with a new issue. I suspect that this will be a hard one to tackle since it's generally applicable to "you should not bind resources to a persistent connection/streaming responses if they're likely to deplete a pool of resources".

Comment From: Divine1

@bclozel this issue occurs without "binding resource to a persistent connection".

Comment From: Divine1

i'm also experiencing the same issue. i have no idea how to release the connection. as a temporary fix i have removed invoking database queries which creating sseemitter.

Comment From: bclozel

If you’re starting a transaction, a streaming query or if the session is configured to stay opened with the view, you are effectively binding that database resource to the persistent connection. If you believe you’ve found a different problem, please create a new issue with a sample, minimal project that reproduces the behavior.

Comment From: Divine1

@bclozel thank you, i think its the same problem not different

spring.jpa.open-in-view = false

adding above property fixed the issue. it detaches the dbconnectionSession from the incoming session.

references

  • https://developer.jboss.org/docs/DOC-13954
  • https://stackoverflow.com/questions/30549489/what-is-this-spring-jpa-open-in-view-true-property-in-spring-boot

Comment From: alohaaloha

spring.jpa.open-in-view = false is not the answer since majority of spring applications relies on lazy loading of entities and their fields. This means that keeping connection open is a bug when working with SseEmitter.