Updating a database while using a preparedStatement select

Question

I'm selecting a subset of data from a MS SQL 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?

Can your tell us which transaction isolation level is in use? — ordnungswidrig
– ordnungswidrig, Commented Mar 6, 2009 at 9:49
... and also what the exact query and exact column you updateInt? — vladr
– vladr, Commented Mar 6, 2009 at 13:59

vladr · Accepted Answer · 2009-03-06 14:23:20Z

2

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.

edited Mar 6, 2009 at 14:23

answered Mar 6, 2009 at 14:17

vladr

67k18 gold badges131 silver badges132 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

vladr Over a year ago

Could it be that "the someone else" who is triggering the optimisitic lock exception on your side has actually performed the update that you are seeing? :)

kilhra Over a year ago

Nope, the update only happens in one place, my process is the only one accessing the database

vladr Over a year ago

Aha! This can only mean that your query is returning duplicate records, updates fine the first time, fails the second time. :) Why would this be the case?

Jacques René Mesrine · Accepted Answer · 2009-07-09 04:24:51Z

0

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)

answered Jul 9, 2009 at 4:24

Jacques René Mesrine

48.4k27 gold badges71 silver badges106 bronze badges

Collectives™ on Stack Overflow

Updating a database while using a preparedStatement select

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related