optimistic locking batch update

Question

How to use optimistic locking with batch updates? I am using SimpleJdbcTemplate and for single row I can build update sql that increments version column value and includes version in WHERE clause.

Unfortunately te result int[] updated = simpleJdbcTemplate.batchUpdate does not contain rowcounts when using oracle driver. All elements are -2 indicating unknown rowcount.

Is there some other, more performant way of doing this than executing all updates individually? These batches contain an average of 5 items (only) but may be up to 250.

Answer 1

Just thinking aloud here - if it's a problem with the batch support in the driver, you could try to achieve the same using a single query, making batching less relevant. (As you know, batching is about avoiding latency with multiple queries, but latency is still present even when batching a single query.)

Here's how you might achieve the optimistic update with a single query

• Build a temporary table that contains the conditions necessary for a row update, and rewrite your update query to be a join on this table. (e.g. outer join the current timestamp in the live data with the timestamp in your temporary table.) If the timestamp on the live data has not been updated then the row will be selected from your temporary table.

Since you can use the temporary table as a select query, you can find which rows would be updated, and then submit this as an update query. (All within a transaction, of course.)

To illustrate:

TempUpdateTable
---------------
id,        // id of the row to be updated
timestamp, // timestamp data originally fetched
data1      // data to be updated
data2
dataN

This gives the ids of all data to be updated, which you can store for later reference

SELECT d.id FROM TempUpdateTable t JOIN YourData d 
    ON t.id=d.id WHERE t.timestamp=d.timestamp

The same query can then be used in an update statement

UPDATE YourData
SET data=t.data1
SET data=t.data2  //etc...
FROM TempUpdateTable t WHERE t.id IN
  (SELECT d.in FROM TempUpdateTable t JOIN YourData d 
    ON t.id=d.id WHERE d.timestamp=d.timestamp)