Oracle死锁不断重复出现在同一条记录上 [英] Oracle deadlock keeps repeating on the same record
问题描述
我对以下查询有疑问:
SELECT o.OID, o.DOSSIER_KEY, o.DOSSIER_TYPE
FROM TVM04_VMAX_TEMP_RIGHTS
WHERE o.DOSSIER_KEY = ?
AND o.DOSSIER_TYPE = ? FOR UPDATE
即使以不同的时间间隔重复多次，它仍然会导致死锁.如果我了解死锁是如何发生的，那么我希望这两个冲突会话之一会回滚，而另一个会话会继续前进(
it keeps causing a deadlock even though it is repeated more times at different intervals. If I have understood how a deadlock happens, I would expect that one of the two colliding sessions is rollbacked and the other goes forward (here)
我确实得到了 内部异常:java.sql.SQLException:ORA-00060:在等待资源时检测到死锁，但是每次我手动运行查询时，另一个tx在我们系统上再次访问同一记录的可能性在我看来，也接近于0.
I get indeed a Internal Exception: java.sql.SQLException: ORA-00060: deadlock detected while waiting for resource, but the probability that on our system another tx is again accessing the same record, every time I run the Query manually too, seems to me near to 0.
这是我的转储Oracle文件:
This is my dump Oracle file:
Deadlock graph:
---------Blocker(s)-------- ---------Waiter(s)---------
Resource Name process session holds waits process session holds waits
TX-00400007-008c00ec 304 786 X 300 757 X
TX-00070016-0002dae6 300 757 X 304 786 X
session 786: DID 0001-0130-011DAD37 session 757: DID 0001-012C-00B1E41F
session 757: DID 0001-012C-00B1E41F session 786: DID 0001-0130-011DAD37
Rows waited on:
Session 786: obj - rowid = 0003921D - AAA5IdAAMAAHjdyAAG
(dictionary objn - 234013, file - 12, block - 1980274, slot - 6)
Session 757: obj - rowid = 0003921D - AAA5IdAARAACfC6AAz
(dictionary objn - 234013, file - 17, block - 651450, slot - 51)
----- Information for the OTHER waiting sessions -----
Session 757:
sid: 757 ser: 387 audsid: 18983600 user: 64/WLSP01
flags: (0x41) USR/- flags_idl: (0x1) BSY/-/-/-/-/-
flags2: (0x40009) -/-/INC
pid: 300 O/S info: user: oracle, term: UNKNOWN, ospid: 42730046
image: oracle@H50A450
client details:
O/S info: user: weblogic, term: unknown, ospid: 1234
machine: H53AD20 program: JDBC Thin Client
application name: JDBC Thin Client, hash value=2546894660
current SQL:
SELECT o.OID, o.DOSSIER_KEY, o.DOSSIER_TYPE FROM TVM04_VMAX_TEMP_RIGHTS o WHERE o.DOSSIER_KEY = :1 AND o.DOSSIER_TY
PE = :2 FOR UPDATE
----- End of information for the OTHER waiting sessions -----
Information for THIS session:
----- Current SQL Statement for this session (sql_id=489bnqugb9wsz) -----
SELECT o.OID, o.DOSSIER_KEY, o.DOSSIER_TYPE FROM TVM04_VMAX_TEMP_RIGHTS o WHERE o.DOSSIER_KEY = :1 AND o.DOSSIER_TYPE
= :2 FOR UPDATE
===================================================
您是否暗示为什么以及如何发生?
Have you any hint why and how it could happen?
非常感谢您！
Fabio
推荐答案
我建议使用 SKIP LOCKED 子句，以避免其他会话获取已锁定的更新行.
I would suggest to use the SKIP LOCKED clause to avoid other sessions to fetch the rows for update which are already locked.
它只会锁定可以选择进行更新的行，被跳过的其余行已被其他会话锁定.
It will only lock the rows which it could select for update, the rest which are skipped are already locked by other session.
例如，
会议1:
SQL> SELECT empno, deptno
2 FROM emp WHERE
3 deptno = 10
4 FOR UPDATE NOWAIT;
EMPNO DEPTNO
---------- ----------
7782 10
7839 10
7934 10
SQL>
会议2:
SQL> SELECT empno, deptno
2 FROM emp WHERE
3 deptno in (10, 20)
4 FOR UPDATE NOWAIT;
FROM emp WHERE
*
ERROR at line 2:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
现在让我们跳过会话1锁定的行.
SQL> SELECT empno, deptno
2 FROM emp WHERE
3 deptno IN (10, 20)
4 FOR UPDATE SKIP LOCKED;
EMPNO DEPTNO
---------- ----------
7369 20
7566 20
7788 20
7876 20
7902 20
SQL>
因此，department = 10被会话1 锁定，然后department = 20被会话2 锁定.
So, department = 10 were locked by session 1 and then department = 20 are locked by session 2.
另外，请在此处看看我的答案，以更好地了解死锁.请阅读了解Oracle死锁.
Also, have a look at my answer here for better understanding of deadlocks. Please read Understanding Oracle Deadlock.
这篇关于Oracle死锁不断重复出现在同一条记录上的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
