oracle mseq,RAC Deadlock For Example

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