如何做一个安全的“选择更新” DB2上有多个表的WHERE条件？ [英] How to do a safe "SELECT FOR UPDATE" with a WHERE condition over multiple tables on a DB2?

问题描述

问题

在DB2（9.5版）上，SQL语句

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

给我错误消息 SQLSTATE = 42829 （不支持FOR UPDATE子句，因为光标指定的表不能修改）。

附加信息

我需要指定 WITH RR ，因为我正在运行在隔离级别 READ_COMMITTED ，但我需要我的查询阻止，而另一个进程运行相同的查询。

解决方案到目前为止...

如果我这样查询：

  SELECT t.Id FROM Table t WHERE t.Id IN（
 SELECT o.Id FROM Table1 o，Table2 x WHERE [...] 
）FOR RRDATE with RR 
  

一切正常。

新问题

但是，当多个进程同时执行此查询时，我偶尔会遇到死锁异常。 >

问题

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

解决方案

首先，为了具有隔离级别 READ_COMMITTED 您不需要指定 WITH RR ，因为这导致隔离级别 SERIALIZABLE 。要指定 WITH RS （读稳定）就足够了。

传播 FOR UPDATE WITH RS 到内部选择你必须另外指定使用和保持更新锁定。

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

  SELECT t.Id FROM Table t WHERE t.Id IN（
 SELECT o.Id FROM Table1 o，Table2 x WHERE [...] 
）用于更新的RS使用和保持更新LOCKS 
  

我通过JDBC对DB2进行了一些测试，并且没有死锁。

Problem

On a DB2 (version 9.5) the SQL statement

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

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

Additional info

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.

Solution so far...

If I instead query like this:

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

everything works fine.

New problem

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

Question

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

解决方案

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.

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

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

这篇关于如何做一个安全的“选择更新” DB2上有多个表的WHERE条件？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！