ResultSet Already Closed – Issue

Ø       Background:

In the current J2EE applications, various Database operations are involved. These operations play very important role in J2EE Applications in terms of Information management, Performance etc. The common Database operations are SELECT, INSERT, UPDATE, DELETE and Stored Procedures calls. These operations if not handled properly through the code, can lead to different issues like Performance hit, Deadlock and Timeout etc which can bring the complete Application down. So one not only needs to carefully design the Database tables, indexes, but also needs to handle all the operations carefully so that those will not hamper the application.

As mentioned above, the most common issues which we can face are Deadlock, Query Timeout, bad performance of the Queries due to incorrect Query structure or inefficient Database design, Connection unavailability. But apart from these commonly occurring issues/errors, we can encounter a weird error while accessing ResultSet objects as “ResultSet already closed”. This error can occur in spite of your Database design is good or your database handling code is clean. So, we need to deal differently with this kind of error. Let’s refer one practical scenario where this issue could occur.

 

Ø       Issue/Scenario:

Consider following practical scenario which can lead to above mentioned issue:

1. We have one function in the class (Data Access Layer) which does the following things:

It establishes the Connection with Database.
It creates the Statement Object with the SELECT Query (which returns some results)
It fetches the result in ResultSet object.
In the loop for the results fetched in ResultSet (in “while <>.next()” loop), for each iteration, call one more private function and pass the same Connection object to it.

2. The called private function uses the same Connection object. Using it, it executes the SELECT query. For this, it creates new Statement and ResultSet object for that Connection. We execute the query using the same Connection object and loop through the results that are fetched and process those as per the requirement. Finally after all the processing is finished, we close those ResultSet and Statement objects created in this function. It does NOT close the Connection object though.

3. So, when we return back to previous calling function, the Connection object is still open. But when we try to fetch the next record from original ResultSet (which was created in first function) by calling “<>.next()”, it gives error as “ResultSet already closed”.

 

Ø       Understanding with an Example

Let’s say we have first function as “abc()” and the other function which gets called from abc is “def()”.
We have SELECT query in the function abc() which fetches say 10 results. So, we loop through each result obtain through “<>.next()” call.
In each of the iteration, we call “def()” and pass the same Connection object with “AutoCommit as true”.
In “def()” function, it again executes SELECT query using the same Connection object. It process the results obtained and before returning, in the “finally” block, it closes the Statement and ResultSet objects that are created in this (def()) function. But it does NOT close the Connection object.
So when the control returns back to “abc()” function and it tries to go to the next iteration of the ResultSet object of “abc()” function, it says “ResultSet already closed”.

 Please refer to the “Appendix” section for the code snippet of above scenario.

 

Ø       Reason:

Let’s dig more into the problem. We will continue with the same example given above to understand it in a better way. In function “def()”, we used the same Connection object which was created in “abc()” function. Also, in “def()” function, we closed only the ResultSet and Statement objects, NOT the Connection object. Whenever we close any Connection object, all the open objects associated with it; like Statement and ResultSet also get closed automatically. If we would have closed the Connection object, then it would have said that the ResultSet in “abc()” function is already closed. But in above mentioned scenario, this has not happened. In spite of keeping Connection object open, we got the error saying “ResultSet already closed.”

This issue is common when we use the same connection object for indirect nested looping as described in above example. The problem lies with one of the ResultSet object properties, which gets set by default in Application Server. This ResultSet property is “ResultSet Holdability” which by default gets the value as “CLOSED_AFTER_COMMIT” (value=2). This issue is also related to Connection’s AutoCommit property which gets set to “true” by default. Below mentioned information depicts what has happened in the above mentioned scenario:

When we execute the SELECT query in “abc()” function, we got two opened objects on the same Connection object, first is “Statement” object and other is cursor, “ResultSet” object.
In the while loop of its ResultSet, we call one function “def()”, we pass the same Connection object (with AutoCommit=true) to “def()” method . When passed, it gets passed with all its existing properties and settings (with “abc()” method’s Statement and ResultSet objects of open on it).
“def()” method has its own SELECT query which gets executed on the same passed Connection object. So, when the Statement gets executed by “executeQuery ()”, Transaction gets committed and by default (as AutoCommit is by default: true), for that Connection, java closes all the opened Cursors first, and then opens the new Cursor (ResultSet) in called method.
So, the already opened cursor in “abc()” method gets closed at this time only. That’s why, when the control returns back from called function and try to go to next iteration of <>, it says, “ResultSet already closed”.

 

