Can someone please help me in finding the cause of the deadlock and how to really analyze the below trace file -
*** 2014-02-18 14:10:55.033
Global blockers dump start:---------------------------------
DUMP LOCAL BLOCKER/HOLDER: block level 3 res [0x23000f][0xb3a3d],[TX][ext 0x2,0x0]
----------resource 0x8d9ba3348----------------------
resname : [0x23000f][0xb3a3d],[TX][ext 0x2,0x0]
hash mask : x3
Local inst : 2
dir_inst : 2
master_inst : 2
hv idx : 32
hv last r.inc : 2
current inc : 2
hv status : 0
hv master : 1
open options : dd
grant_bits : KJUSERNL KJUSEREX
grant mode : KJUSERNL KJUSERCR KJUSERCW KJUSERPR KJUSERPW KJUSEREX
count : 1 0 0 0 0 1
val_state : KJUSERVS_NOVALUE
valblk : 0x50030000000000005038161909000000 PP8
access_inst : 2
vbreq_state : 0
state : x0
resp : 0x8d9ba3348
On Scan_q? : N
Total accesses: 32312
Imm. accesses: 29865
Granted_locks : 1
Cvting_locks : 1
value_block: 50 03 00 00 00 00 00 00 50 38 16 19 09 00 00 00
GRANTED_Q :
lp 0x8c296fd08 gl KJUSEREX rp 0x8d9ba3348 [0x23000f][0xb3a3d],[TX][ext 0x2,0x0]
master 2 gl owner 0x8d90b6d68 possible pid 30752 xid 159000-0002-000000F4 bast 0 rseq 2336 mseq 0 history 0x14951495
open opt KJUSERDEADLOCK
CONVERT_Q:
lp 0x8c297cb28 gl KJUSERNL rl KJUSERPR rp 0x8d9ba3348 [0x23000f][0xb3a3d],[TX][ext 0x2,0x0]
master 2 gl owner 0x8c112b1d8 possible pid 24175 xid 3B000-0002-00000B0D bast 0 rseq 2336 mseq 0 history 0x1495149a
convert opt KJUSERGETVALUE
----------enqueue 0x8c296fd08------------------------
lock version : 1513
Owner inst : 2
grant_level : KJUSEREX
req_level : KJUSEREX
bast_level : KJUSERNL
notify_func : (nil)
resp : 0x8d9ba3348
procp : 0x8c18b5b70
pid : 30752
proc version : 153
oprocp : (nil)
opid : 30752
group lock owner : 0x8d90b6d68
possible pid : 30752
xid : 159000-0002-000000F4
dd_time : 0.0 secs
dd_count : 0
timeout : 0.0 secs
On_timer_q? : N
On_dd_q? : N
lock_state : GRANTED
ast_flag : 0x0
Open Options : KJUSERDEADLOCK
Convert options : KJUSERGETVALUE KJUSERNOQUEUE KJUSERNODEADLOCKWAIT
History : 0x14951495
Msg_Seq : 0x0
res_seq : 2336
valblk : 0x00000000000000000000000000000000 .
user session for deadlock lock 0x8c296fd08
sid: 1157 ser: 42431 audsid: 16262658 user: 61/ELMS
flags: (0x8000045) USR/- flags_idl: (0x1) BSY/-/-/-/-/-
flags2: (0x40009) -/-/INC
pid: 345 O/S info: user: oracle, term: UNKNOWN, ospid: 30752
image: oracle@danprvb404
client details:
O/S info: user: ELMSMNSV206$, term: ELMSMNSV206, ospid: 2036:6332
machine: AD-ENT\ELMSMNSV206 program: w3wp.exe
application name: w3wp.exe, hash value=2799981571
current SQL:
INSERT INTO TBL_TMX_ACTLINKREL (ACTIVITYFK, LINKEDACTFK, LINKTYPE, REFCOUNT ) VALUES ( :B3 , :B2 , :B1 , 1 )
DUMP LOCAL BLOCKER: initiate state dump for DEADLOCK
possible owner[345.30752] on resource TX-0023000F-000B3A3D
*** 2014-02-18 14:10:55.044
Submitting asynchronized dump request [28]. summary=[ges process stack dump (kjdglblkrdm1)].
DUMP LOCAL BLOCKER/HOLDER: block level 3 res [0x1b0018][0xdb83e],[TX][ext 0x2,0x0]
----------resource 0x8d9b702e8----------------------
resname : [0x1b0018][0xdb83e],[TX][ext 0x2,0x0]
hash mask : x3
Local inst : 2
dir_inst : 2
master_inst : 2
hv idx : 42
hv last r.inc : 2
current inc : 2
hv status : 0
hv master : 1
open options : dd
grant_bits : KJUSERNL KJUSEREX
grant mode : KJUSERNL KJUSERCR KJUSERCW KJUSERPR KJUSERPW KJUSEREX
count : 1 0 0 0 0 1
val_state : KJUSERVS_NOVALUE
valblk : 0x9cd3ef080000000080c361ddff7f0000 .a
access_inst : 2
vbreq_state : 0
state : x0
resp : 0x8d9b702e8
On Scan_q? : N
Total accesses: 9540
Imm. accesses: 7177
Granted_locks : 1
Cvting_locks : 1
value_block: 9c d3 ef 08 00 00 00 00 80 c3 61 dd ff 7f 00 00
GRANTED_Q :
lp 0x8c294b8e8 gl KJUSEREX rp 0x8d9b702e8 [0x1b0018][0xdb83e],[TX][ext 0x2,0x0]
master 2 gl owner 0x8c112b1d8 possible pid 24175 xid 3B000-0002-00000B0D bast 0 rseq 2258 mseq 0 history 0x14951495
open opt KJUSERDEADLOCK
CONVERT_Q:
lp 0x8c2983150 gl KJUSERNL rl KJUSERPR rp 0x8d9b702e8 [0x1b0018][0xdb83e],[TX][ext 0x2,0x0]
master 2 gl owner 0x8d90b6d68 possible pid 30752 xid 159000-0002-000000F4 bast 0 rseq 2258 mseq 0 history 0x1495149a
convert opt KJUSERGETVALUE
----------enqueue 0x8c294b8e8------------------------
lock version : 1673761
Owner inst : 2
grant_level : KJUSEREX
req_level : KJUSEREX
bast_level : KJUSERNL
notify_func : (nil)
resp : 0x8d9b702e8
procp : 0x8c18d02a0
pid : 24175
proc version : 499
oprocp : (nil)
opid : 24175
group lock owner : 0x8c112b1d8
possible pid : 24175
xid : 3B000-0002-00000B0D
dd_time : 0.0 secs
dd_count : 0
timeout : 0.0 secs
On_timer_q? : N
On_dd_q? : N
lock_state : GRANTED
ast_flag : 0x0
Open Options : KJUSERDEADLOCK
Convert options : KJUSERGETVALUE KJUSERNOQUEUE KJUSERNODEADLOCKWAIT
History : 0x14951495
Msg_Seq : 0x0
res_seq : 2258
valblk : 0x40c861ddff7f000080c561ddff7f0000 @aa
user session for deadlock lock 0x8c294b8e8
sid: 1394 ser: 14175 audsid: 16262574 user: 61/ELMS
flags: (0x8000045) USR/- flags_idl: (0x1) BSY/-/-/-/-/-
flags2: (0x40009) -/-/INC
pid: 59 O/S info: user: oracle, term: UNKNOWN, ospid: 24175
image: oracle@danprvb404
client details:
O/S info: user: ELMSMNSV201$, term: ELMSMNSV201, ospid: 4032:5404
machine: AD-ENT\ELMSMNSV201 program: w3wp.exe
application name: w3wp.exe, hash value=2799981571
current SQL:
DELETE FROM TBL_TMX_ACTIVITY WHERE ACTIVITY_PK = :B1
DUMP LOCAL BLOCKER: initiate state dump for DEADLOCK
possible owner[59.24175] on resource TX-001B0018-000DB83E
*** 2014-02-18 14:10:55.045
Submitting asynchronized dump request [28]. summary=[ges process stack dump (kjdglblkrdm1)].
Global blockers dump end:-----------------------------------
Global Wait-For-Graph(WFG) at ddTS[0.4879] :
BLOCKED 0x8c297cb28 3 wq 2 cvtops x1 TX 0x23000f.0xb3a3d(ext 0x2,0x0)[3B000-0002-00000B0D] inst 2
BLOCKER 0x8c296fd08 3 wq 1 cvtops x29 TX 0x23000f.0xb3a3d(ext 0x2,0x0)[159000-0002-000000F4] inst 2
BLOCKED 0x8c2983150 3 wq 2 cvtops x1 TX 0x1b0018.0xdb83e(ext 0x2,0x0)[159000-0002-000000F4] inst 2
BLOCKER 0x8c294b8e8 3 wq 1 cvtops x29 TX 0x1b0018.0xdb83e(ext 0x2,0x0)[3B000-0002-00000B0D] inst 2
*** 2014-02-18 14:10:55.875
* Cancel deadlock victim lockp 0x8c297cb28
I have already searched for this, but I was only finding information on trace file with Deadlock graph in it... I did not find any help on how to analyze the trace file with Wait-For-Graph.
