Geek Answers Handbook

Updating more than one row

I need to make a selection and then update some of the rows in the ResultSet atomic way.

The code I use looks (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 updates will be performed atomically? If not, how can I assure this?
  • What happens if any other process changes the database row that you update with updateRow() ? Is there a way to lock rows in a ResultSet ?
+4
source share
3 answers

There is probably a whole bunch of technologies and concepts here and things start to get pretty tacky when you start looking at multi-threaded / multi-processor applications.

As Yassevk said, you should study Transactions to ensure the atomic nature of your updates - an example of a very low level would be to do something like:

 ... 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 updates will then be placed in one transaction. If any of the updates throws an exception (for example, an invalid value or an unsuccessful connection path through the results), the entire batch will be discarded. (Finally added because I'm his champion, p)

This, however, will not solve your second problem, which is two competing methods trying to update the same table - race condition. Here, in my opinion, there are two main approaches: each of them has advantages and disadvantages.

The simplest approach - Lock the table - this will require minimal code changes, but it has a rather big drawback. Based on the assumption that, as in most applications, it is more readable that the record: locking the table will prevent other users from viewing the data, and the likelihood that the code will hang expects locks to be released before the connection timed out and throws an exception.

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

  • All updates for this table go through one class.
  • This class implements the Singleton pattern or provides update methods as static methods.
  • Update methods use the Synchronized keyword to prevent race conditions.
+4
source

Use transactions.

0
source

What happens if any other process changes the database row that you update with updateRow ()? Is there a way to lock rows in a ResultSet?

In Oracle, you can specify some rows to update by issuing the following SQL.

 select cola, colB from tabA for update;

The next transaction / thread / application that tries to update this row will receive an exception. see this for more details - http://asktom.oracle.com/pls/asktom/f?p=100:11:07::::P11_QUESTION_ID:4530093713805

0
source

More articles:


All Articles