Hi, we are using version 11.2.0.4 of oracle. We got a job(Delete query) failed with deadlock and now wanted to trackback and understand the cause of that and to avoid that in future. I have got below from the alert log. Need help in understanding the issue, how would the Delete and Update can cause this scenario here?
INSTANCE 1
*** 2018-09-27 14:13:15.132
user session for deadlock lock 0x780f9c1a0
sid: 548 ser: 5869 audsid: 213234108 user: 338/SP
flags: (0x45) USR/- flags_idl: (0x1) BSY/-/-/-/-/-
flags2: (0x40009) -/-/INC
pid: 235 O/S info: user: grid, term: UNKNOWN, ospid: 277507
client details:
O/S info: user: admin, term: , ospid: 23767
hash value=1857129389
current SQL:
DELETE FROM SFH SFH WHERE SFHID NOT IN (SELECT /*+ NL_AJ */ SFHID FROM SFS SFS WHERE SFHID IS NOT NULL AND SFS.SFHID=SFH.SFHID ) AND SFHID NOT IN (SELECT /*+ NL_AJ*/ SFHID FROM SRS RF WHERE SFHID IS NOT NULL AND SFH.SFHID=RF.SFHID) AND SFHID NOT IN (SELECT /*+ NL_AJ*/ SFHID FROM SDD DG WHERE SFHID IS NOT NULL AND SFH.SFHID=DG.SFHID) AND SFHID NOT IN (SELECT /*+ NL_AJ*/ SFHID FROM FE1 WHERE SFHID IS NOT NULL AND SFH.SFHID=FE1.SFHID) AND SFHID NOT IN (SELECT /*+ NL_AJ*/ SFHID FROM
DUMP LOCAL BLOCKER: initiate state dump for DEADLOCK
possible owner[235.277507] on resource TM-00017BEA-00000000
1988228 user session for deadlock lock 0x780ebfe70
1988229 sid: 1289 ser: 55073 audsid: 213236821 user: 341/STL_APP
1988230 flags: (0x45) USR/- flags_idl: (0x1) BSY/-/-/-/-/-
1988231 flags2: (0x40009) -/-/INC
1988232 pid: 378 O/S info: user: grid, term: UNKNOWN, ospid: 264316
1988234 client details:
1988235 O/S info: user: user2, term: unknown, ospid: 1234
1988236 machine: MACHINE1 program: JDBC Thin Client
1988237 application name: JDBC Thin Client, hash value=2546894660
1988238 current SQL:
1988239 INSERT INTO SFH(C1, C2, C3, ...) VALUES (:1 , :2 , :3 ..)
1988240 DUMP LOCAL BLOCKER: initiate state dump for DEADLOCK
1988241 possible owner[378.264316] on resource TM-00017BC5-00000000
INSTANCE 2- query
user session for deadlock lock 0x780df0b30
sid: 151 ser: 20769 audsid: 213255607 user: 654/SPM
flags: (0x41) USR/- flags_idl: (0x1) BSY/-/-/-/-/-
flags2: (0x40009) -/-/INC
pid: 131 O/S info: user: grid, term: UNKNOWN, ospid: 159319
client details:
O/S info: user: user1, term: , ospid: 33345
machine: MACHINE2 hash value=3433766376
current SQL:
UPDATE SSC SET C1 = :1, C2 = :2, C3 = :3,.. WHERE C5 = :25
DUMP LOCAL BLOCKER: initiate state dump for DEADLOCK
possible owner[131.159319] on resource TM-00017BEA-00000000
1990800 *** 2018-09-27 14:13:20.011
1990801 Submitting asynchronized dump request [1c]. summary=[ges process stack dump (kjdglblkrdm1)].
1990802 Global blockers dump end:-----------------------------------
1990803 Global Wait-For-Graph(WFG) at ddTS[0.d025] :
1990804 BLOCKED 0x780df4590 2 wq 2 cvtops x1 TM 0x17bea.0x0(ext 0x0,0x0)[BF000-0001-000125DE] inst 1
1990805 BLOCKER 0x780df0b30 2 wq 2 cvtops x1 TM 0x17bea.0x0(ext 0x0,0x0)[83000-0002-00107F05] inst 2
1990806 BLOCKED 0x780df0b30 3 wq 2 cvtops x1 TM 0x17bea.0x0(ext 0x0,0x0)[83000-0002-00107F05] inst 2
1990807 BLOCKER 0x780f9c1a0 3 wq 1 cvtops x1 TM 0x17bea.0x0(ext 0x0,0x0)[EB000-0001-000070FD] inst 1
1990808 BLOCKED 0x780f9f4a8 3 wq 2 cvtops x1 TM 0x17bc5.0x0(ext 0x0,0x0)[EB000-0001-000070FD] inst 1
1990809 BLOCKER 0x780df3c80 3 wq 1 cvtops x1 TM 0x17bc5.0x0(ext 0x0,0x0)[BF000-0001-000125DE] inst 1