select for update not returning row
378642Nov 21 2007 — edited Dec 10 2007I have a problem with select ... for update sometimes not returning a row when I know for absolute certain that the row exists.
Here's what I'm trying to do :
int id = 99;
PreparedStatement ps = myConnection.prepareStatement("select * from myTable where ID=?");
ps.setInt(1, id); // ID is the primary key of the table
ResultSet rset = ps.executeQuery();
if ( rset.next() ) {
// do some stuff with the ResultSet
ps.close();
myConnection.rollback();
} else {
ps.close();
// insert a new row with ID = id
// deal with the condition that someone else might just have inserted the row before I got there !
myConnection.commit();
}
// Whatever, we now know the row must exist
ps = myConnection.prepareStatement("select * from myTable where ID=? for update");
ps.setInt(1, id);
rset = ps.executeQuery(); // Block if anybody else has a lock on the row
if ( rset.next() ) {
// get data out of the result set
ps.close();
} else {
ps.close();
throw new Exception("WAAAAH! row not found");
}
// more stuff
// update the row
myConnection.commit();
There are multiple instances of this code running simultaneously
sometimes hitting different rows on myTable sometimes the same row
This bit of code runs thousands of times a day usually quite OK.
Now and again the row not found exception is thrown, causing havoc.
Why would that be ?
Oracle 10.2.0.2.0 64 bit production
Red Hat Linux
Sun Java 1.5
Oracle JDBC 10.1.0.4.0
We could upgrade the JDBC drivers if someone convinced me that was the problem