Seems that Transaction Commit is closing all open Result Sets. Any way to hold the result set open over commit?
Found https://hibernate.atlassian.net/browse/HHH-10394 referencing this issue as well, but doesn’t seem to have any activity. Any work arounds for this issue?
EDIT: Running v5.3.3
Once the Transaction is committed, the JDBC Connection will be released and will go back to the connection pool, and other Threads can get to use that Connection.
Therefore, this is the right behavior. How would that work if the associated ResultSet is not closed but the Connection#close method was called?
Try setting the hibernate.connection.release_mode property to ON_CLOSE and see if that helps.
If it does not, try to provide a fix and send us a Pull Request so we can investigate it.
The Connection Release happens after all ResultSets have already unconditionally been closed.
LogicalConnectionManagedImpl
@Override
public void afterTransaction() {
super.afterTransaction();
if ( connectionHandlingMode.getReleaseMode() != ConnectionReleaseMode.ON_CLOSE ) {
// NOTE : we check for !ON_CLOSE here (rather than AFTER_TRANSACTION) to also catch AFTER_STATEMENT cases
// that were circumvented due to held resources
log.debug( "Initiating JDBC connection release from afterTransaction" );
releaseConnection();
}
}
super.afterTransaction() calls resourceRegistry.releaseResources() which will close all resultSets.
What exactly is the use case that requires JDBC resources to be held open even after commit? Try to explain exactly what you need and how would that with Hibernate.
Step1: Query.iterate to obtain iterator of Entity id’s
Step2: For each ID, perform business logic (CRUD a separate Entity), then commit
The commit will close the resultSet obtained from Step 1.
While i understand if it is only id’s, this could likely be pre-iterated, before step 2. However, for large sets of data, this may not be feasible.
Just use pagination for Step 1 as explained in this article.
Streaming might pick a full-table scan while pagination can better take advantage of indexes.
Pagination would still require the iterator to be fully pre-iterated prior to step2? It would allow the entire resultset to be stored in memory without worry, but would still require an extra step. While this is likely feasible, it would require quite a bit of code re-write. We are upgrading from hibernate 3.5, where ResultSet holdability was honored. Curious as to why this changed?
If you use pagination, it does not matter when you consume the selected result set.
I think the current implementation is the correct one. I see no reason of ever keeping a resource open after ending a transaction. That would be a Connection leak.
Sorry, misread the pagination article. It’s using getResultList instead of Stream, therefore fully loading the List of objects at time of query. While this is an option, it also requires a sort on the query. Instead of relying on database cursor, we now have to include/maintain pagination properties to fully iterate a set of results. This seems like added overhead that should not be required. I agree, Pagination is the best approach when dealing with UI/queries that will be limited to a smaller set of Results. However if i want to iterate over a full table, pagination doesn’t seem to be the best tool for the job.
I’m not sure I agree with the current implementation being correct. Why blatantly ignore a connection property? I understand this isn’t the most common paradigm, but it is valid nonetheless. Furthermore, this is functionality that has worked in previous versions up until 5.X. Seems silly to break functionality, and force users to upgrade to work around an issue that could easily be fixe
As for the Connection leak, this shouldn’t be a problem as long as all ResultSets and PreparedStatements are properly cleaned up upon connection close. Hibernate has gone as far as to provide the ability to postpone releasing the connection until the session is closed. If this is enabled, why not honor the ResultSet.holdability? If configured to HOLD_CURSORS_OVER_COMMIT, why forcibly close them after each commit?
Pagination is the best approach when dealing with UI/queries that will be limited to a smaller set of Results. However if i want to iterate over a full table, pagination doesn’t seem to be the best tool for the job.
Maybe it’s better to do the process in the DB altogether and avoid moving tons of data from the DB to the app just to do the processing in Java.
I’m not sure I agree with the current implementation being correct.
It is correct from an OLTP perspective.
Furthermore, this is functionality that has worked in previous versions up until 5.X. Seems silly to break functionality,
Nope. It just happened to work in 3.x. It was never designed or guaranteed to work as it did.
and force users to upgrade to work around an issue that could easily be fixe
If you think it’s worth having this feature, send us a Pull Request with the implementation. That’s the beauty of open source software development.
As for the Connection leak, this shouldn’t be a problem as long as all ResultSets and PreparedStatements are properly cleaned up upon connection close.
The connection should be closed when the Tx is ended. This is the correct way.
Hibernate has gone as far as to provide the ability to postpone releasing the connection until the session is closed. If this is enabled, why not honor the ResultSet.holdability?
Nope. The Connection is bound to the Tx, not to the Session. ResultSet.holdability and OLTP don’t mix well. ResultSet.holdability reminds me of mainframes and 2-tier applications, not web, enterprise apps or microservices over the Internet.
If configured to HOLD_CURSORS_OVER_COMMIT, why forcibly close them after each commit?
Because Hibernate has never guaranteed or ever considered this mode. If you really think you can do it without breaking the current functionality, I’m eager to review your Pull Request.