Oracle deadlock keeps repeating on the same record

Question

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

Answer 1

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.