Affects: \4.3.28

We have a search facility that returns a SqlRowSet obtained from jdbcTemplate.queryForRowSet. The row set is returned to a UI that displays the contents of the row set in a grid. We are experiencing a similar problem to that described in #23718.

Each time the search is used the connection is not being returned to the pool. We can see the database sessions incrementing over time. We can reclaim the connections using the abandoned connection timeout.

I have tried the work-around suggested in the linked issue and it appears to be working. Explicitly closing the result set prevents the number of database sessions from increasing.

Comment From: sbrannen

Can you please tell us what database and version you are using?

Also, what is the concrete type of the CachedRowSet in use?

The latter can be obtained via ((ResultSetWrappingSqlRowSet) sqlRowSet).getResultSet().getClass().getName().

Comment From: chriswightman

The problem is occurring in Oracle Database 19c Standard Edition 2. It was originally found in Oracle 12c Standard Edition. Exact type of SqlRowSet is: com.sun.rowset.CachedRowSetImpl

Comment From: snicoll

@chriswightman sorry this got overlooked. Can you please share a small sample that demonstrates that the ResultSet is not closed? The issue you've linked was closed because we couldn't find a way to reproduce and I don't think we will be able to without more details from you.

You can attach a zip to this issue with the small app or push the code to a separate GitHub repository.

Comment From: chriswightman

I can give you some more information. The search facility produces a number of criteria that we use to build SQL and bind values to. The SQL can become quite large. The search is run in a thread to prevent the UI from locking up. Access to the data returned in the SqlRowSet is also done in this thread. We then access the UI (Vaadin) and update a grid with the results. The thread then terminates. This is when we can see that the SqlRowSet connection has been abandoned. We added code to explicitly close the SqlRowSet. This stopped the connection leak.

Comment From: snicoll

Thanks for following up but, unfortunately, that's another way to describe the problem. We'd really need to be able to reproduce the issue to be able to assess if a fix needs to be made here. Can you please provide the sample?

Comment From: chriswightman

Unfortunately, the use of the SqlRowSet is buried within a whole load of proprietary code, which I can’t share with you.

So, I am afraid there is nothing else to do here.

Comment From: snicoll

We don't need that code specifically but a way to reproduce the problem. If you can't provide that, the issue is no longer actionable so I am going to close it. But we can reconsider for sure if you manage to provide a sample project.