I have a problem with the following query:
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)
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.
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?
Thank you very much in advance!
Fabio
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.
For example,
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>
Session 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
Now let's skip the rows which are locked by session 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>
So, department = 10 were locked by session 1 and then department = 20 are locked by session 2.
Also, have a look at my answer here for better understanding of deadlocks. Please read Understanding Oracle Deadlock.