如何做一个安全的“SELECT FOR UPDATE"在 DB2 上的多个表上使用 WHERE 条件? [英] How to do a safe "SELECT FOR UPDATE" with a WHERE condition over multiple tables on a DB2?

问题描述

在 DB2(9.5 版)上的 SQL 语句

On a DB2 (version 9.5) the SQL statement

SELECT o.Id FROM Table1 o, Table2 x WHERE [...] FOR UPDATE WITH RR

给我错误信息 SQLSTATE=42829(不允许 FOR UPDATE 子句，因为游标指定的表无法修改).

gives me the error message SQLSTATE=42829 (The FOR UPDATE clause is not allowed because the table specified by the cursor cannot be modified).

我需要指定 WITH RR，因为我在隔离级别 READ_COMMITTED 上运行，但是当有另一个进程运行相同的查询时，我需要阻止我的查询.

I need to specify WITH RR, because I'm running on isolation level READ_COMMITTED, but I need my query to block while there is another process running the same query.

如果我改为这样查询:

SELECT t.Id FROM Table t WHERE t.Id IN (
    SELECT o.Id FROM Table1 o, Table2 x WHERE [...]
) FOR UPDATE WITH RR

一切正常.

但现在我偶尔会在多个进程同时执行此查询时遇到死锁异常.

But now I occasionally get deadlock exceptions when multiple processes perform this query simultaneously.

有没有办法制定 FOR UPDATE 查询而不引入可能发生死锁的地方?

Is there a way to formulate the FOR UPDATE query without introducing a place where a deadlock can occur?

推荐答案

首先，对于具有隔离级别READ_COMMITTED，您不需要指定WITH RR，因为这样会导致在隔离级别 SERIALIZABLE 中.指定 WITH RS (Read Stability) 就足够了.

First, for having isolation level READ_COMMITTED you do not need to specify WITH RR, because this results in the isolation level SERIALIZABLE. To specify WITH RS (Read Stability) is enough.

要将 FOR UPDATE WITH RS 传播到内部选择，您必须另外指定 USE AND KEEP UPDATE LOCKS.

To propagate the FOR UPDATE WITH RS to the inner select you have to specify additionally USE AND KEEP UPDATE LOCKS.

所以完整的语句如下所示:

So the complete statement looks like this:

SELECT t.Id FROM Table t WHERE t.Id IN (
    SELECT o.Id FROM Table1 o, Table2 x WHERE [...]
) FOR UPDATE WITH RS USE AND KEEP UPDATE LOCKS

我通过 JDBC 对 DB2 进行了一些测试，它没有死锁.

I made some tests on a DB2 via JDBC and it worked without deadlocks.

这篇关于如何做一个安全的“SELECT FOR UPDATE"在 DB2 上的多个表上使用 WHERE 条件?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！