ResultSet holdability into the View layer broken by Hibernate 5

[symposion]

Some time ago a fix was introduced for this: #16954 . It allows specifying that ResultSets should be held open beyond transaction commit. (Separate problem: there's no easy way to do this for JPATransactionManager, which makes usage with Spring Data hard). In the meantime, however, changes made in Hibernate 5 have re-broken this functionality. Hibernate now aggressively closes all JDBC resources on transaction commit, and the Hibernate maintainers have basically said that cursor holdability is evil and they're not interested in supporting it (https://discourse.hibernate.org/t/resultset-holdability-not-working-with-hibernate/1445/9)

As things stand, this makes it pretty much impossible (AFAICT) to build a fully streaming stack from database to MVC layer (using Open Session In View) - in particular a streaming REST API that returns a stream of JSON objects from a large dataset. It also renders HibernateTransactionManager.allowResultSetAccessAfterCompletion pointless because Hibernate closes the result set anyway, irrespective of the behaviour of the underlying JDBC driver.

It's not clear to me if there's a viable solution for any of this, but as a minimum it would be great if this was clearly documented as a limitation. I've spent hours wandering through issues, forum posts + SO answers getting conflicting information about this and it would be good to have a definitive statement of what is and isn't possible in this regard

[nicklyra]

I've run into this very problem today when some code started exhibiting java.sql.SQLException: Operation not allowed after ResultSet closed errors during serialization. A method annotated with @RequestMapping is returning an entity that contains a database entity that has some lazy-loaded fields, and it looks as though the transaction is closed at some point before AbstractJackson2HttpMessageConverter.writeInternal gets called, and our Hibernate5Module has Hibernate5Module.Feature.FORCE_LAZY_LOADING enabled.

It looks like it is then trying to lazy-load those fields, but it can't because the transaction (and thus the connection and its result set) is already closed.

This may sound naive as I don't know a great deal about Spring Boot internals, but if a @RestController is annotated with @Transactional could the transaction/session closure merely be delayed until after object serialization was complete? It might be a little ugly and break down the separation between Controller and View of course... seems you'd likely need the VIew to issue a callback when it was done doing what it needed to do so the Controller side of things could clean up.

I guess one potential workaround would be to grab the ObjectMapper and serialize the object graph first, but of course this wouldn't work well for very large object graphs.

Update: I discovered that in our case it wasn't what I thought it was: there was some wrongful interplay between a custom Hibernate Persister and a service into which it called that was annotated with @Transactional. This was causing a new transaction to start while Hibernate and the Persister were processing the ResultSet internally. In our case it appears that the right / expected thing was actually happening with serialization, though I'm actually a little bit surprised that it works. Leaving this comment here just in case it helps someone else who is researching similar behavior in the future.

[jhoeller]

Reading up on the current state of affairs, it seems appropriate to deprecate setAllowResultAccessAfterCompletion since there is no chance of this getting addressed in the Hibernate 5.x line, and to document it as effectively not working as intended anymore. Also, Spring only supports Hibernate 6.x via JPA which does not have an equivalent setting to begin with, so deprecation is also applicable in that direction.