Single resource deadlock: blocking enqueue which blocks itself, f 0
Single resource deadlock: blocking enqueue which blocks itself, f 0
Granted global enqueue 0xd8578490
----------enqueue 0xd8578490------------------------
lock version : 1
Owner inst : 2
grant_level : KJUSERCW
req_level : KJUSERPW
bast_level : KJUSERNL
notify_func : 0x4fe4b6e
resp : 0xd9c7ad50
procp : 0xd9971780
pid : 0
proc version : 0
oprocp : (nil)
opid : 0
group lock owner : (nil)
xid : 0000-0000-00000000
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 :
Convert options : KJUSERGETVALUE
History : 0xd7d8d7da
Msg_Seq : 0x60005
res_seq : 2
valblk : 0x00000000000000000000000000000000 .
DUMP LOCAL BLOCKER/HOLDER: block level 4 res [0x1451c][0x0],[TM][ext 0x0,0x0]
----------resource 0xd9c7ad50----------------------
resname : [0x1451c][0x0],[TM][ext 0x0,0x0]
hash mask : x3
Local inst : 1
dir_inst : 1
master_inst : 1
hv idx : 112
hv last r.inc : 56
current inc : 56
hv status : 0
hv master : 0
open options : dd cached
grant_bits : KJUSERCW
grant mode : KJUSERNL KJUSERCR KJUSERCW KJUSERPR KJUSERPW KJUSEREX
count : 0 0 2 0 0 0
val_state : KJUSERVS_NOVALUE
valblk : 0x00000000000000000000000000000000 .
access_inst : 1
vbreq_state : 0
state : x0
resp : 0xd9c7ad50
On Scan_q? : N
Total accesses: 150
Imm. accesses: 143
Granted_locks : 1
Cvting_locks : 1
value_block: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
GRANTED_Q :
lp 0xd876ab70 gl KJUSERCW rp 0xd9c7ad50 [0x1451c][0x0],[TM][ext 0x0,0x0]
master 1 gl owner 0xda7233c0 possible pid 1993 xid 2B000-0001-000000B5 bast 0 rseq 1 mseq 0 history 0x49a51495
open opt KJUSERDEADLOCK
CONVERT_Q:
lp 0xd8578490 gl KJUSERCW rl KJUSERPW rp 0xd9c7ad50 [0x1451c][0x0],[TM][ext 0x0,0x0]
master 1 owner 2 bast 1 rseq 2 mseq 0x60005 history 0xd7d8d7da
convert opt KJUSERGETVALUE
----------enqueue 0xd876ab70------------------------
lock version : 2071
Owner inst : 1
grant_level : KJUSERCW
req_level : KJUSERPW
bast_level : KJUSERNL
notify_func : (nil)
resp : 0xd9c7ad50
procp : 0xd9979b08
pid : 1993
proc version : 81
oprocp : (nil)
opid : 1993
group lock owner : 0xda7233c0
possible pid : 1993
xid : 2B000-0001-000000B5
dd_time : 0.0 secs
dd_count : 0
timeout : 0.0 secs
On_timer_q? : N
On_dd_q? : N
lock_state : CONVERTING
ast_flag : 0x0
Open Options : KJUSERDEADLOCK
Convert options : KJUSERGETVALUE
History : 0x49a51495
Msg_Seq : 0x0
res_seq : 1
valblk : 0x00000000000000000000000000000000 .
user session for deadlock lock 0xd876ab70
sid: 416 ser: 217 audsid: 2301258 user: 95/SPOT
flags: (0x10041) USR/- flags_idl: (0x1) BSY/-/-/-/-/-
flags2: (0x40008) -/-
pid: 43 O/S info: user: oracle, term: UNKNOWN, ospid: 1993
image: oracle@rh2.oracle.com (J000)
client details:
O/S info: user: oracle, term: UNKNOWN, ospid: 1993
machine: rh2.oracle.com program: oracle@rh2.oracle.com (J000)
application name: DBMS_SCHEDULER, hash value=2478762354
action name: QUEST_PPCM_JOB_PM_1, hash value=3637730750
current SQL:
DELETE FROM QUEST_PPCM_SNAPSHOT WHERE SNAPSHOT_TYPE = :B2 AND INSTANCE_ID > 0
AND SNAPSHOT_TIMESTAMP < TRUNC (SYSDATE) - :B1
----------enqueue 0xd8578490------------------------
lock version : 1
Owner inst : 2
grant_level : KJUSERCW
req_level : KJUSERPW
bast_level : KJUSERNL
notify_func : 0x4fe4b6e
resp : 0xd9c7ad50
procp : 0xd9971780
pid : 0
proc version : 0
oprocp : (nil)
opid : 0
group lock owner : (nil)
xid : 0000-0000-00000000
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 :
Convert options : KJUSERGETVALUE
History : 0xd7d8d7da
Msg_Seq : 0x60005
res_seq : 2
valblk : 0x03000000000000000100000000000000 .
Requesting global enqueue 0xd876ab70
----------enqueue 0xd876ab70------------------------
lock version : 2071
Owner inst : 1
grant_level : KJUSERCW
req_level : KJUSERPW
bast_level : KJUSERNL
notify_func : (nil)
resp : 0xd9c7ad50
procp : 0xd9979b08
pid : 1993
proc version : 81
oprocp : (nil)
opid : 1993
group lock owner : 0xda7233c0
possible pid : 1993
xid : 2B000-0001-000000B5
dd_time : 0.0 secs
dd_count : 0
timeout : 0.0 secs
On_timer_q? : N
On_dd_q? : N
lock_state : CONVERTING
ast_flag : 0x0
Open Options : KJUSERDEADLOCK
Convert options : KJUSERGETVALUE
History : 0x49a51495
Msg_Seq : 0x0
res_seq : 1
valblk : 0x00000000000000000000000000000000 . u
ser session for deadlock lock 0xd876ab70
sid: 416 ser: 217 audsid: 2301258 user: 95/SPOT
flags: (0x10041) USR/- flags_idl: (0x1) BSY/-/-/-/-/-
flags2: (0x40008) -/-
pid: 43 O/S info: user: oracle, term: UNKNOWN, ospid: 1993
image: oracle@rh2.oracle.com (J000)
client details: O/S info: user: oracle, term: UNKNOWN, ospid: 1993
machine: rh2.oracle.com program: oracle@rh2.oracle.com (J000)
application name: DBMS_SCHEDULER, hash value=2478762354
action name: QUEST_PPCM_JOB_PM_1, hash value=3637730750
current SQL:
DELETE FROM QUEST_PPCM_SNAPSHOT WHERE SNAPSHOT_TYPE = :B2 AND INSTANCE_ID > 0 AND SNAPSHOT_TIMESTAMP < TRUNC (SYSDATE) - :B1
----------resource 0xd9c7ad50----------------------
resname : [0x1451c][0x0],[TM][ext 0x0,0x0]
hash mask : x3
Local inst : 1
dir_inst : 1
master_inst : 1
hv idx : 112
hv last r.inc : 56
current inc : 56
hv status : 0
hv master : 0
open options : dd cached
grant_bits : KJUSERCW
grant mode : KJUSERNL KJUSERCR KJUSERCW KJUSERPR KJUSERPW KJUSEREX
count : 0 0 2 0 0 0
val_state : KJUSERVS_NOVALUE
valblk : 0x00000000000000000000000000000000 .
access_inst : 1
vbreq_state : 0
state : x0
resp : 0xd9c7ad50
On Scan_q? : N
Total accesses: 150
Imm. accesses: 143
Granted_locks : 1
Cvting_locks : 1
value_block: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
GRANTED_Q :
lp 0xd876ab70 gl KJUSERCW rp 0xd9c7ad50 [0x1451c][0x0],[TM][ext 0x0,0x0]
master 1 gl owner 0xda7233c0 possible pid 1993 xid 2B000-0001-000000B5 bast 0 rseq 1 mseq 0 history 0x49a51495
open opt KJUSERDEADLOCK
CONVERT_Q:
lp 0xd8578490 gl KJUSERCW rl KJUSERPW rp 0xd9c7ad50 [0x1451c][0x0],[TM][ext 0x0,0x0]
master 1 owner 2 bast 1 rseq 2 mseq 0x60005 history 0xd7d8d7da
convert opt KJUSERGETVALUE
-------------------------------------------------------------------------------
Trace Bucket Dump Begin: default bucket for process 43 (osid: 1993, J000)
TIME(*=approx):SEQ:COMPONENT:FILE@LINE:FUNCTION:SECT/DUMP: [EVENT#:PID:SID] DATA
TM DEADLOCK IN RAC:
* End DRM for pkey remastering request(s) (locally requested)
ENQUEUE DUMP REQUEST: from 2 spnum 12 on [0x1475a][0x0],[TM][ext 0x0,0x0] for reason 3 mtype 0
*** 2011-06-21 21:57:42.212
DUMP LOCAL BLOCKER/HOLDER: block level 5 res [0x1475a][0x0],[TM][ext 0x0,0x0]
----------resource 0xd9e62b48----------------------
resname : [0x1475a][0x0],[TM][ext 0x0,0x0]
hash mask : x3
Local inst : 1
dir_inst : 1
master_inst : 1
hv idx : 46
hv last r.inc : 56
current inc : 56
hv status : 0
hv master : 0
open options : dd cached
grant_bits : KJUSERNL KJUSEREX
grant mode : KJUSERNL KJUSERCR KJUSERCW KJUSERPR KJUSERPW KJUSEREX
count : 1 0 0 0 0 1
val_state : KJUSERVS_NOVALUE
valblk : 0x00000000000000000000000000000000 .
access_inst : 1
vbreq_state : 0
state : x0
resp : 0xd9e62b48
On Scan_q? : N
Total accesses: 30
Imm. accesses: 25
Granted_locks : 1
Cvting_locks : 1
value_block: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
GRANTED_Q :
lp 0xd87530a8 gl KJUSEREX rp 0xd9e62b48 [0x1475a][0x0],[TM][ext 0x0,0x0]
master 1 gl owner 0xda7233c0 possible pid 26255 xid 2B000-0001-00000576 bast 0 rseq 3 mseq 0 history 0x9a514495
open opt KJUSERDEADLOCK
CONVERT_Q:
lp 0xd85b60e0 gl KJUSERNL rl KJUSEREX rp 0xd9e62b48 [0x1475a][0x0],[TM][ext 0x0,0x0]
master 1 owner 2 bast 0 rseq 12 mseq 0x1 history 0x97ad
convert opt KJUSERGETVALUE
----------enqueue 0xd87530a8------------------------
lock version : 10199
Owner inst : 1
grant_level : KJUSEREX
req_level : KJUSEREX
bast_level : KJUSERNL
notify_func : (nil)
resp : 0xd9e62b48
procp : 0xd99750b0
pid : 26255
proc version : 229
oprocp : (nil)
opid : 26255
group lock owner : 0xda7233c0
possible pid : 26255
xid : 2B000-0001-00000576
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
History : 0x9a514495
Msg_Seq : 0x0
res_seq : 3
valblk : 0x00000000000000000000000000000000 .
user session for deadlock lock 0xd87530a8
sid: 416 ser: 1053 audsid: 4294967295 user: 0/SYS
flags: (0x41) USR/- flags_idl: (0x1) BSY/-/-/-/-/-
flags2: (0x40008) -/-
pid: 43 O/S info: user: oracle, term: UNKNOWN, ospid: 26255
image: oracle@rh2.oracle.com (TNS V1-V3)
client details:
O/S info: user: oracle, term: pts/3, ospid: 26254
machine: rh2.oracle.com program: sqlplus@rh2.oracle.com (TNS V1-V3)
application name: sqlplus@rh2.oracle.com (TNS V1-V3), hash value=3660119738
current SQL:
lock table lock2 in exclusive mode
DUMP LOCAL BLOCKER: initiate state dump for DEADLOCK
possible owner[43.26255] on resource TM-0001475A-00000000
*** 2011-06-21 21:57:42.215
Submitting asynchronized dump request [28]. summary=[ges process stack dump (kjdglblkrdm1)].
----------enqueue 0xd85b60e0------------------------
lock version : 1
Owner inst : 2
grant_level : KJUSERNL
req_level : KJUSEREX
bast_level : KJUSERNL
notify_func : (nil)
resp : 0xd9e62b48
procp : 0xd9971780
pid : 0
proc version : 0
oprocp : (nil)
opid : 0
group lock owner : (nil)
xid : 0000-0000-00000000
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 : KJUSERNO_XID
Convert options : KJUSERGETVALUE
History : 0x97ad
Msg_Seq : 0x1
res_seq : 12
valblk : 0xe0720660ff7f000020700660ff7f0000 .r` p`
Global blockers dump start:---------------------------------
DUMP LOCAL BLOCKER/HOLDER: block level 5 res [0x1485a][0x0],[TM][ext 0x0,0x0]
----------resource 0xd9c5a7c8----------------------
resname : [0x1485a][0x0],[TM][ext 0x0,0x0]
hash mask : x3
Local inst : 1
dir_inst : 1
master_inst : 1
hv idx : 46
hv last r.inc : 56
current inc : 56
hv status : 0
hv master : 0
open options : dd cached
grant_bits : KJUSERNL KJUSEREX
grant mode : KJUSERNL KJUSERCR KJUSERCW KJUSERPR KJUSERPW KJUSEREX
count : 1 0 0 0 0 1
val_state : KJUSERVS_NOVALUE
valblk : 0x000000000a0000000a00000001000000 .
access_inst : 2
vbreq_state : 0
state : x0
resp : 0xd9c5a7c8
On Scan_q? : N
Total accesses: 29
Imm. accesses: 20
Granted_locks : 1
Cvting_locks : 1
value_block: 00 00 00 00 0a 00 00 00 0a 00 00 00 01 00 00 00
GRANTED_Q :
lp 0xd85b5f20 gl KJUSEREX rp 0xd9c5a7c8 [0x1485a][0x0],[TM][ext 0x0,0x0]
master 1 owner 2 bast 0 rseq 15 mseq 0x2 history 0x977d8d
open opt KJUSERNO_XID
CONVERT_Q:
lp 0xd8757ff8 gl KJUSERNL rl KJUSEREX rp 0xd9c5a7c8 [0x1485a][0x0],[TM][ext 0x0,0x0]
master 1 gl owner 0xda7233c0 possible pid 26255 xid 2B000-0001-00000576 bast 0 rseq 2 mseq 0 history 0xda51449a
convert opt KJUSERGETVALUE
----------enqueue 0xd85b5f20------------------------
lock version : 1
Owner inst : 2
grant_level : KJUSEREX
req_level : KJUSEREX
bast_level : KJUSEREX
notify_func : (nil)
resp : 0xd9c5a7c8
procp : 0xd9971780
pid : 0
proc version : 0
oprocp : (nil)
opid : 0
group lock owner : (nil)
xid : 0000-0000-00000000
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 : KJUSERNO_XID
Convert options : KJUSERGETVALUE
History : 0x977d8d
Msg_Seq : 0x2
res_seq : 15
valblk : 0x00000000000000000000000000000000 .
----------enqueue 0xd8757ff8------------------------
lock version : 10261
Owner inst : 1
grant_level : KJUSERNL
req_level : KJUSEREX
bast_level : KJUSERNL
notify_func : (nil)
resp : 0xd9c5a7c8
procp : 0xd99750b0
pid : 26255
proc version : 229
oprocp : (nil)
opid : 26255
group lock owner : 0xda7233c0
possible pid : 26255
xid : 2B000-0001-00000576
dd_time : 10.0 secs
dd_count : 1
timeout : 0.0 secs
On_timer_q? : N
On_dd_q? : Y
lock_state : OPENING CONVERTING
ast_flag : 0x0
Open Options : KJUSERDEADLOCK
Convert options : KJUSERGETVALUE
History : 0xda51449a
Msg_Seq : 0x0
res_seq : 2
valblk : 0x00000000000000000000000000000000 .
user session for deadlock lock 0xd8757ff8
sid: 416 ser: 1053 audsid: 4294967295 user: 0/SYS
flags: (0x41) USR/- flags_idl: (0x1) BSY/-/-/-/-/-
flags2: (0x40008) -/-
pid: 43 O/S info: user: oracle, term: UNKNOWN, ospid: 26255
image: oracle@rh2.oracle.com (TNS V1-V3)
client details:
O/S info: user: oracle, term: pts/3, ospid: 26254
machine: rh2.oracle.com program: sqlplus@rh2.oracle.com (TNS V1-V3)
application name: sqlplus@rh2.oracle.com (TNS V1-V3), hash value=3660119738
current SQL:
lock table lock2 in exclusive mode
DUMP LOCAL BLOCKER: initiate state dump for DEADLOCK
possible owner[43.26255] on resource TM-0001485A-00000000
*** 2011-06-21 21:57:42.219
Submitting asynchronized dump request [28]. summary=[ges process stack dump (kjdglblkrdm1)].
DUMP LOCAL BLOCKER/HOLDER: block level 5 res [0x1475a][0x0],[TM][ext 0x0,0x0]
----------resource 0xd9e62b48----------------------
resname : [0x1475a][0x0],[TM][ext 0x0,0x0]
hash mask : x3
Local inst : 1
dir_inst : 1
master_inst : 1
hv idx : 46
hv last r.inc : 56
current inc : 56
hv status : 0
hv master : 0
open options : dd cached
grant_bits : KJUSERNL KJUSEREX
grant mode : KJUSERNL KJUSERCR KJUSERCW KJUSERPR KJUSERPW KJUSEREX
count : 1 0 0 0 0 1
val_state : KJUSERVS_NOVALUE
valblk : 0x7c5b5c0900000000806a0660ff7f0000 |[\j`
access_inst : 1
vbreq_state : 0
state : x0
resp : 0xd9e62b48
On Scan_q? : N
Total accesses: 32
Imm. accesses: 26
Granted_locks : 1
Cvting_locks : 1
value_block: 7c 5b 5c 09 00 00 00 00 80 6a 06 60 ff 7f 00 00
GRANTED_Q :
lp 0xd87530a8 gl KJUSEREX rp 0xd9e62b48 [0x1475a][0x0],[TM][ext 0x0,0x0]
master 1 gl owner 0xda7233c0 possible pid 26255 xid 2B000-0001-00000576 bast 0 rseq 3 mseq 0 history 0x9a514495
open opt KJUSERDEADLOCK
CONVERT_Q:
lp 0xd85b60e0 gl KJUSERNL rl KJUSEREX rp 0xd9e62b48 [0x1475a][0x0],[TM][ext 0x0,0x0]
master 1 owner 2 bast 0 rseq 12 mseq 0x1 history 0x97ad
convert opt KJUSERGETVALUE
----------enqueue 0xd87530a8------------------------
lock version : 10199
Owner inst : 1
grant_level : KJUSEREX
req_level : KJUSEREX
bast_level : KJUSERNL
notify_func : (nil)
resp : 0xd9e62b48
procp : 0xd99750b0
pid : 26255
proc version : 229
oprocp : (nil)
opid : 26255
group lock owner : 0xda7233c0
possible pid : 26255
xid : 2B000-0001-00000576
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
History : 0x9a514495
Msg_Seq : 0x0
res_seq : 3
valblk : 0x00000000ff7f000031000502ff7f0000 .1
user session for deadlock lock 0xd87530a8
sid: 416 ser: 1053 audsid: 4294967295 user: 0/SYS
flags: (0x41) USR/- flags_idl: (0x1) BSY/-/-/-/-/-
flags2: (0x40008) -/-
pid: 43 O/S info: user: oracle, term: UNKNOWN, ospid: 26255
image: oracle@rh2.oracle.com (TNS V1-V3)
client details:
O/S info: user: oracle, term: pts/3, ospid: 26254
machine: rh2.oracle.com program: sqlplus@rh2.oracle.com (TNS V1-V3)
application name: sqlplus@rh2.oracle.com (TNS V1-V3), hash value=3660119738
current SQL:
lock table lock2 in exclusive mode
DUMP LOCAL BLOCKER: initiate state dump for DEADLOCK
possible owner[43.26255] on resource TM-0001475A-00000000
*** 2011-06-21 21:57:42.220
Submitting asynchronized dump request [28]. summary=[ges process stack dump (kjdglblkrdm1)].
----------enqueue 0xd85b60e0------------------------
lock version : 1
Owner inst : 2
grant_level : KJUSERNL
req_level : KJUSEREX
bast_level : KJUSERNL
notify_func : (nil)
resp : 0xd9e62b48
procp : 0xd9971780
pid : 0
proc version : 0
oprocp : (nil)
opid : 0
group lock owner : (nil)
xid : 0000-0000-00000000
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 : KJUSERNO_XID
Convert options : KJUSERGETVALUE
History : 0x97ad
Msg_Seq : 0x1
res_seq : 12
valblk : 0x406f0660ff7f0000806c0660ff7f0000 @o`l`
Global blockers dump end:-----------------------------------
Global Wait-For-Graph(WFG) at ddTS[0.1] :
BLOCKED 0xd8757ff8 5 wq 2 cvtops x1 TM 0x1485a.0x0(ext 0x0,0x0)[2B000-0001-00000576] inst 1
BLOCKER 0xd87520d0 5 wq 1 cvtops x1 TM 0x1485a.0x0(ext 0x0,0x0)[22000-0002-000010C6] inst 2
BLOCKED 0xd8766338 5 wq 2 cvtops x1 TM 0x1475a.0x0(ext 0x0,0x0)[22000-0002-000010C6] inst 2
BLOCKER 0xd87530a8 5 wq 1 cvtops x1 TM 0x1475a.0x0(ext 0x0,0x0)[2B000-0001-00000576] inst 1
TX DEADLOCK in RAC:
ENQUEUE DUMP REQUEST: from 2 spnum 12 on [0x10001][0x7b3],[TX][ext 0x2,0x0] for reason 3 mtype 0
DUMP LOCAL BLOCKER/HOLDER: block level 5 res [0x10001][0x7b3],[TX][ext 0x2,0x0]
----------resource 0xd9c5a6a0----------------------
resname : [0x10001][0x7b3],[TX][ext 0x2,0x0]
hash mask : x3
Local inst : 1
dir_inst : 1
master_inst : 1
hv idx : 8
hv last r.inc : 56
current inc : 56
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 : 0x00000000000000000000000000000000 .
access_inst : 1
vbreq_state : 0
state : x0
resp : 0xd9c5a6a0
On Scan_q? : N
Total accesses: 54
Imm. accesses: 44
Granted_locks : 1
Cvting_locks : 1
value_block: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
GRANTED_Q :
lp 0xd85b62a0 gl KJUSEREX rp 0xd9c5a6a0 [0x10001][0x7b3],[TX][ext 0x2,0x0]
master 1 gl owner 0xda7233c0 possible pid 26287 xid 2B000-0001-00000578 bast 0 rseq 8 mseq 0 history 0x95
open opt KJUSERDEADLOCK
CONVERT_Q:
lp 0xd85b6460 gl KJUSERNL rl KJUSEREX rp 0xd9c5a6a0 [0x10001][0x7b3],[TX][ext 0x2,0x0]
master 1 owner 2 bast 0 rseq 22 mseq 0x1 history 0x97ad
convert opt KJUSERGETVALUE
----------enqueue 0xd85b62a0------------------------
lock version : 1
Owner inst : 1
grant_level : KJUSEREX
req_level : KJUSEREX
bast_level : KJUSERNL
notify_func : (nil)
resp : 0xd9c5a6a0
procp : 0xd996b090
pid : 22148
proc version : 0
oprocp : (nil)
opid : 22148
group lock owner : 0xda7233c0
possible pid : 26287
xid : 2B000-0001-00000578
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 : 0x95
Msg_Seq : 0x0
res_seq : 8
valblk : 0x00000000000000000000000000000000 .
user session for deadlock lock 0xd85b62a0
sid: 416 ser: 1055 audsid: 4294967295 user: 0/SYS
flags: (0x41) USR/- flags_idl: (0x1) BSY/-/-/-/-/-
flags2: (0x40008) -/-
pid: 43 O/S info: user: oracle, term: UNKNOWN, ospid: 26287
image: oracle@rh2.oracle.com (TNS V1-V3)
client details:
O/S info: user: oracle, term: pts/3, ospid: 26286
machine: rh2.oracle.com program: sqlplus@rh2.oracle.com (TNS V1-V3)
application name: sqlplus@rh2.oracle.com (TNS V1-V3), hash value=3660119738
current SQL:
update lock1 set t1=t1+10 where t1=2
DUMP LOCAL BLOCKER: initiate state dump for DEADLOCK
possible owner[43.26287] on resource TX-00010001-000007B3
*** 2011-06-21 22:08:18.048
Submitting asynchronized dump request [28]. summary=[ges process stack dump (kjdglblkrdm1)].
----------enqueue 0xd85b6460------------------------
lock version : 1
Owner inst : 2
grant_level : KJUSERNL
req_level : KJUSEREX
bast_level : KJUSERNL
notify_func : (nil)
resp : 0xd9c5a6a0
procp : 0xd9971780
pid : 0
proc version : 0
oprocp : (nil)
opid : 0
group lock owner : (nil)
xid : 0000-0000-00000000
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 : KJUSERNO_XID
Convert options : KJUSERGETVALUE
History : 0x97ad
Msg_Seq : 0x1
res_seq : 22
valblk : 0xe0720660ff7f000020700660ff7f0000 .r` p`
Global blockers dump start:---------------------------------
DUMP LOCAL BLOCKER/HOLDER: block level 5 res [0xb0000][0xc8],[TX][ext 0x5,0x0]
----------resource 0xd9e626a8----------------------
resname : [0xb0000][0xc8],[TX][ext 0x5,0x0]
hash mask : x3
Local inst : 1
dir_inst : 2
master_inst : 2
hv idx : 28
hv last r.inc : 56
current inc : 56
hv status : 0
hv master : 0
open options : dd
Held mode : KJUSERNL
Cvt mode : KJUSEREX
Next Cvt mode : KJUSERNL
msg_seq : 0x1
res_seq : 2
grant_bits : KJUSERNL
grant mode : KJUSERNL KJUSERCR KJUSERCW KJUSERPR KJUSERPW KJUSEREX
count : 1 0 0 0 0 0
val_state : KJUSERVS_NOVALUE
valblk : 0x000000000a0000000a00000001000000 .
access_inst : 2
vbreq_state : 0
state : x8
resp : 0xd9e626a8
On Scan_q? : N
Total accesses: 24
Imm. accesses: 15
Granted_locks : 0
Cvting_locks : 1
value_block: 00 00 00 00 0a 00 00 00 0a 00 00 00 01 00 00 00
GRANTED_Q :
CONVERT_Q:
lp 0xd8757ff8 gl KJUSERNL rl KJUSEREX rp 0xd9e626a8 [0xb0000][0xc8],[TX][ext 0x5,0x0]
master 2 gl owner 0xda7233c0 possible pid 26287 xid 2B000-0001-00000578 bast 0 rseq 2 mseq 0 history 0x495149da
convert opt KJUSERGETVALUE
----------enqueue 0xd8757ff8------------------------
lock version : 11019
Owner inst : 1
grant_level : KJUSERNL
req_level : KJUSEREX
bast_level : KJUSERNL
notify_func : (nil)
resp : 0xd9e626a8
procp : 0xd99750b0
pid : 26287
proc version : 230
oprocp : (nil)
opid : 26287
group lock owner : 0xda7233c0
possible pid : 26287
xid : 2B000-0001-00000578
dd_time : 10.0 secs
dd_count : 1
timeout : 0.0 secs
On_timer_q? : N
On_dd_q? : Y
lock_state : OPENING CONVERTING
ast_flag : 0x0
Open Options : KJUSERDEADLOCK
Convert options : KJUSERGETVALUE
History : 0x495149da
Msg_Seq : 0x0
res_seq : 2
valblk : 0x00000000000000000000000000000000 .
user session for deadlock lock 0xd8757ff8
sid: 416 ser: 1055 audsid: 4294967295 user: 0/SYS
flags: (0x41) USR/- flags_idl: (0x1) BSY/-/-/-/-/-
flags2: (0x40008) -/-
pid: 43 O/S info: user: oracle, term: UNKNOWN, ospid: 26287
image: oracle@rh2.oracle.com (TNS V1-V3)
client details:
O/S info: user: oracle, term: pts/3, ospid: 26286
machine: rh2.oracle.com program: sqlplus@rh2.oracle.com (TNS V1-V3)
application name: sqlplus@rh2.oracle.com (TNS V1-V3), hash value=3660119738
current SQL:
update lock1 set t1=t1+10 where t1=2
DUMP LOCAL BLOCKER: initiate state dump for DEADLOCK
possible owner[43.26287] on resource TX-000B0000-000000C8
*** 2011-06-21 22:08:18.051
Submitting asynchronized dump request [28]. summary=[ges process stack dump (kjdglblkrdm1)].
DUMP LOCAL BLOCKER/HOLDER: block level 5 res [0x10001][0x7b3],[TX][ext 0x2,0x0]
----------resource 0xd9c5a6a0----------------------
resname : [0x10001][0x7b3],[TX][ext 0x2,0x0]
hash mask : x3
Local inst : 1
dir_inst : 1
master_inst : 1
hv idx : 8
hv last r.inc : 56
current inc : 56
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 : 0x7c5b5c0900000000806a0660ff7f0000 |[\j`
access_inst : 1
vbreq_state : 0
state : x0
resp : 0xd9c5a6a0
On Scan_q? : N
Total accesses: 56
Imm. accesses: 45
Granted_locks : 1
Cvting_locks : 1
value_block: 7c 5b 5c 09 00 00 00 00 80 6a 06 60 ff 7f 00 00
GRANTED_Q :
lp 0xd85b62a0 gl KJUSEREX rp 0xd9c5a6a0 [0x10001][0x7b3],[TX][ext 0x2,0x0]
master 1 gl owner 0xda7233c0 possible pid 26287 xid 2B000-0001-00000578 bast 0 rseq 8 mseq 0 history 0x95
open opt KJUSERDEADLOCK
CONVERT_Q:
lp 0xd85b6460 gl KJUSERNL rl KJUSEREX rp 0xd9c5a6a0 [0x10001][0x7b3],[TX][ext 0x2,0x0]
master 1 owner 2 bast 0 rseq 22 mseq 0x1 history 0x97ad
convert opt KJUSERGETVALUE
----------enqueue 0xd85b62a0------------------------
lock version : 1
Owner inst : 1
grant_level : KJUSEREX
req_level : KJUSEREX
bast_level : KJUSERNL
notify_func : (nil)
resp : 0xd9c5a6a0
procp : 0xd996b090
pid : 22148
proc version : 0
oprocp : (nil)
opid : 22148
group lock owner : 0xda7233c0
possible pid : 26287
xid : 2B000-0001-00000578
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 : 0x95
Msg_Seq : 0x0
res_seq : 8
valblk : 0x00000000ff7f000031000502ff7f0000 .1
user session for deadlock lock 0xd85b62a0
sid: 416 ser: 1055 audsid: 4294967295 user: 0/SYS
flags: (0x41) USR/- flags_idl: (0x1) BSY/-/-/-/-/-
flags2: (0x40008) -/-
pid: 43 O/S info: user: oracle, term: UNKNOWN, ospid: 26287
image: oracle@rh2.oracle.com (TNS V1-V3)
client details:
O/S info: user: oracle, term: pts/3, ospid: 26286
machine: rh2.oracle.com program: sqlplus@rh2.oracle.com (TNS V1-V3)
application name: sqlplus@rh2.oracle.com (TNS V1-V3), hash value=3660119738
current SQL:
update lock1 set t1=t1+10 where t1=2
DUMP LOCAL BLOCKER: initiate state dump for DEADLOCK
possible owner[43.26287] on resource TX-00010001-000007B3
*** 2011-06-21 22:08:18.053
Submitting asynchronized dump request [28]. summary=[ges process stack dump (kjdglblkrdm1)].
----------enqueue 0xd85b6460------------------------
lock version : 1
Owner inst : 2
grant_level : KJUSERNL
req_level : KJUSEREX
bast_level : KJUSERNL
notify_func : (nil)
resp : 0xd9c5a6a0
procp : 0xd9971780
pid : 0
proc version : 0
oprocp : (nil)
opid : 0
group lock owner : (nil)
xid : 0000-0000-00000000
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 : KJUSERNO_XID
Convert options : KJUSERGETVALUE
History : 0x97ad
Msg_Seq : 0x1
res_seq : 22
valblk : 0x406f0660ff7f0000806c0660ff7f0000 @o`l`
Global blockers dump end:-----------------------------------
Global Wait-For-Graph(WFG) at ddTS[0.6] :
BLOCKED 0xd8757ff8 5 wq 2 cvtops x1 TX 0xb0000.0xc8(ext 0x5,0x0)[2B000-0001-00000578] inst 1
BLOCKER 0xd8561ee0 5 wq 1 cvtops x28 TX 0xb0000.0xc8(ext 0x5,0x0)[22000-0002-000010C6] inst 2
BLOCKED 0xd8766338 5 wq 2 cvtops x1 TX 0x10001.0x7b3(ext 0x2,0x0)[22000-0002-000010C6] inst 2
BLOCKER 0xd85b62a0 5 wq 1 cvtops x28 TX 0x10001.0x7b3(ext 0x2,0x0)[2B000-0001-00000578] inst 1
*** 2011-06-21 22:08:19.059
* Cancel deadlock victim lockp 0xd8757ff8
TX DEADLOCK LOCAL only:
*** 2011-06-21 22:27:00.022
DUMP LOCAL BLOCKER/HOLDER: block level 5 res [0x70015][0x81e],[TX][ext 0x2,0x0]
----------resource 0xd9e62330----------------------
resname : [0x70015][0x81e],[TX][ext 0x2,0x0]
hash mask : x3
Local inst : 1
dir_inst : 1
master_inst : 1
hv idx : 7
hv last r.inc : 42
current inc : 56
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 : 0x506b0660ff7f00006d6cf50400000000 Pk`ml
access_inst : 1
vbreq_state : 0
state : x0
resp : 0xd9e62330
On Scan_q? : N
Total accesses: 23
Imm. accesses: 15
Granted_locks : 1
Cvting_locks : 1
value_block: 50 6b 06 60 ff 7f 00 00 6d 6c f5 04 00 00 00 00
GRANTED_Q :
lp 0xd8767a10 gl KJUSEREX rp 0xd9e62330 [0x70015][0x81e],[TX][ext 0x2,0x0]
master 1 gl owner 0xda2cff40 possible pid 26847 xid 2E000-0001-00000347 bast 0 rseq 1 mseq 0 history 0x14951495
open opt KJUSERDEADLOCK
CONVERT_Q:
lp 0xd876a630 gl KJUSERNL rl KJUSEREX rp 0xd9e62330 [0x70015][0x81e],[TX][ext 0x2,0x0]
master 1 gl owner 0xda7233c0 possible pid 26843 xid 2B000-0001-0000057A bast 0 rseq 1 mseq 0 history 0x1495149a
convert opt KJUSERGETVALUE
----------enqueue 0xd8767a10------------------------
lock version : 8523
Owner inst : 1
grant_level : KJUSEREX
req_level : KJUSEREX
bast_level : KJUSERNL
notify_func : (nil)
resp : 0xd9e62330
procp : 0xd99750b0
pid : 26843
proc version : 231
oprocp : (nil)
opid : 26843
group lock owner : 0xda2cff40
possible pid : 26847
xid : 2E000-0001-00000347
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 : 0x14951495
Msg_Seq : 0x0
res_seq : 1
valblk : 0x00000000000000000000000000000000 .
user session for deadlock lock 0xd8767a10
sid: 16 ser: 851 audsid: 4294967295 user: 0/SYS
flags: (0x41) USR/- flags_idl: (0x1) BSY/-/-/-/-/-
flags2: (0x40008) -/-
pid: 46 O/S info: user: oracle, term: UNKNOWN, ospid: 26847
image: oracle@rh2.oracle.com (TNS V1-V3)
client details:
O/S info: user: oracle, term: pts/3, ospid: 26846
machine: rh2.oracle.com program: sqlplus@rh2.oracle.com (TNS V1-V3)
application name: sqlplus@rh2.oracle.com (TNS V1-V3), hash value=3660119738
current SQL:
update lock1 set t1=t1+10 where t1=11
DUMP LOCAL BLOCKER: initiate state dump for DEADLOCK
possible owner[46.26847] on resource TX-00070015-0000081E
*** 2011-06-21 22:27:00.024
Submitting asynchronized dump request [28]. summary=[ges process stack dump (kjdglblkrdm1)].
----------enqueue 0xd876a630------------------------
lock version : 9399
Owner inst : 1
grant_level : KJUSERNL
req_level : KJUSEREX
bast_level : KJUSERNL
notify_func : (nil)
resp : 0xd9e62330
procp : 0xd99750b0
pid : 26843
proc version : 231
oprocp : (nil)
opid : 26843
group lock owner : 0xda7233c0
possible pid : 26843
xid : 2B000-0001-0000057A
dd_time : 10.0 secs
dd_count : 1
timeout : 0.0 secs
On_timer_q? : N
On_dd_q? : Y
lock_state : OPENING CONVERTING
ast_flag : 0x0
Open Options : KJUSERDEADLOCK
Convert options : KJUSERGETVALUE
History : 0x1495149a
Msg_Seq : 0x0
res_seq : 1
valblk : 0xa06e0660ff7f0000e06b0660ff7f0000 .n`k`
user session for deadlock lock 0xd876a630
sid: 416 ser: 1057 audsid: 4294967295 user: 0/SYS
flags: (0x41) USR/- flags_idl: (0x1) BSY/-/-/-/-/-
flags2: (0x40008) -/-
pid: 43 O/S info: user: oracle, term: UNKNOWN, ospid: 26843
image: oracle@rh2.oracle.com (TNS V1-V3)
client details:
O/S info: user: oracle, term: pts/4, ospid: 26842
machine: rh2.oracle.com program: sqlplus@rh2.oracle.com (TNS V1-V3)
application name: sqlplus@rh2.oracle.com (TNS V1-V3), hash value=3660119738
current SQL:
update lock1 set t1=t1+10 where t1=12
DUMP LOCAL BLOCKER: initiate state dump for DEADLOCK
possible owner[43.26843] on resource TX-00070015-0000081E
*** 2011-06-21 22:27:00.025
Submitting asynchronized dump request [28]. summary=[ges process stack dump (kjdglblkrdm1)].
DUMP LOCAL BLOCKER/HOLDER: block level 5 res [0x40008][0x7d9],[TX][ext 0x2,0x0]
----------resource 0xd9e62208----------------------
resname : [0x40008][0x7d9],[TX][ext 0x2,0x0]
hash mask : x3
Local inst : 1
dir_inst : 1
master_inst : 1
hv idx : 53
hv last r.inc : 42
current inc : 56
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 : 0x7c5b5c0900000000e0690660ff7f0000 |[\i`
access_inst : 1
vbreq_state : 0
state : x0
resp : 0xd9e62208
On Scan_q? : N
Total accesses: 92
Imm. accesses: 82
Granted_locks : 1
Cvting_locks : 1
value_block: 7c 5b 5c 09 00 00 00 00 e0 69 06 60 ff 7f 00 00
GRANTED_Q :
lp 0xd876a7f0 gl KJUSEREX rp 0xd9e62208 [0x40008][0x7d9],[TX][ext 0x2,0x0]
master 1 gl owner 0xda7233c0 possible pid 26843 xid 2B000-0001-0000057A bast 0 rseq 6 mseq 0 history 0x14951495
open opt KJUSERDEADLOCK
CONVERT_Q:
lp 0xd876ab70 gl KJUSERNL rl KJUSEREX rp 0xd9e62208 [0x40008][0x7d9],[TX][ext 0x2,0x0]
master 1 gl owner 0xda2cff40 possible pid 26847 xid 2E000-0001-00000347 bast 0 rseq 6 mseq 0 history 0x1495149a
convert opt KJUSERGETVALUE
----------enqueue 0xd876a7f0------------------------
lock version : 6107
Owner inst : 1
grant_level : KJUSEREX
req_level : KJUSEREX
bast_level : KJUSERNL
notify_func : (nil)
resp : 0xd9e62208
procp : 0xd9978428
pid : 26847
proc version : 238
oprocp : (nil)
opid : 26847
group lock owner : 0xda7233c0
possible pid : 26843
xid : 2B000-0001-0000057A
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 : 0x14951495
Msg_Seq : 0x0
res_seq : 6
valblk : 0x00000000ff7f000031000502ff7f0000 .1
user session for deadlock lock 0xd876a7f0
sid: 416 ser: 1057 audsid: 4294967295 user: 0/SYS
flags: (0x41) USR/- flags_idl: (0x1) BSY/-/-/-/-/-
flags2: (0x40008) -/-
pid: 43 O/S info: user: oracle, term: UNKNOWN, ospid: 26843
image: oracle@rh2.oracle.com (TNS V1-V3)
client details:
O/S info: user: oracle, term: pts/4, ospid: 26842
machine: rh2.oracle.com program: sqlplus@rh2.oracle.com (TNS V1-V3)
application name: sqlplus@rh2.oracle.com (TNS V1-V3), hash value=3660119738
current SQL:
update lock1 set t1=t1+10 where t1=12
DUMP LOCAL BLOCKER: initiate state dump for DEADLOCK
possible owner[43.26843] on resource TX-00040008-000007D9
*** 2011-06-21 22:27:00.029
Submitting asynchronized dump request [28]. summary=[ges process stack dump (kjdglblkrdm1)].
----------enqueue 0xd876ab70------------------------
lock version : 3827
Owner inst : 1
grant_level : KJUSERNL
req_level : KJUSEREX
bast_level : KJUSERNL
notify_func : (nil)
resp : 0xd9e62208
procp : 0xd9978428
pid : 26847
proc version : 238
oprocp : (nil)
opid : 26847
group lock owner : 0xda2cff40
possible pid : 26847
xid : 2E000-0001-00000347
dd_time : 5.0 secs
dd_count : 0
timeout : 0.0 secs
On_timer_q? : N
On_dd_q? : Y
lock_state : OPENING CONVERTING
ast_flag : 0x0
Open Options : KJUSERDEADLOCK
Convert options : KJUSERGETVALUE
History : 0x1495149a
Msg_Seq : 0x0
res_seq : 6
valblk : 0xa06e0660ff7f0000e06b0660ff7f0000 .n`k`
user session for deadlock lock 0xd876ab70
sid: 16 ser: 851 audsid: 4294967295 user: 0/SYS
flags: (0x41) USR/- flags_idl: (0x1) BSY/-/-/-/-/-
flags2: (0x40008) -/-
pid: 46 O/S info: user: oracle, term: UNKNOWN, ospid: 26847
image: oracle@rh2.oracle.com (TNS V1-V3)
client details:
O/S info: user: oracle, term: pts/3, ospid: 26846
machine: rh2.oracle.com program: sqlplus@rh2.oracle.com (TNS V1-V3)
application name: sqlplus@rh2.oracle.com (TNS V1-V3), hash value=3660119738
current SQL:
update lock1 set t1=t1+10 where t1=11
DUMP LOCAL BLOCKER: initiate state dump for DEADLOCK
possible owner[46.26847] on resource TX-00040008-000007D9
*** 2011-06-21 22:27:00.031
Submitting asynchronized dump request [28]. summary=[ges process stack dump (kjdglblkrdm1)].
Global blockers dump end:-----------------------------------
Global Wait-For-Graph(WFG) at ddTS[0.db] :
BLOCKED 0xd876a630 5 wq 2 cvtops x1 TX 0x70015.0x81e(ext 0x2,0x0)[2B000-0001-0000057A] inst 1
BLOCKER 0xd8767a10 5 wq 1 cvtops x28 TX 0x70015.0x81e(ext 0x2,0x0)[2E000-0001-00000347] inst 1
BLOCKED 0xd876ab70 5 wq 2 cvtops x1 TX 0x40008.0x7d9(ext 0x2,0x0)[2E000-0001-00000347] inst 1
BLOCKER 0xd876a7f0 5 wq 1 cvtops x28 TX 0x40008.0x7d9(ext 0x2,0x0)[2B000-0001-0000057A] inst 1
Summary Of Bugs Which Could Cause Deadlock In RAC Environment
Applies to:
Oracle Server - Enterprise Edition - Version: 9.2.0.4 to 11.1 Information in this document applies to any platform.Purpose
The purpose of this Note is to explain various bugs filed specifically for the Dead lock errors in a RAC environment against specific Oracle database versions (This Note covers bugs reported versions above 9.2.0.4), and explain the symptoms of each bug, workarounds if any and references the patch availability at the time this article was written.Scope
This article is a consolidated effort to summarisze top bugs reported specifically for the Dead lock error in RAC environment (This Note covers bugs reported versions above 9.2.0.4) which have been fixed. It is directed towards Oracle Support Analysts and Oracle Customers to have an overview of various bugs logged for the same error .Summary Of Bugs Which Could Cause Deadlock In RAC Environment
Bugs Fixed in Version 9.2.0.5 Note 2941738.8 Bug 2941738 SMON processes may deadlock in RAC Note 2902030.8 Bug 2902030 Deadlocks in RAC not logged in alert log Bugs Fixed in Version 9.2.0.6 10.1.0.4 10.2.0.1 Note 3268802.8 Bug 3268802 Additional diagnostics for deadlock in RAC environment Note.3646162.8 Bug 3646162 False deadlock (ORA-60) in a RAC environment / TM lock mode change Note.3627263.8 Bug 3627263 DEADLOCK OPS STARTUP Deadlock / hang during RAC instance startup Bugs Fixed in Version 9.2.0.7 10.2.0.1 Note.3992847.8 Bug 3992847 Deadlocks are not detected in rac when one node rebooted more recently than the rest Note 3641819.8 Bug 3641819 Undetected deadlock possible in RAC Note 3777178.8 Bug 3777178 TA / US enqueue deadlock during transaction recovery in RAC Note 4220161.8 Bug 4220161 OPS Deadlock between SMON processes on different instances Bug Fixed in Version 9.2.0.8 10.2.0.1 Note 4371923.8 Bug 4371923 SMON may deadlock on TX enqueue waits for updates to COL_USAGE$ in RAC Bug Fixed in Version 10.2.0.2 Note 4579381.8 Bug 4579381 Deadlock on DC_USERS in RAC (ORA-4020) Bug Fixed in Version 10.2.0.3 Note 5012368.8 Bug 5012368 Undetected deadlock in RAC Note 4913415.8 Bug 4913415 Global deadlock not reported in RAC Bug Fixed in Version 10.2.0.4 Note 5470095.8 Bug 5470095 Self deadlock should provide more targeted diagnostics Note 5454831.8 Bug 5454831 deadlock possible on working set latches Note 5334733.8 Bug 5334733 Deadlock resolution can be slow in RAC Note 4441119.8 Bug 4441119 Not enough information dumped when RAC detects a deadlock Note 5883112.8 Bug 5883112 False deadlock in RAC Bug Fixed in Version 10.2.0.5 Note 6145177.8 Bug 6145177 Single resource deadlock with a zero DID For summary of bugs which could cause deadlock in single instance see Note 554616.1
How to Find TX Enqueue Contention in RAC or OPS
PURPOSE
————-
To find the TX Enqueue contention in a RAC or OPS environment
What is TX Enqueue ?
In one word oracle is maintaining queue for transaction.
How Many Resources ?
1/ active transaction
How Many Locks?
1/transaction + 1/process waiting for a locked row by that
transaction.
How Many Users?
1 + 1/ process waiting for something locked by this transaction.
Who Uses?
All processes
What need to investigate?
The mode of TX (6/4), Holding/Waiting/Requesting
SCOPE & APPLICATION
=====================
This document will help to analyze the application design related to transaction bottlenecks
and database performance tuning.
Let start with an example:
===================
create table akdas (A1 number, Col1 Varchar2(10), Col2 Varchar2(10));
insert into akdas values(5,’Hello’,’Hi’);
insert into akdas values(6,’Sudip’,’Datta’);
insert into akdas values(7,’Preetam’,’Roy’);
insert into akdas values(8,’Michael’,’Polaski’);
From Node 1:
==========
update akdas set a1=11 where a1=6;
From Node 2:
==========
update akdas set a1=12 where a1=7;
update akdas set a1=11 where a1=6; /* this will wait for Node1: to complete the transaction */
This Note Is Made To Analyzing Only the TX-Mode-6 (Exclusive).
1. Now run the following query to track down the problem: Who is waiting
===================================================================
prompt
prompt Query 1. Waiting for TX Enqueue where mode is Exclusive
prompt =====================================
prompt
set linesize 100
set pagesize 66
col c1 for a15
col c1 heading “Program Name ”
select l.inst_id,l.SID,program c1,l.TYPE,l.ID1,l.ID2,l.LMODE,l.REQUEST
from gv$lock l,gv$session s
where l.type like ‘TX’ and l.REQUEST =6
and l.inst_id=s.inst_id and l.sid=s.sid
order by id1
/
Output will be here
===============
INST_ID SID Program Name TY ID1 ID2 LMODE REQUEST
———– ———- —————— — ——– ——– ———- ——–
2 13 sqlplus@opcbsol TX 393236 780 0 6
2 (TNS V1-V3)
It is clear that SID 12 of instance 2 is doing a DML and waiting on REQUEST Mode 6.
2. Let’s run the next query to find who is holding
===========================================
prompt
prompt
prompt Query 2. Holding for TX Enqueue where mode greater than 6
prompt =======================================
prompt
set linesize 100
set pagesize 66
col c1 for a15
col c1 heading “Program Name ”
select l.inst_id,l.SID,program c1,l.TYPE,l.ID1,l.ID2,l.LMODE,l.REQUEST
from gv$lock l,gv$session s
where l.type like ‘TX’ and l.LMODE =6 and (l.ID1,l.ID2) in
(select id1,id2 from gv$lock where type like ‘TX’ and REQUEST =6)
and l.inst_id=s.inst_id and l.sid=s.sid
order by id1
/
Output will be here
===============
INST_ID SID Program Name TY ID1 ID2 LMODE REQUEST
———- ———- ————– — ———- ——– ———– ——–
1 12 sqlplus@opcbsol TX 393236 780 6 0
1 (TNS V1-V3)
So holder is SID 12 on instance 1. Where LMODE = 6.
3. Let’s find out the exact file#, block# and Record# where it is waiting
===============================================================
prompt
prompt
prompt Query 3. Object# ,File#, Block# and Slot# TX Enqueue in detail
prompt ========================================
prompt
set linesize 110
col c0 for 999
col c0 heading “INS”
col c1 for a15
col c1 heading “Program Name ”
select inst_id c0,sid,program c1,ROW_WAIT_OBJ# object_no, ROW_WAIT_FILE# Rfile_no,
ROW_WAIT_BLOCK# Block_no ,ROW_WAIT_ROW# Row_no
from gv$session
where (inst_id,sid) in (select inst_id,sid from gv$session_wait where p1=’1415053318′)
/
Output Will be here
===============
INS SID Program Name OBJECT_NO RFILE_NO BLOCK_NO ROW_NO
—– ———- ————- ————— ——— ——-
2 13 sqlplus@opcbsol 7261 9 12346 1
2 (TNS V1-V3)
From the output, it is clear that it is waiting on Relative_File# 9, Block# 12346, Row Number 1.
Here Row Number 1 means the slot number in the block 12346. This Row_No start from 0 (zero).
4. Let’s Find the object details
=============================
prompt
prompt
prompt Query 4. Object Involve for TX Enqueue in detail
prompt ===============================
prompt
set linesize 100
set pagesize 100
col owner for a10
col object_name for a20
col object_type for a10
select owner,object_name,object_id,object_type
from dba_objects
where
object_id in (select ROW_WAIT_OBJ# from gv$session
where (inst_id, sid) in (select inst_id,sid from gv$session_wait where p1=’1415053318′))
/
Output Will be here
===============
OWNER OBJECT_NAME OBJECT_ID OBJECT_TYP
——— ———— ——– ———–
AKDAS AKDAS 7261 TABLE
5. Let’s find the row value details
=============================
prompt
prompt
prompt Query 5. Finding the row value
prompt ====================
prompt
select * from . where rowid like
DBMS_ROWID.ROWID_CREATE(1,&Object_No,&Rfile_No, &Block_No, &Row_Number)
/
From query 3 and 4 we will get the value for all variables.
Owner = AKDAS
Table_Name = AKDAS
Object_No = 7261
Rfile_No = 9
Block_No = 12346
Row_Number = 1
Output Will be here
===============
A1 Col1 Col2
———- ————— ———-
6 Hello Hi
So we can drag down to the row value where TX Enqueue contention exists.
6. Let’s find the user activity that is “Holder” and “Waiter”
====================================================
set linesize 120
set pagesize 66
col c0 for 999
col c0 heading “INS”
col c1 for a9
col c1 heading “OS User”
col c2 for a9
col c2 heading “Oracle User”
col c3 for a15
col c3 heading “Program Name”
col b1 for a9
col b1 heading “Unix PID”
col b2 for 9999 justify left
col b2 heading “ORA SID”
col b3 for 999999 justify left
col b3 heading “SERIAL#”
col sql_text for a45
set space 1
break on b1 nodup on c0 nodup on c3 nodup on c1 nodup on c2 nodup on b2 nodup on b3 skip 2
select a.inst_id c0,b.sid b2,c.spid b1, b.program c3, b.username c2,b.serial# b3, a.sql_text
from gv$sql a, gv$session b, gv$process c
where
a.address = b.sql_address
and b.paddr = c.addr
and a.hash_value = b.sql_hash_value
and a.inst_id=b.inst_id and a.inst_id=c.inst_id
and a.inst_id like ‘&inst_id’ and b.sid like ‘&sid’
order by c.spid,a.hash_value
/
This query asks the Instance Number and Sid number, which you can get from step 1 and 2.
But remember , you can see the waiter activity, but you may not see the holder activity.
Reason is, the holder is sitting idle after doing the DML operation. So SQL for Holder
should not be seen under gv$sql.
This all query can be run for single instance database, but all GV$ view need to replace to V$
and there is no INST_ID for V$ View, that part need to be taken care.
RELATED DOCUMENTS
———————————-
Note:62354.1 -> TX Transaction locks – Example waits scenarios.
Note:135749.1 -> Script to Monitor Current User Activity in the Database.
Note:61685.1 -> Extracting Data from a Corrupt Table using ROWID Range Scans in Oracle