Oracle 12c – ORA-00060 DEADLOCK DETECTED

deadlockjavaoracleoracle-12c

We faced Oracle deadlock (ORA-00060) problem on live for our Java/J2EE based web application. The trace file is pasted below:

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-00030013-0000CC59-00000000-00000000        106     408     X             76     171           X
TX-000F001E-0001681C-00000000-00000000         76     171     X            106     408           X

session 408: DID 0001-006A-007D5293 session 171: DID 0001-004C-001C2F48 
session 171: DID 0001-004C-001C2F48 session 408: DID 0001-006A-007D5293 

Rows waited on:
  Session 408: obj - rowid = 0002A2AC - AAAqKsAAUAABE3oAAB
  (dictionary objn - 172716, file - 20, block - 282088, slot - 1)
  Session 171: obj - rowid = 0002A2AC - AAAqKsAAUAABE2pAAA
  (dictionary objn - 172716, file - 20, block - 282025, slot - 0)

----- Information for the OTHER waiting sessions -----
Session 171:
  sid: 171 ser: 62179 audsid: 1760178 user: *******
    flags: (0x8000045) USR/- flags_idl: (0x1) BSY/-/-/-/-/-
    flags2: (0x40009) -/-/INC
  pid: 76 O/S info: user: ****, term: UNKNOWN, ospid: 48169
    image: **************
  client details:
    O/S info: user: ****, term: unknown, ospid: 1234
    machine: ********* program: JDBC Thin Client
    client info: *********************
    application name: JDBC, hash value=3519240545
    action name: ProcessDummySearchData-UpdateSearchData, hash value=560080304
  current SQL:
  UPDATE TABLE1 SET COL3=:1 ,LASTMODIFIED=SYSDATE WHERE UUID=:2  AND COL2=:3 

----- End of information for the OTHER waiting sessions -----

Information for THIS session:

----- Current SQL Statement for this session (sql_id=635v1mk350vst) -----
update TABLE1 set COL3= :1  
where 
COL4= :2  
and COL5 =  :3

If I try to apply "proposed fix" from below URL: I fail to understand how creating composite indexes (UUID, COL2) & (COL4, COL5) in TABLE1 can solve this issue? Can anyone throw some light on how to solve this issue? I understand that this particular issue is perhaps due to simultaneous update on same table/row.

https://stackoverflow.com/questions/20474959/how-to-find-out-the-cause-of-an-oracle-deadlock

Best Answer

Adding indexes will make deadlock less likely to happen because it may eliminate the need of scanning table, but it will not fix the issue completely. The problem is that nothing forces Oracle (or any other RDMBS) to lock rows prior to update in any guaranteed order. From the deadlock trace you have 2 statements that are executing at the same time and seem to update the same rows :

---1
update TABLE1 set COL3= :1  
where 
COL4= :2  
and COL5 =  :3    ;
 ----2
UPDATE TABLE1 SET COL3=:1 ,LASTMODIFIED=SYSDATE WHERE UUID=:2  AND COL2=:3;

There are multiple ways to deal with deadlocks, some of them :

1) catch error on application side and re-execute failed update.
2) do SELECT FOR UPDATE NOWAIT prior to update (with or without SKIP LOCKED)
3) SELECT rows you plan to update in particular rows and issues SELECT FOR UPDATE for each row individually, then update rows.
4) LOCK TABLE in appropriate mode before issuing update
5) Lock some common resource before update (for instance you may want to use DBMS_LOCK functions)

Related Question