Skip to Main Content

Java Database Connectivity (JDBC)

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

select for update not returning row

378642Nov 21 2007 — edited Dec 10 2007
I 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
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jan 7 2008
Added on Nov 21 2007
12 comments
2,409 views