Ø       Solution:

Auto-Commit: First and probably the easiest solution is set “AutoCommit=false” for the Connection Object immediately after creating it. Hence, when we execute any Query in the called function, it will not close the previously opened cursors as Commit is not happened yet. So, “CLOSED AFTER COMMIT” will not come into picture.

Pros:This is the easiest solution which will take care of preventing closing the previously opened cursors on the same Connection object.
Cons: This is advised if all the Queries are only the “SELECT” queries. So, COMMIT will not matter. This will avoid the closing of the outer loop’s cursor and application will run smoothly. But if we need to use this option for Update Queries, we have to take utmost care because setting AutoCommit false will not update the rows immediately until we specifically say
“.commit()”. So, this is not a generic solution as such.

ResultSet Holdability: There is one option in Application Server (Database Connection settings), where we can mention the attribute value of “ResultSet Holdability” as “HOLD_OVER_COMMIT” (value=1) for the ResultSet cursor type, instead of “CLOSED AFTER COMMIT”. This option will prevent the closing already opened ResultSet (cursors), even if the inner loop’s (called method) transaction commits. This Holdability option also can be set through the code as shown below:

Statement st = .createStatement(ResultSet.TYPE_FORWARD_ONLY,

ResultSet.CONCUR_READ_ONLY,ResultSet.HOLD_CURSORS_OVER_COMMIT);

After closing the Connection object, open cursors defined WITH HOLD remain open. The cursor is positioned before the next logical row of the results table. 

Pros: This is probably the safest solution if at all we have such a situation in the code. Setting of one parameter in Application Server or declare HOLD_CURSOR parameter while creating Statement object will prevent the “ResultSet closed” error from occurring. This option will also avoid the code changes such as setting AutoCommit false which reduces the risk of other manual errors.
Cons: By default the value set is “CLOSE_AFTER_COMMIT”, which closes all the open cursors immediately after committing. So, if we are saying, don’t close the cursors on commit, and then the code needs to be written properly to have a control over the number of open cursor objects. Also, we have to make sure that all the open objects are closed once the processing is over. Simply closing all the objects like ResultSet, Statement and Connection will serve the purpose.

 

Joins/Inner Queries: Third solution to this problem could be avoiding the nested loops. Write the query in such a way that it will avoid the nested loops (function calling). This will avoid calling another function in the ResultSet loop and in turn, it will avoid causing this Exception.

Pros: Along with avoiding the nested calls, it will also avoid the temporary creation of Statement and ResultSet object in the called function.
Cons: This option will require formulating the nested/inner queries which might degrade the performance of the application if the numbers of records are more in the Database. This will affect more in case of large user-base applications. Also, if each query involves more than 2-3 tables itself, then this option is least advisable.

 

Creation of Temporary Connection: Forth option could be, NOT passing the Connection object to the called private function. Instead, create the new Connection objet in the called function and close it in the same function. This will avoid closing the ResultSet object in the calling function as that ResultSet is no more related to the Connection Object in the calling function.

Pros: This option will avoid closing the outer method’s ResultSet object as the Connection objects would be different.
Cons: This option may lead to creating of many temporary Connection objects in the private functions. Sometimes, if no Connections available in the Connection pool, application may timeout in waiting for the Connection to be available. Also, it’s not advisable to create so many temporary Connection objects as using this option, we need to be very careful during coding and need to close all the Temporary objects created once their purpose is saved.

 

Ø       Conclusion:

As discussed above, there are 4 most possible and common options to avoid the error. Each option has pros and cons. But going by the amount of coding and testing needs to be done after the code changes, the resource usage and performance impact, the second option (changing the ResultSet Holdability option in the Application Server or through code) is the better and recommended one. This will also avoid the risk of manual coding errors. Still, the last decision always depends on the Project Requirement and the situation. So, while deciding, we need to consider all the possible advantages and disadvantages which we will be having after choosing certain approach.

 

Ø       Appendix: 

Below is the pseudo code which executed, gives error as “ResultSet already closed” at highlighted line:

Public
<object>
</object>
<object>
</object>

-
About the Author:
Ashutosh Koshe is Team Lead in IBM. Prior to that, he was working in Infosys. He has 5 years of IT experience out of which has 3 years of team lead experience. He has extensively worked in Java-J2EE tecnology and has handeled various complex issues for Programming, Performance, Database. He has also handeled Quality, Performance, Automation of the Projects. He was involved in some of the technical Researhes for the company. He also conducts the Interviews for candidates.
Article Source