以原子方式更新多个行 [英] Update more than one row atomically

问题描述

我需要执行一个select，然后以原子方式更新 ResultSet 中的一些行。

I need to execute a select and then update some of the rows in the ResultSet in an atomic way.

我使用的代码看起来像（简化）：

The code I am using looks like (simplified):

stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
rs = stmt.executeQuery("SELECT ...");

while (rs.next()) {
    if (conditions_to_update) {
        rs.updateString(...);
        rs.updateRow();
    }
}

• 更新将以原子方式执行？
  
• 如果任何其他进程已更改您通过 updateRow（）更新的数据库行，会发生什么？？是否有任何方法锁定 ResultSet ？


• Can I guarantee that the updates are going to be executed atomically ? If not, how could I assure that ?
• What happens if any other process has changed the database row that you are updating via updateRow() ? Is there any way to lock the rows in the ResultSet ?

推荐答案

这里可能有一大堆技术和概念，当你开始考虑多线程/多请求应用程序时，事情开始变得相当粘。

There's probably a whole heap of technologies and concepts that come into play here, and things start to get fairly sticky when you start considering multi-threaded / multi request applications.

正如Iassevk所说，您应该使用事务，以确保您的更新的原子性质 - 一个非常低级的例子是做一些事情，如下：

As Iassevk stated, you should look into using Transactions to ensure the atomic nature of your updates - a very low-level example would be to do something along the lines of:

...
con.setAutoCommit(false);
try {
  while (rs.next()) {
    if (conditions_to_update) {
      rs.updateString(...);
      rs.updateRow();
    }
  }
  con.setAutoCommit(true);
} catch (Exception ex) {
  //log the exception and rollback
  con.rollback;
} finally {
  con.close();
}

然后，所有更新将批处理到同一事务中。如果任何更新生成了异常（例如无效的值或连接失败的部分方式通过结果），整个批次将回滚。 （最后添加，因为我是它的冠军; p）

All the updates would then be batched into the same transaction. If any of the updates generated an Exception (such as an invalid value or the connection failing part way through the results), the whole lot would be rolled back. (Finally added because I am a champion of it ;p )

但是，这不会解决你的第二个问题，这是两个竞争的方法尝试更新相同的表 - 竞争条件。在我看来，这里有两个主要方法 - 每个都有它的优点和缺点。

This however, won't address your second issue which is two competing methods trying to update the same table - a race condition. There are, in my mind, two main approaches here - each has it's merits and drawbacks.

最简单的方法是锁定表 - 这将需要最少的代码更改，但有一个相当大的缺点。假设与大多数应用程序一样，它是更多的读取：write表将阻止所有其他用户查看数据，代码将挂起，等待锁在连接超时之前释放的可能性

The easiest approach would be to Lock the table - this would require minimal code changes but has a pretty big drawback. Working on the assumption that, as with most applications, it's more read that write: locking the table will prevent all other users from viewing the data, with the likelihood the code will hang, waiting for the lock to release before the connection time-out kicks in and throws an exception.

更复杂的方法是确保执行这些更新的方法以线程安全的方式实现。为此：

The more complex approach is to ensure that the methods for performing these updates are implemented in a thread-safe manner. To that end:



• 此表格的所有更新都通过单个类


• 实现Singleton模式或将更新方法公开为静态方法


• 更新方法使用 Synchronized 关键字以防止竞争条件


• All the updates for this table pass through a single Class
• That class implements a Singleton pattern, or exposes the update methods as Static methods
• The update methods utilise the Synchronized keyword to prevent race conditions

这篇关于以原子方式更新多个行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！