We are using version 11.2.0.4 of oracle Exadata. We got deadlock error and the statement in the RAC WFG trace i am seeing is as below. As i see it, its showing TX mode-5 LOCK during the error. And the statement logged is showing an UPDATE statement. There is no index on the UPDATED column(LS_DT) and the filter is on the primary key. Also here we are not updating the primary key(C1) of the table rather the primary key is just used as a filter in the query.
I want to understand what scenario might have caused this deadlock issue and how can i dig into this and if its application design issue and we will work to fix this in future.
Global blockers dump end:-----------------------------------
Global Wait-For-Graph(WFG) at ddTS[0.c0] :
BLOCKED 0x71615c990 5 wq 2 cvtops x1 TX 0xa0000.0x106cdb7(ext 0x2,0x0)[98000-0002-00000076] inst 2
BLOCKER 0x678450b70 5 wq 1 cvtops x28 TX 0xa0000.0x106cdb7(ext 0x2,0x0)[55000-0001-000000A7] inst 1
BLOCKED 0x719779d58 5 wq 2 cvtops x1 TX 0x380021.0x24f81(ext 0x4,0x0)[55000-0001-000000A7] inst 1
BLOCKER 0x66439cf30 5 wq 1 cvtops x28 TX 0x380021.0x24f81(ext 0x4,0x0)[98000-0002-00000076] inst 2
* Cancel deadlock victim lockp 0x71615c990
user session for deadlock lock 0x66439cf30
sid: 348 ser: 1647 audsid: 4700426 user: 1137/INFORMATICA
flags: (0x100045) USR/- flags_idl: (0x1) BSY/-/-/-/-/-
flags2: (0x40009) -/-/INC
pid: 152 O/S info: user: grid, term: UNKNOWN, ospid: 93440
image: oracle@XXXXX.XXXX
client details:
O/S info: user: user1, term: , ospid: 13668
machine: XXXX program: XXXX@XXXX (TNS V1-V3)
application name: XXXX@XXXX (TNS V1-V3), hash value=1446832488
current SQL:
UPDATE TAB1 SET LS_DT = :1 WHERE C1 = :2
DUMP LOCAL BLOCKER: initiate state dump for DEADLOCK
possible owner[152.93440] on resource TX-00380021-00024F81
user session for deadlock lock 0x71615c990
sid: 348 ser: 1647 audsid: 4700426 user: 1137/INFORMATICA
flags: (0x100045) USR/- flags_idl: (0x1) BSY/-/-/-/-/-
flags2: (0x40009) -/-/INC
pid: 152 O/S info: user: grid, term: UNKNOWN, ospid: 93440
image: oracle@XXXXX.XXXX
client details:
O/S info: user: user1, term: , ospid: 13668
machine: XXXX program: XXXX@XXXX (TNS V1-V3)
application name: XXXX@XXXX (TNS V1-V3), hash value=1446832488
current SQL:
UPDATE TAB1 SET LS_DT = :1 WHERE C1 = :2
DUMP LOCAL BLOCKER: initiate state dump for DEADLOCK
possible owner[152.93440] on resource TX-000A0000-0106CDB7
user session for deadlock lock 0x66439cf30
sid: 348 ser: 1647 audsid: 4700426 user: 1137/INFORMATICA
flags: (0x100045) USR/- flags_idl: (0x1) BSY/-/-/-/-/-
flags2: (0x40009) -/-/INC
pid: 152 O/S info: user: grid, term: UNKNOWN, ospid: 93440
image: oracle@XXXXX.XXXX
client details:
O/S info: user: user1, term: , ospid: 13668
machine: XXXX program: XXXX@XXXX (TNS V1-V3)
application name: XXXX@XXXX (TNS V1-V3), hash value=1446832488
current SQL:
UPDATE TAB1 SET LS_DT = :1 WHERE C1 = :2
DUMP LOCAL BLOCKER: initiate state dump for DEADLOCK
possible owner[152.93440] on resource TX-00380021-00024F81