问题描述
从不同的会话执行时，我有查询会导致死锁.
I have query when executed from different session is resulting in deadlock.
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跟踪文件中，看到以下语句:
In the Oracle trace file, I see the statement:
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.
===================================================
有什么办法可以避免这种情况?重写查询还是建立索引?
Is there any way to avoid this? Rewriting the Query or Indexing?
推荐答案
更新
与其试图确切地猜测Oracle如何检索行并强制执行计划，不如使用可用的 UPDATE FOR锁定功能.
Instead of trying to guess exactly how Oracle retrieves rows and force a plan, it may be easier to use one of the available UPDATE FOR locking features.
NOWAIT或SKIP LOCKED应该能够解决该问题.尽管使用NOWAIT，您可能需要添加一些应用程序逻辑以在发生错误后重试.
NOWAIT or SKIP LOCKED should be able to fix the problem. Although with NOWAIT you would probably need to add some application logic to retry after an error.
由于存在绑定变量，因此同一SQL语句可能有多个执行计划.通常这是一件好事，例如，考虑如下查询:select * from tab where status = ?.全表扫描最适合于处于流行状态，而索引扫描最适合于处于罕见状态.但是，如果一个计划使用索引而一个计划使用表，则同一条语句将以不同的顺序检索资源，从而可能导致死锁.
Since there are bind variables there may be multiple execution plans for the same SQL statement. This is normally a good thing, for example think about a query like this: select * from tab where status = ?. A full table scan would work best for a popular status, and an index scan would work better for a rare status. But if one plan uses an index and one uses a table, the same statement will retrieve resources in a different order, potentially causing a deadlock.
强制语句始终使用相同的计划将防止死锁.
Forcing the statement to always use the same plan will prevent the deadlocks.
首先，您将要确认我关于多个执行计划的理论是正确的.在此查询中查找多行，特别是对于同一SQL_ID查找不同的plan_hash_value.
First, you will want to confirm my theory about multiple execution plans is correct. Look for multiple rows in this query, specifically look for different plan_hash_values for the same SQL_ID.
select child_number, plan_hash_value, gv$sql.*
from gv$sql
where sql_text like '%NEXT=(SELECT%';
这是强制语句始终使用相同计划的问题.一种简单的方法是找到固定特定计划的大纲，并对这两个语句使用相同的提示集.希望强制计划对于所有绑定变量集仍然可以很好地运行.
Then it's a matter of forcing the statements to always use the same plan. One simple way is to find the outline that fixes a specific plan, and use the same set of hints for both statements. Hopefully the forced plan will still run well for all sets of bind variables.
select *
from table(dbms_xplan.display_cursor(
sql_id => '<SQL_ID from above>',
cursor_child_no => <child_number from above>,
format => '+outline')
);
这篇关于使用子查询选择更新导致死锁的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！