JDBC - SQLException : The cursor has been previously released and is unavailable

Question

I have working program in Java that is in production for a long time now. It updates a few thousand records from database 2x per day.

After new year, when database took a hit (lot of processing happening on 1st) and I updated the other parts of the code to a new version (whole process consists of 5 programs (this is 3rd/5) that are run together in Eclipse project, but I did not change this program even a little bit), I get SQL Exception:

The cursor has been previously released and is unavailable

Where does the exception happen?

• While iterating ResultSet, doesn't matter how many rows it already read. (can happen on third, can happen on 2000th row).
• ResultSet is created on connection that was used before and is read only.
• ResultSet is created on newly created Statement object. ->Updates on the same table are done with another write-only connection transaction-wise.

This example is probably not reproducible.

Database: IBM Informix Dynamic Server Version 14.10.FC7
Eclipse version: 2021-12 (4.22)
Java version: 1.8.0_131
JDBC driver version: 4.50.1

readCon = DriverManager.getConnection(url, user, passwd); 
writeCon = DriverManager.getConnection(url, user, passwd);
Statement st = readCon.createStatement();
ResultSet rs = st.executeQuery(select from table_X....);

while (rs.next){
   // commit is not happening if transaction didnt begin
   writeCon.<commit transaction, begin transaction> 
   writeCon.UpdateUsingPreparedStatement(update table_X...)
}
...

NOTE: This program runs smoothly without any problems after running the process from that program (from step 3)

What did I learn from trying to search how to solve this?

• I didn't find much on the Internet, only solution was to update JDBC driver to 4.50.1 (which I am using right now)

Answer 1

In almost all cases where I have seen this comes down to 2 types of problems.

1. Concurrent use of the same JDBC objects. This is almost always the case. The program has threads and the threads are reusing connections or Statement objects. This often blows up on you in high concurrent environments because you end up operating on the same internal statement id and one thread is then closing the statement/cursor on your other Thread so it looks like the statement closed on you suddenly. As you say you do have 2 connections but do make absolutely sure nothing is shared among threads. I've debugged a number of customer applications that thought they had proper separation but in fact did end up sharing some objects among threads. Turning on the SQLIDEBUG or instructing the driver to dump the protocol tracing events will show who sent the close on the statement. Support teams can help with this analysis. Usually when I do this, I find the close was sent by another thread right in the middle of the work you really wanted done.
2. Much rarer, but occasionally another issue will cause the cursor to get closed, but in those cases, you would see very obvious prior Exceptions from the JDBC driver and/or server before you hit this statement already closed error. This could be that you hit this problem WARN - Failed to getImportedKeys The cursor has been previously released and is unavailable and upgrading the driver does fix it.

My guess is you have shared connection objects among threads that 99% of the time doesn't clash, but when you get to having a really busy system that 1% shows up and causes the issue you are seeing.