Hello,
I have a deadlock problem occuring when updating a table partitionned by hash on pk (8 partitions).
There is no FK.
I try to read the trace file to understand how this event occured but I have some difficulties.
I didn't find information as usual.
Has anyone an idea to read the following extract of trace file :
*** 2015-02-17 15:43:56.054
*** SESSION ID:(349.1) 2015-02-17 15:43:56.054
*** CLIENT ID:() 2015-02-17 15:43:56.054
*** SERVICE NAME:(SYS$BACKGROUND) 2015-02-17 15:43:56.054
*** MODULE NAME:() 2015-02-17 15:43:56.054
*** ACTION NAME:() 2015-02-17 15:43:56.054
*** TRACE FILE RECREATED AFTER BEING REMOVED ***
Global blockers dump start:---------------------------------
DUMP LOCAL BLOCKER/HOLDER: block level 3 res [0x150022][0x11960],[TX][ext 0x2,0x0]
----------resource 0xdabf9b30----------------------
resname : [0x150022][0x11960],[TX][ext 0x2,0x0]
hash mask : x3
Local inst : 1
dir_inst : 1
master_inst : 1
hv idx : 86
hv last r.inc : 0
current inc : 2
hv status : 0
hv master : 0
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 : 0x6003000000000000c829aadc00000000 `)
access_inst : 1
vbreq_state : 0
state : x0
resp : 0xdabf9b30
On Scan_q? : N
Total accesses: 668
Imm. accesses: 373
Granted_locks : 1
Cvting_locks : 1
value_block: 60 03 00 00 00 00 00 00 c8 29 aa dc 00 00 00 00
GRANTED_Q :
lp 0xd972bfb8 gl KJUSEREX rp 0xdabf9b30 [0x150022][0x11960],[TX][ext 0x2,0x0]
master 1 gl owner 0xdc8b4e00 possible pid 8174 xid 41000-0001-000061AA bast 0 rseq 57 mseq 0
history REF_RES > LOC_AST > CLOSE > FREE > REF_RES > LOC_AST
open opt KJUSERDEADLOCK
CONVERT_Q:
lp 0xd97a9a80 gl KJUSERNL rl KJUSERPR rp 0xdabf9b30 [0x150022][0x11960],[TX][ext 0x2,0x0]
master 1 gl owner 0xdfae5c00 possible pid 8178 xid 4B000-0001-00003C46 bast 0 rseq 57 mseq 0
history GR2CVT > CVT2GRCANCL > LOC_AST > FREE > REF_RES > GR2CVT
convert opt KJUSERGETVALUE
----------enqueue 0xd972bfb8------------------------
lock version : 20105
Owner inst : 1
grant_level : KJUSEREX
req_level : KJUSEREX
bast_level : KJUSERNL
notify_func : (nil)
resp : 0xdabf9b30
procp : 0xdb79c010
pid : 8176
proc version : 6095
oprocp : (nil)
opid : 8176
group lock owner : 0xdc8b4e00
possible pid : 8174
xid : 41000-0001-000061AA
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 : KJUSERNOQUEUE KJUSERNODEADLOCKWAIT
History : REF_RES > LOC_AST > CLOSE > FREE > REF_RES > LOC_AST
Msg_Seq : 0x0
res_seq : 57
valblk : 0x084e8c3cff7f00000003000000000000 .N<
user session for deadlock lock 0xd972bfb8
sid: 33 ser: 1737 audsid: 229606 user: 63/AML00BATCH
flags: (0x45) USR/- flags_idl: (0x1) BSY/-/-/-/-/-
flags2: (0x40009) -/-/INC
pid: 65 O/S info: user: grid, term: UNKNOWN, ospid: 8174
image: oracle@psp854
client details:
O/S info: user: pro8aml, term: , ospid: 5110
machine: psp482 program: osh@psp482 (TNS V1-V3)
application name: osh@psp482 (TNS V1-V3), hash value=516211629
current SQL:
UPDATE CLIENT_UNIQUE SET CUMULATIVE_VERSEMENT_UNIQUE=:CUMULATIVE_VERSEMENT_UNIQUE,CUMULATIVE_DECAISSEMENT_UNIQUE=:CUMULATIVE_DECAISSEMENT_UNIQUE,CON_DT_SOUSC=:CON_DT_SOUSC WHERE CUSTOMER_SOURCE_UNIQUE_ID=:CUSTOMER_SOURCE_UNIQUE_ID
DUMP LOCAL BLOCKER: initiate state dump for DEADLOCK
possible owner[65.8174] on resource TX-00150022-00011960
*** 2015-02-17 15:43:56.055
Submitting asynchronized dump request [1c]. summary=[ges process stack dump (kjdglblkrdm1)].
DUMP LOCAL BLOCKER/HOLDER: block level 3 res [0x9003e][0xb844],[TX][ext 0x2,0x0]
----------resource 0xdba9c1e0----------------------
resname : [0x9003e][0xb844],[TX][ext 0x2,0x0]
hash mask : x3
Local inst : 1
dir_inst : 1
master_inst : 1
hv idx : 86
hv last r.inc : 0
current inc : 2
hv status : 0
hv master : 0
open options : dd
grant_bits : KJUSERNL KJUSEREX
grant mode : KJUSERNL KJUSERCR KJUSERCW KJUSERPR KJUSERPW KJUSEREX
count : 2 0 0 0 0 1
val_state : KJUSERVS_NOVALUE
valblk : 0xb03e8c3cff7f000005f5e50200000000 .><
access_inst : 1
vbreq_state : 0
state : x0
resp : 0xdba9c1e0
On Scan_q? : N
Total accesses: 1189
Imm. accesses: 1028
Granted_locks : 1
Cvting_locks : 2
value_block: b0 3e 8c 3c ff 7f 00 00 05 f5 e5 02 00 00 00 00
GRANTED_Q :
lp 0xd92c46e0 gl KJUSEREX rp 0xdba9c1e0 [0x9003e][0xb844],[TX][ext 0x2,0x0]
master 1 gl owner 0xdfae5c00 possible pid 8178 xid 4B000-0001-00003C46 bast 0 rseq 118 mseq 0
history GR2CVT > CVT2GRCANCL > LOC_AST > FREE > REF_RES > LOC_AST
open opt KJUSERDEADLOCK
CONVERT_Q:
lp 0xd94b9500 gl KJUSERNL rl KJUSERPR rp 0xdba9c1e0 [0x9003e][0xb844],[TX][ext 0x2,0x0]
master 1 gl owner 0xdc9660c0 possible pid 8180 xid 45000-0001-000096D1 bast 0 rseq 118 mseq 0
history GR2CVT > CVT2GRCANCL > LOC_AST > FREE > REF_RES > GR2CVT
convert opt KJUSERGETVALUE
lp 0xd92c48c8 gl KJUSERNL rl KJUSERPR rp 0xdba9c1e0 [0x9003e][0xb844],[TX][ext 0x2,0x0]
master 1 gl owner 0xdc8b4e00 possible pid 8174 xid 41000-0001-000061AA bast 0 rseq 118 mseq 0
history GR2CVT > CVT2GRCANCL > LOC_AST > FREE > REF_RES > GR2CVT
convert opt KJUSERGETVALUE
----------enqueue 0xd92c46e0------------------------
lock version : 14657
Owner inst : 1
grant_level : KJUSEREX
req_level : KJUSEREX
bast_level : KJUSERNL
notify_func : (nil)
resp : 0xdba9c1e0
procp : 0xdb78e930
pid : 8174
proc version : 4522
oprocp : (nil)
opid : 8174
group lock owner : 0xdfae5c00
possible pid : 8178
xid : 4B000-0001-00003C46
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 : KJUSERNOQUEUE KJUSERNODEADLOCKWAIT
History : GR2CVT > CVT2GRCANCL > LOC_AST > FREE > REF_RES > LOC_AST
Msg_Seq : 0x0
res_seq : 118
valblk : 0x0000000000000000e02c970a00000000 .,
user session for deadlock lock 0xd92c46e0
sid: 322 ser: 13641 audsid: 229608 user: 63/AML00BATCH
flags: (0x45) USR/- flags_idl: (0x1) BSY/-/-/-/-/-
flags2: (0x40009) -/-/INC
pid: 75 O/S info: user: grid, term: UNKNOWN, ospid: 8178
image: oracle@psp854
client details:
O/S info: user: pro8aml, term: , ospid: 5113
machine: psp482 program: osh@psp482 (TNS V1-V3)
application name: osh@psp482 (TNS V1-V3), hash value=516211629
current SQL:
UPDATE CLIENT_UNIQUE SET CUMULATIVE_VERSEMENT_UNIQUE=:CUMULATIVE_VERSEMENT_UNIQUE,CUMULATIVE_DECAISSEMENT_UNIQUE=:CUMULATIVE_DECAISSEMENT_UNIQUE,CON_DT_SOUSC=:CON_DT_SOUSC WHERE CUSTOMER_SOURCE_UNIQUE_ID=:CUSTOMER_SOURCE_UNIQUE_ID
DUMP LOCAL BLOCKER: initiate state dump for DEADLOCK
possible owner[75.8178] on resource TX-0009003E-0000B844
*** 2015-02-17 15:43:56.056
Submitting asynchronized dump request [1c]. summary=[ges process stack dump (kjdglblkrdm1)].
Global blockers dump end:-----------------------------------
Global Wait-For-Graph(WFG) at ddTS[0.1] :
BLOCKED 0xd97a9a80 3 wq 2 cvtops x1 TX 0x150022.0x11960(ext 0x2,0x0)[4B000-0001-00003C46] inst 1
BLOCKER 0xd972bfb8 3 wq 1 cvtops x28 TX 0x150022.0x11960(ext 0x2,0x0)[41000-0001-000061AA] inst 1
BLOCKED 0xd92c48c8 3 wq 2 cvtops x1 TX 0x9003e.0xb844(ext 0x2,0x0)[41000-0001-000061AA] inst 1
BLOCKER 0xd92c46e0 3 wq 1 cvtops x28 TX 0x9003e.0xb844(ext 0x2,0x0)[4B000-0001-00003C46] inst 1
Regards,
Mohamed