乐观锁定批处理更新 [英] Optimistic locking batch update

问题描述

如何在批量更新中使用乐观锁定?我正在使用SimpleJdbcTemplate，对于单行，我可以构建更新sql，以增加版本列的值，并在WHERE子句中包含版本.

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

不幸的是，使用Oracle驱动程序时，结果int[] updated = simpleJdbcTemplate.batchUpdate不包含行数.所有元素均为-2，表示未知的行数.

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

除了单独执行所有更新之外，还有其他更高效的方法吗?这些批次平均包含5个项目(仅)，但最多可以包含250个.

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.

推荐答案

在这里大声思考-如果驱动程序中的批处理支持存在问题，您可以尝试使用单个查询来实现相同目的，从而使批处理的相关性降低. (您知道，批处理是为了避免多次查询的延迟，但是即使批处理单个查询，延迟仍然存在.)

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

• 构建一个临时表，其中包含行更新所需的条件，然后将更新查询重写为该表的联接. (例如，将实时数据中的当前时间戳与临时表中的时间戳进行外部联接.)如果实时数据中的时间戳尚未更新，则将从您的临时表中选择该行.

由于您可以使用临时表作为选择查询，因此可以找到要更新的行，然后将其作为更新查询提交. (当然，所有这些都在交易之内.)

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

说明:

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

这给出了所有要更新的数据的ID，您可以将其存储以供以后参考

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)

这篇关于乐观锁定批处理更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！