1

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)
Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
Johnys
  • 111
  • 6
  • I'm sure it's infuriating for you, but it is unlikely that we can help without more information. I think we will need an [MCVE] — which means (minimal) table schemas, data sets and code that reproduces the problem. That will help your Tech Support channels too. If the minimum information is too big for an SO question, you should probably go to Tech Support, but they will appreciate having as minimal a set of code and data as you can find. – Jonathan Leffler Jan 17 '23 at 03:02
  • @JonathanLeffler I included code, which is being used in the program. The reason this is not reproducible is because i was not able to reproduce it, it only happens if it comes after a few programs (so the connection is not fresh). Table schema is not important in this matter. – Johnys Jan 17 '23 at 09:58
  • The fact `writeCon` and `readCon` seem to be fields, and not local variables makes a possible reason that you're sharing the same connections with multiple threads, and then when one thread closes, or commits, or does something else which closes result sets, the other thread is confronted with that problem. – Mark Rotteveel Jan 18 '23 at 15:54

1 Answers1

4

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.

Brian Hughes
  • 683
  • 3
  • 8
  • 1
    You have a really good point, but (un)fortunately this error does not occur in multi threaded environment. For each select statement is defined new statement/preparedstatement object on the connection. The processes are (in order) 1- single threaded using Connection1, 2-single threaded using Connection2, 3- (the error program) single threaded using Connection1, 4- multi threaded using connection 1 with 5- on another thread using connection 3. So program, that throws this error is executed only in the single thread env. This program has to close before creating the 2 threads for the last 2 prog – Johnys Jan 18 '23 at 20:59
  • Also, readCon and writeCon are two separate connections, and f.e. insert,update,delete never happens on readCon and retrospectively select never happens on writeCon. I will try to turn on the driver debug mode, Thank you – Johnys Jan 18 '23 at 21:04
  • The sqliprint did not hint me anyhow, I've contacted my support team about this issue. What bothers me even more is that the documentation on sqliprint is non-existent. I am absolutely sure I'm not using the connection among other threads (I've profiled the application and in fact no other thread is created while this process is running). – Johnys Jan 24 '23 at 09:12
  • The SQLIprint stuff is not intended to be self-servicing but it's also not clear in the docs you are not really supposed to undeerstand it. How it's been forever. So if you don't have any other threads running then there is possibly a bug in the driver/server. But without a reproduction or trace it will be hard to detect the problem. There are even newer JDBC drivers that you can try out as well if the problem returns. 4.50.1 is 4 years old or more? – Brian Hughes Jan 25 '23 at 13:22
  • I can't really operate on newer drivers, because this is a standard at my job. The database server (even when it has its bugs) is fine, there are many more processes and users using this database, so the problem is probably client side. Even our database support could not decode the sqliprint files, they said there was nothing wrong, not even a trace of some exception. They asked for original sqlidebug files, so i'm still waiting on the response. – Johnys Jan 30 '23 at 12:17