Update more than one row atomically

Question

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();
    }
}

• 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 ?

Answer 1

Use transactions.

Answer 2

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.

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();
}

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.

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:

• 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