By default, spring's DefaultJpaDialect doesn't close the connection, instead it delays until the entity manager is closed:
https://github.com/spring-projects/spring-framework/blob/ac0e71745b1de86e293382cce64e94f04e0ab6af/spring-orm/src/main/java/org/springframework/orm/jpa/DefaultJpaDialect.java#L112-L114
Issuing a web request to a controller, uses OpenEntityManagerInViewInterceptor who creates an entityManager that lives the whole request.
Thus, any connection opened inside a web request will remain open until the request is finished, potentially leading to starvation, unable to acquire new connections.
A real life example:
A web request starts an async process that will compute some results, and waits for it to finish. The async process requires a db connection for computation. All web requests on the same endpoint share and wait on the same async process to compute results. If all requests are issued concurrently before the async process starts, the requests will acquire all the pool db connections, so the process will not be able to acquire a connection for the computation, and will timeout.
Sample repo: https://github.com/cdalexndr/spring-boot-issue-26638
Related: https://github.com/spring-projects/spring-boot/issues/7107
Spring Boot 2.4.4
Comment From: cdalexndr
Workaround: spring.jpa.open-in-view=false + hibernate enable_lazy_load_no_trans or separate JPA logic from view.
Comment From: snicoll
Thanks for the report but that's essentially a duplicate of the discussion in #7107 where we've discussed the pros and cons of enabling this behavior by default or not. What you call a workaround is actually what you should be doing and the reason why the warning is issued.
Comment From: cdalexndr
@snicoll in #7107 the opinions are performance based.
@odrotbohm says:
So I'd love to learn what's wrong with the default setting?
also this:
Still, there's nothing that inherently breaks code when an O(S|E)IVF is in place.
The current issue proves that enabling OSIV can actually break code.
By default spring enables OSIV anti-pattern that can break code. I think this adds sufficient validation to tilt the scale balance in favor of removing the default enabled OSIV. It should only be used in dev environment, or experimental setups...
Comment From: snicoll
The current issue proves that enabling OSIV can actually break code.
I am failing to see the argument. Without OSIV, you can end up in the exact same scenario if you have too may concurrent requests or if your connection pool is not configured accordingly. OSIV adds a way to run queries during view rendering (as indicated by the warning in the logs) and therefore expand the lifecycle of the connection. We can continue the discussion on the PR that you've created as I think the warning is too generic and the phrasing could be improved.
Comment From: cdalexndr
Without OSIV, you can end up in the exact same scenario...
Without OSIV, I'm controlling the connection life time, so it's my fault if anything goes bad. A user not knowing that OSIV is enabled by default could encounter this same issue without doing anything wrong in the code.
Comment From: cdalexndr
you can end up in the exact same scenario if you have too may concurrent requests
When the whole pool is in used, the requests will wait until a connection is released and made available. As the requests finish their work, they will release the connection to be made available to other requests. So if the requests are short lived, they will all get a connection eventually.
This is not the case with my example: My above real life example replication steps result in a DEADLOCK while trying to acquire a connection... The process will hang waiting for a connection that will never be released, resulting in timeout exception (after 30s).
Comment From: roma2341
OSIV is so bad that it should be disabled by default. Recently i had a problem - Unable to acquire JDBC Connection, because all hikari connections were occupied for a long period of time. I used JMeter to replicate the issue locally and saw that after 30 simultanous requests my app throws this connection-acquiring error. The mapping checked user permission and returned file. And it seems to me, that db connection was occupied until user download the whole file and if so - this OSIV feature is real time bomb, especially for those, who create their project from scratch, becuase thei will have to rewrite and retest a bunch of logic in the future to remove this OSIV (it happens to me now). After disabling OSIV i can make 10000 of requests and all of them are successfully processed and hikari don't show error that connection acqusition wasn't performed in 30 seconds. I had to make the adapter for OSIV interceptor to skip some controllers as a temporary solution.
Comment From: Perdjesk
Hijacking this issue given that https://github.com/spring-projects/spring-boot/issues/7107 is closed. Not part of this comment to argue pros and cons of OSIV, or its activation by default.
Given a code base that uses OSIV, how to keep using OSIV pattern and deal with the following case.
The following endpoint returns files but requires a JPA DB access before fetching the file from S3.
@GetMapping("/{fileId}")
public ResponseEntity<?> findOne(@PathVariable String fileId) {
FileMetadata filemetadata = fileService.findOne(fileId); // DB access through JPA
return S3Client.getObject(filemetadata.getObjectId()) // S3 fetching of large file +10s
}
Loosely adapted from: https://www.baeldung.com/spring-open-session-in-view#2-exhausting-the-connection-pool
The above example with OSIV enabled will starve the connection pool sooner or later with connection being held while the file is being streamed to clients. Is there any possibility to keep using OSIV and adapt the above example to not starve the connection pool?