使用preparedStatement选择时更新数据库

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:

我正在使用PreparedStatement从MS SQL数据库中选择一个数据子集。在遍历结果集时,我还想更新行。目前我使用这样的东西:

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?

如何防止此异常?或者既然程序确实做了我想做的事情,我可以忽略它吗?

2 个解决方案

#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.

最好的策略是忽略异常(记录未更新),可能将失败的记录推送到队列(在内存中),然后对失败的记录进行第二次传递(重新评估查询中的条件并根据需要进行更新) - 如果情况不是这样,则将数字<> 20添加为查询中的条件之一。)重复直到不再有记录失败。最终所有记录都将更新。

#2

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

假设您确切知道要更新哪些行,我会这样做

SET your AUTOCOMMIT to OFF

将您的AUTOCOMMIT设置为OFF

SET ISOLATION Level to SERIALIZABLE

SET ISOLATION级别为SERIALIZABLE

SELECT row1, row1 FROM table WHERE somecondition FOR UPDATE

SELECT row1,row1 FROM表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)

这是通过悲观锁定实现的(假设您的数据库支持行锁定,它应该工作)

#1

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