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] DATATM 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 1TX 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 0xd8757ff8TX 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 1Summary Of
转载于:https://blog.51cto.com/maclean/1276778