Updating a database while using a preparedStatement select

Question

Hi I'm selecting a subset of data from a MSSql datbase, using a PreparedStatement. While iterating through the resultset, I also want to update the rows, at the moment I use something like this: prepStatement = con.prepareStatement( selectQuery, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);

rs = prepStatement.executeQuery();

while(rs.next){ rs.updateInt("number", 20) rs.updateRow(); }

The database is updated with the correct values, but I get the following exception: "Optimistic concurrency check failed. The row was modified outside of this cursor."

I've googled it, but haven't been able to find any help on the issue.

How do I prevent this exception? Or since the program does do what I want it to do, can I just ignore it?

Answer 1

The record has been modified between the moment it was retrieved from the database (through your cursor) and the moment when you attempted to save it back. If the number column can be safely updated independently of the rest of the record or independently of some other process having already set the number column to some other value, you could be tempted to do:

con.execute("update table set number = 20 where id=" & rs("id") )

However, the race condition persists, and your change may be in turn overwritten by another process.

The best strategy is to ignore the exception (the record was not updated), possibly pushing the failed record to a queue (in memory), then do a second pass over the failed records (re-evaluating the conditions in query and updating as appropriate - add number <> 20 as one of the conditions in query if this is not already the case.) Repeat until no more records fail. Eventually all records will be updated.

Answer 2

That would indeed be it! (In reference to Vlad's comment on his own post) Thank you so much

I know what happens now, it joins two tables, one of which has 6 results conforming to what i want and one having three, this then produces a result of 6, which is wrong.
The only thing making it unique is the uid, which I don't use in updating them. So it would see some of them as duplicates.

it will not be a problem once it goes into production, as the test data contains time duplicates, which should be impossible in prod.

Thanks again!

Answer 3

Assuming you know exactly which rows you will update, I would do

• SET your AUTOCOMMIT to OFF
• SET ISOLATION Level to SERIALIZABLE
• SELECT row1, row1 FROM table WHERE somecondition FOR UPDATE
• UPDATE the rows
• COMMIT

This is achieved via pessimistic locking (and assuming row locking is supported in your DB, it should work)