SELECT FOR UPDATE with Subquery results in Deadlock

Question

当从不同的会话执行查询时，会导致死锁。

TAB1 (ID, TARGET, STATE, NEXT) AND ID is primary key

Column ID is the primary key.
SELECT * 
FROM 
TAB1 WHERE NEXT = (SELECT MIN(NEXT) FROM TAB1 WHERE TARGET=? AND STATE=?) 
AND TARGET=? AND STATE=? FOR UPDATE

在Oracle跟踪文件中，我看到以下语句：

DEADLOCK DETECTED
Current SQL statement for this session:
SELECT ID, TARGET, NEXT, STATE FROM TAB1 
WHERE NEXT=(SELECT MIN(NEXT) FROM TAB1 WHERE (TARGET='$any') AND ( STATE = 0))
AND (TARGET='$any')
AND (STATE = 0) FOR UPDATE
The following deadlock is not an ORACLE error. It is a
deadlock due to user error in the design of an application
or from issuing incorrect ad-hoc SQL. The following
information may aid in determining the deadlock:
Deadlock graph:
                       ---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name          process session holds waits  process session holds waits
TX-00010012-0102905b        54     474     X             52     256           X
TX-000a0005-00a30961        52     256     X             54     474           X

session 474: DID 0001-0036-00000002 session 256: DID 0001-0034-00000002
session 256: DID 0001-0034-00000002 session 474: DID 0001-0036-00000002
Rows waited on:
Session 256: obj - rowid = 00013181 - AAATGBAAzAABtPTAAI
  (dictionary objn - 78209, file - 51, block - 447443, slot - 8)
Session 474: obj - rowid = 00013181 - AAATGBAAzAABtPUAAJ
  (dictionary objn - 78209, file - 51, block - 447444, slot - 9)
Information on the OTHER waiting sessions:
Session 256:
  pid=52 serial=58842 audsid=43375302 user: 106/B2B_ISINTERNAL
  O/S info: user: admwmt, term: spl099wmt04.compucom.local, ospid: , machine: spl099wmt04.compucom.local/10.16.0.41
            program: JDBC Connect Client
Current SQL Statement:
SELECT ID, TARGET, NEXT, STATE FROM TAB1 
WHERE NEXT=(SELECT MIN(NEXT) FROM TAB1 WHERE (TARGET='$any') AND ( STATE = 0))
AND (TARGET='$any')
AND (STATE = 0) FOR UPDATE
End of information on OTHER waiting sessions.
===================================================

有什么方法可以避免这种情况吗？重写查询还是索引？

Answer 1

我认为原因可能是您实际上使用FOR UPDATE子句选择了同一个表两次，一次在主查询中，一次在子查询中。

Answer 2

更新

使用一个可用的UPDATE FOR locking features可能更容易，而不是试图猜测Oracle如何检索行并强制执行计划。

NOWAIT或SKIP LOCKED应该能够解决这个问题。尽管使用NOWAIT，您可能需要添加一些应用程序逻辑，以便在出现错误后重试。

由于存在绑定变量，因此同一SQL语句可能有多个执行计划。这通常是一件好事，例如，考虑如下查询：select * from tab where status = ?。全表扫描最适合流行状态，而索引扫描更适合稀有状态。但是，如果一个计划使用索引，而另一个计划使用表，则相同的语句将以不同的顺序检索资源，这可能会导致死锁。

强制语句始终使用相同的计划可以防止死锁。

首先，您需要确认我关于多个执行计划的理论是正确的。在这个查询中查找多行，特别是为相同的plan_hash_value查找不同的SQL_ID。

select child_number, plan_hash_value, gv$sql.*
from gv$sql
where sql_text like '%NEXT=(SELECT%';

然后就是强制语句始终使用相同的计划的问题。一种简单的方法是找到修复特定计划的大纲，并对两个语句使用相同的提示集。希望强制计划仍然可以很好地运行于所有绑定变量集。

select *
from table(dbms_xplan.display_cursor(
    sql_id => '<SQL_ID from above>',
    cursor_child_no => <child_number from above>,
    format => '+outline')
);