enq: sq – contention 等待事件

2012/12/16 by Leave a Comment

 

ENQUEUE SQ的使用

 

当用户的SQL触发ORACLE需要填充或初始化SEQUENCE CACHE序列缓存时需要申请该SQ Enqueue 以便保护sequence的高水位，SEQUENCE CACHE以KGL sequence object形式存在，其通过查询row cache字典缓存数据中的元数据而获得填充，例如一个SELECT会递归地访问字典表。

 

减少该ENQUEUE SQ的争用

 

  • shared pool空间的不足可能直接导致该SQ ENQUEUE的争用，因为shared pool的空间不足可能导致sequence元数据被频繁刷出共享池。
  • 用户可以尝试使用dbms_shared_pool.keep()来保持该sequence保存在shared pool中
  • 可以为SEQUENCE增加 cache参数，由此减少填充该KGL对象的次数，则同样的申请ENQUEUE的次数会减少

 

ID1/ID2

ID1 代表对应的sequence的object_id ， ID2总是0

 

 

如果自己搞不定可以找诗檀软件专业ORACLE数据库优化团队成员帮您恢复!

 

诗檀软件专业数据库优化团队

 

服务热线 ： 13764045638   QQ号:47079569    邮箱：service@parnassusdata.com

Filed Under: Oracle Tagged With: ,

Script:Diagnostic Oracle Locks

2011/08/27 by 8 Comments

以下脚本可以用于诊断Oracle实例中的锁情况(Lock Status):


REM SCRIPT: FULLY DECODED LOCKING

set echo off
set lines 200
set pagesize 66
break on Kill on sid on  username on terminal
column Kill heading 'Kill String' format a13
column res heading 'Resource Type' format 999
column id1 format 9999990
column id2 format 9999990
column locking heading 'Lock Held/Lock Requested' format a40
column lmode heading 'Lock Held' format a20
column request heading 'Lock Requested' format a20
column serial# format 99999
column username  format a10  heading "Username"
column terminal heading Term format a6
column obj format a30 heading "Table/Sequence Name"
column owner format a9
column LAddr heading "ID1 - ID2" format a18
column Lockt heading "Lock Type" format a40
column command heading "Command" format a25
column sid format 990

select
    nvl(s.username,'Internal') username,
    l.sid,
    nvl(s.terminal,'None') terminal,
    decode(l.type,'TM',u.name||'.'||substr(t.name,1,20),
           'DL',u.name||'.'||substr(t.name,1,20),
           'SQ',u.name||'.'||substr(t.name,1,20),'None') obj,
    decode(command,
        0,'None',
        1,'CREATE TABLE',
        2,'INSERT',
        3,'SELECT',
        4,'CREATE CLUSTER',
        5,'ALTER CLUSTER',
        6,'UPDATE',
        7,'DELETE',
        8,'DROP CLUSTER',
        9,'CREATE INDEX',
        10,'DROP INDEX',
        11,'ALTER INDEX',
        12,'DROP TABLE',
        13,'CREATE SEQUENCE',
        14,'ALTER SEQUENCE',
        15,'ALTER TABLE',
        16,'DROP SEQUENCE',
        17,'GRANT',
        18,'REVOKE',
        19,'CREATE SYNONYM',
        20,'DROP SYNONYM',
        21,'CREATE VIEW',
        22,'DROP VIEW',
        23,'VALIDATE INDEX',
        24,'CREATE PROCEDURE',
        25,'ALTER PROCEDURE',
        26,'LOCK TABLE',
        27,'NO OPERATION',
        28,'RENAME',
        29,'COMMENT',
        30,'AUDIT',
        31,'NOAUDIT',
        32,'CREATE DATABASE LINK',
        33,'DROP DATABASE LINK',
        34,'CREATE DATABASE',
        35,'ALTER DATABASE',
        36,'CREATE ROLLBACK SEGMENT',
        37,'ALTER ROLLBACK SEGMENT',
        38,'DROP ROLLBACK SEGMENT',
        39,'CREATE TABLESPACE',
        40,'ALTER TABLESPACE',
        41,'DROP TABLESPACE',
        42,'ALTER SESSION',
        43,'ALTER USER',
        44,'COMMIT',
        45,'ROLLBACK',
        46,'SAVEPOINT',
        47,'PL/SQL EXECUTE',
        48,'SET TRANSACTION',
        49,'ALTER SYSTEM SWITCH LOG',
        50,'EXPLAIN',
        51,'CREATE USER',
        52,'CREATE ROLE',
        53,'DROP USER',
        54,'DROP ROLE',
        55,'SET ROLE',
        56,'CREATE SCHEMA',
        57,'CREATE CONTROL FILE',
        58,'ALTER TRACING',
        59,'CREATE TRIGGER',
        60,'ALTER TRIGGER',
        61,'DROP TRIGGER',
        62,'ANALYZE TABLE',
        63,'ANALYZE INDEX',
        64,'ANALYZE CLUSTER',
        65,'CREATE PROFILE',
        66,'DROP PROFILE',
        67,'ALTER PROFILE',
        68,'DROP PROCEDURE',
        69,'-',
        70,'ALTER RESOURCE COST',
        71,'CREATE SNAPSHOT LOG',
        72,'ALTER SNAPSHOT LOG',
        73,'DROP SNAPSHOT LOG',
        74,'CREATE SNAPSHOT',
        75,'ALTER SNAPSHOT',
        76,'DROP SNAPSHOT',
        77,'CREATE TYPE',
        78,'DROP TYPE',
        79,'ALTER ROLE',
        80,'ALTER TYPE',
        81,'CREATE TYPE BODY',
        82,'ALTER TYPE BODY',
        83,'DROP TYPE BODY',
        84,'DROP LIBRARY',
        85,'TRUNCATE TABLE',
        86,'TRUNCATE CLUSTER',
        87,'CREATE BITMAP FILE',
        88,'ALTER VIEW',
        89,'DROP BITMAP FILE',
        90,'SET CONSTRAINTS',
        91,'CREATE FUNCTION',
        92,'ALTER FUNCTION',
        93,'DROP FUNCTION',
        94,'CREATE PACKAGE',
        95,'ALTER PACKAGE',
        96,'DROP PACKAGE',
        97,'CREATE PACKAGE BODY',
        98,'ALTER PACKAGE BODY',
        99,'DROP PACKAGE BODY',
        command||' - ???') command,
    decode(l.lmode,1,'No Lock',
        2,'Row Share',
        3,'Row Exclusive',
        4,'Share',
        5,'Share Row Exclusive',
        6,'Exclusive','NONE') lmode,
    decode(l.request,1,'No Lock',
        2,'Row Share',
        3,'Row Exclusive',
        4,'Share',
        5,'Share Row Exclusive',
        6,'Exclusive','NONE') request,
    l.id1||'-'||l.id2 Laddr,
    l.type||' - '||
    decode(l.type,
        'BL','Buffer hash table instance',
        'CF',' Control file schema global enqueue',
        'CI','Cross-instance function invocation instance',
        'CU','Cursor bind',
        'DF','Data file instance',
        'DL','Direct loader parallel index create',
        'DM','Mount/startup db primary/secondary instance',
        'DR','Distributed recovery process',
        'DX','Distributed transaction entry',
        'FS','File set',
        'HW','Space management operations on a specific segment',
        'IN','Instance number',
        'IR','Instance recovery serialization global enqueue',
        'IS','Instance state',
        'IV','Library cache invalidation instance',
        'JQ','Job queue',
        'KK','Thread kick',
        'LA','Library cache lock instance (A=namespace)',
        'LB','Library cache lock instance (B=namespace)',
        'LC','Library cache lock instance (C=namespace)',
        'LD','Library cache lock instance (D=namespace)',
        'LE','Library cache instance lock (E=namespace)',
        'LF','Library cache instance lock (F=namespace)',
        'LG','Library cache instance lock (G=namespace)',
        'LH','Library cache instance lock (H=namespace)',
        'LI','Library cache instance lock (I=namespace)',
        'LJ','Library cache instance lock (J=namespace)',
        'LK','Library cache instance lock (K=namespace)',
        'LL','Library cache instance lock (L=namespace)',
        'LM','Library cache instance lock (M=namespace)',
        'LN','Library cache instance lock (N=namespace)',
        'LO','Library cache instance lock (O=namespace)',
        'LP','Library cache instance lock (P=namespace)',
        'MM','Mount definition gloabal enqueue',
        'MR','Media recovery',
        'NA','Library cache pin instance (A=namespace)',
        'NB','Library cache pin instance (B=namespace)',
        'NC','Library cache pin instance (C=namespace)',
        'ND','Library cache pin instance (D=namespace)',
        'NE','Library cache pin instance (E=namespace)',
        'NF','Library cache pin instance (F=namespace)',
        'NG','Library cache pin instance (G=namespace)',
        'NH','Library cache pin instance (H=namespace)',
        'NI','Library cache pin instance (I=namespace)',
        'NJ','Library cache pin instance (J=namespace)',
        'NL','Library cache pin instance (K=namespace)',
        'NK','Library cache pin instance (L=namespace)',
        'NM','Library cache pin instance (M=namespace)',
        'NN','Library cache pin instance (N=namespace)',
        'NO','Library cache pin instance (O=namespace)',
        'NP','Library cache pin instance (P=namespace)',
        'NQ','Library cache pin instance (Q=namespace)',
        'NR','Library cache pin instance (R=namespace)',
        'NS','Library cache pin instance (S=namespace)',
        'NT','Library cache pin instance (T=namespace)',
        'NU','Library cache pin instance (U=namespace)',
        'NV','Library cache pin instance (V=namespace)',
        'NW','Library cache pin instance (W=namespace)',
        'NX','Library cache pin instance (X=namespace)',
        'NY','Library cache pin instance (Y=namespace)',
        'NZ','Library cache pin instance (Z=namespace)',
        'PF','Password file',
        'PI','Parallel operation',
        'PR','Process startup',
        'PS','Parallel operation',
        'QA','Row cache instance (A=cache)',
        'QB','Row cache instance (B=cache)',
        'QC','Row cache instance (C=cache)',
        'QD','Row cache instance (D=cache)',
        'QE','Row cache instance (E=cache)',
        'QF','Row cache instance (F=cache)',
        'QG','Row cache instance (G=cache)',
        'QH','Row cache instance (H=cache)',
        'QI','Row cache instance (I=cache)',
        'QJ','Row cache instance (J=cache)',
        'QL','Row cache instance (K=cache)',
        'QK','Row cache instance (L=cache)',
        'QM','Row cache instance (M=cache)',
        'QN','Row cache instance (N=cache)',
        'QO','Row cache instance (O=cache)',
        'QP','Row cache instance (P=cache)',
        'QQ','Row cache instance (Q=cache)',
        'QR','Row cache instance (R=cache)',
        'QS','Row cache instance (S=cache)',
        'QT','Row cache instance (T=cache)',
        'QU','Row cache instance (U=cache)',
        'QV','Row cache instance (V=cache)',
        'QW','Row cache instance (W=cache)',
        'QX','Row cache instance (X=cache)',
        'QY','Row cache instance (Y=cache)',
        'QZ','Row cache instance (Z=cache)',
        'RT','Redo thread global enqueue',
        'SC','System commit number instance',
        'SM','SMON',
        'SN','Sequence number instance',
        'SQ','Sequence number enqueue',
        'SS','Sort segment',
        'ST','Space transaction enqueue',
        'SV','Sequence number value',
        'TA','Generic enqueue',
        'TM','DML enqueue',
        'TS',decode(l.id2,0,'Temporary segment enqueue',
                    'New block allocation enqueue lock'),
        'TT','Temporary table enqueue',
        'TX','Transaction enqueue',
        'UL','User supplied',
        'UN','User name',
        'US','Undo segment DDL',
        'WL','Being-written redo log instance', '????') Lockt
from    V$LOCK l,
        V$SESSION s,
        SYS.USER$ u,
        SYS.OBJ$ t
where   l.sid = s.sid
and     t.obj#  = decode(l.type,'TM',l.id1,'DL',l.id1,'SQ',l.id1,1)
and     u.user# = t.owner#
and     s.type != 'BACKGROUND'
order by 1,2,5 ;

select /*+ rule */ s.username, s.sid, s.serial#, l.type "LOCK TYPE", l.id1||'-'||l.id2 id1_id2,
       decode(l.lmode,0,'NONE',
                      1,'NULL',
                      2,'  RS',
                      3,'  RX',
                      4,'   S',
                      5,' SRX',
                      6,'   X',
                        '   ?') HELD,
       decode(l.request,0,'     NONE',
                        1,'     NULL',
                        2,'       RS',
                        3,'       RX',
                        4,'        S',
                        5,'      SRX',
                        6,'        X',
                          '        ?') REQUESTED
   from v$lock l,v$session s
   where l.sid = s.sid
   and s.username like upper('%%')
   order by id1_id2, s.sid, l.type;


  col gtxid form a50
  select 
   s.ksusenum SID
   ,r.ksqrsidt TYPE
   ,r.ksqrsid1 ID1
   ,r.ksqrsid2 ID2
   ,l.lmode lmode
   ,l.request request
   ,l.ctime ctime
   ,t.ktcxbstm tran_start_time
   ,g.K2GTIFMT||'-'||g.K2GTITID_EXT||'-'||g.K2GTIBID XID
  from 
   v$_lock l
   ,x$ksuse s
   ,x$ksqrs r 
   ,x$k2gte g
   ,x$ktcxb t
  where 
   l.saddr=s.addr(+) 
   and l.raddr=r.addr 
   and r.ksqrsidt ='TX'
   and l.laddr = t.ktcxbxba(+)
   and l.laddr = g.k2gtdxcb(+)
  ;


oradebug setmypid;
oradebug ulimite;   
oradebug dump hanganalyze 3;
oradebug dump systemstate 10;

REM check lock script

--- begin [lockchk9.sql] ---
define spoolfile = &1
spool &spoolfile
alter session set nls_date_format = 'YYYY-MM-DD HH24:MI:SS';
alter session set timed_statistics = true;
alter session set max_dump_file_size = UNLIMITED;
set feedback on
set term on
set wrap on
set trimspool on
set pagesize 1000
set linesize 2000
set numwidth 10
set echo on
select to_char(sysdate) start_time from dual;
alter session set events 'immediate trace name systemstate level 10';
alter session set events 'immediate trace name hanganalyze level 3';
column host_name format a20 tru
select instance_name, host_name, version, status, startup_time from v$instance;
select * from v$session;
select * from v$process;
select * from v$bgprocess;
select * from v$lock;
select * from v$locked_object;
select * from v$session_wait;
select * from v$latch;
select * from v$latchholder;
select * from v$rowcache;
/* For MTS */
select * from v$dispatcher;
select * from v$shared_server;
select * from v$circuit;
select * from v$queue;
select * from v$dispatcher_rate;
set echo off
Prompt;
Prompt Output file name is:;
define spoolfile
Prompt;
Prompt ALERT.LOG and TRACE FILES are located in:;
column host_name format a12 tru
column name format a20 tru
column value format a60 tru
select distinct i.host_name, p.name, p.value from v$instance i, v$parameter p
 where p.name like '%_dump_dest'
   and p.name != 'core_dump_dest';
select to_char(sysdate) end_time from dual;
spool off
exit
--- end [lockchk9.sql] ---

lockchk10.sql:

--- begin [lockchk10.sql] ---
define spoolfile = &1
spool &spoolfile
alter session set nls_date_format = 'YYYY-MM-DD HH24:MI:SS';
alter session set timed_statistics = true;
alter session set max_dump_file_size = UNLIMITED;
set feedback on
set term on
set wrap on
set trimspool on
set pagesize 1000
set linesize 2000
set numwidth 10
set echo on
select to_char(sysdate) start_time from dual;
alter session set events 'immediate trace name systemstate level 266';
alter session set events 'immediate trace name hanganalyze level 3';
column host_name format a20 tru
select instance_name, host_name, version, status, startup_time from v$instance;
select * from v$session;
select * from v$process;
select * from v$bgprocess;
select * from v$lock;
select * from v$locked_object;
select * from v$session_wait;
select * from v$latch;
select * from v$latchholder;
select * from v$rowcache;
/* FOR MTS */
select * from v$dispatcher;
select * from v$shared_server;
select * from v$circuit;
select * from v$queue;
select * from v$dispatcher_rate;
set echo off
Prompt;
Prompt Output file name is:;
define spoolfile
Prompt;
Prompt ALERT.LOG and TRACE FILES are located in:;
column host_name format a12 tru
column name format a20 tru
column value format a60 tru
select distinct i.host_name, p.name, p.value from v$instance i, v$parameter p
 where p.name like '%_dump_dest'
   and p.name != 'core_dump_dest';
select to_char(sysdate) end_time from dual;
spool off
exit
--- end [lockchk10.sql] ---

lockchk10win.sql

--- begin [lockchk10win.sql] ---
define spoolfile = &1
spool &spoolfile
alter session set nls_date_format = 'YYYY-MM-DD HH24:MI:SS';
alter session set timed_statistics = true;
alter session set max_dump_file_size = UNLIMITED;
set feedback on
set term on
set wrap on
set trimspool on
set pagesize 1000
set linesize 2000
set numwidth 10
set echo on
select to_char(sysdate) start_time from dual;
alter session set events 'immediate trace name systemstate level 10';
alter session set events 'immediate trace name hanganalyze level 3';
column host_name format a20 tru
select instance_name, host_name, version, status, startup_time from v$instance;
select * from v$session;
select * from v$process;
select * from v$bgprocess;
select * from v$lock;
select * from v$locked_object;
select * from v$session_wait;
select * from v$latch;
select * from v$latchholder;
select * from v$rowcache;
/* FOR MTS */
select * from v$dispatcher;
select * from v$shared_server;
select * from v$circuit;
select * from v$queue;
select * from v$dispatcher_rate;
set echo off
Prompt;
Prompt Output file name is:;
define spoolfile
Prompt;
Prompt ALERT.LOG and TRACE FILES are located in:;
column host_name format a12 tru
column name format a20 tru
column value format a60 tru
select distinct i.host_name, p.name, p.value from v$instance i, v$parameter p
 where p.name like '%_dump_dest'
   and p.name != 'core_dump_dest';
select to_char(sysdate) end_time from dual;
spool off
exit
--- end [lockchk10win.sql] ---
Filed Under: Oracle, Oracle脚本script Tagged With: , ,

enqueue lock wait等待事件

2011/08/08 by Leave a Comment

Enqueues are sophisticated locks for managing access to shared resources like tables, rows, jobs, and redo threads. An enqueue can be requested in different levels/mode: null, row share, row exclusive, share, share row exclusive or exclusive. This wait event indicates a wait for a lock that is held by another session (or sessions) in an incompatible mode to the requested mode.

Isolating contention:

Once an enqueue resource contention problem has been identified with Ignite, one can quickly isolate the SQL and sessions that are suffering. Often, the SQL is a good clue to what objects have locking contention.

During a period of time when locking is typically a problem, use the following query to find out what session is requesting a lock, the type and mode of the requested lock and the session that is blocking.

SELECT DECODE(request,0,’Holder: ‘,’Waiter: ‘)||sid sess, id1, id2, lmode, request, type
FROM V$LOCK
WHERE (id1, id2, type) IN
(SELECT id1, id2, type FROM V$LOCK WHERE request>0)
ORDER BY id1, request;

Solutions

There are many types of locks in Oracle and each has a unique contention remedy. The following are the most common sources of contention:

TX     Transaction Lock

Generally due to application or table setup issues. See Metalink Note:62354.1 for example scenarios which can cause TX lock waits.

TM     DML enqueue

Generally due to application issues, particularly if foreign key constraints have not been indexed. The following two articles describe referential integrity issues related to TM locking:

Example TM locks During Referential Integrity Enforcement — Metalink Note:38373.1

TM locks and Foreign Key Constraints — Metalink Note:33453.1

ST     Space management enqueue

Usually caused by too much space management occurring (Eg: small extent sizes, lots of sorting etc..) See Metalink Note:33567.1 for more information about the ST enqueue.

Filed Under: Oracle Tagged With:

enq: US – contention等待事件

2011/08/08 by 1 Comment

This event indicates the session is currently waiting on the Undo Segments and is often related to using system managed undo with an auto tuned undo retention period. In 10g, Oracle added the _undo_autotune parameter which allows the database instance to extend and override “UNDO_RETENTION” settings. This is especically true when the datafiles for the Undo Tablespace are set to automatically extend. However, there are limitations and issues with this feature discussed in more detail in the Solutions section below.

Here are more details about Automatic Tuning of Undo.

Solutions

Review Bug 7291739 which affected Oracle 10.2.0.4 and is fixed in 10.2.0.4.4 / 10.2.0.5 and 11.2. It is caused by runaway queries inflating the undo retention time. This fix allows users to specify a high threshold for undo retention via the “_highthreshold_undoretention” parameter. The tuned value of undo retention will then not exceed the set value.

Ensure the UNDO tablespace has enough space and/or can autoextend to accomodate the undo settings.

If all else fails or you cannot install the latest patchset, disable automatic undo tuning by setting “_undo_autotune” = false.

Filed Under: Oracle Tagged With:

enq: TX – row lock/index contention、allocate ITL等待事件

2011/08/08 by 3 Comments

SQL> select name from v$event_name where name like ‘%TX%’;

NAME
—————————————————————-
enq: TX – row lock contention
enq: TX – allocate ITL entry
enq: TX – index contention
enq: TX – contention

 

 

如果自己搞不定可以找诗檀软件专业ORACLE数据库修复团队成员帮您恢复!

诗檀软件专业数据库修复团队

服务热线 ： 13764045638   QQ号:47079569    邮箱：service@parnassusdata.com

enqueue TX事务锁 transaction enqueue，顾名思义这个队列锁用来保护事务信息。

 

当进程修改某块中的一行数据，则Oracle必须将该事务信息与被改变的这一行联系起来，做法是在块中的row piece的lk上标记ITL位，而实际的ITL记录了这个事务相关的回滚段号USN，以便能够定位其撤销链。

这样做的目的有几个：

  1. 允许用户手动rollback或者因为dead transaction的发生而后台(PMON or SMON 取决于_cleanup_rollback_entries)回滚该事务。
  2. 在块中留下Undo线索，才能让查询者Queryer顺藤摸瓜去构造这个块在事务发生前的镜像块

 

绝大多数情况下 ,TX enqueue的申请、获得模式总是排他的exclusive的即mode=6/request=6的(v$LOCK)，这通常意味着我们的DML操作所感兴趣的数据行正被其他事务锁定着， 但这并不绝对。

 

有场景中我们会以共享模式Share mode=4/request=4(V$LOCK)去申请、获得TX enqueue锁； 一种场景是进程仅仅先需要申请到数据块(data block包括table/index)上的 ITL(interested transaction lists)事务槽。 以share mode等待(request=4)TX enqueue 的一种可能就是块上无法再扩展本进程所需要的一个ITL了，要么这个块上的ITL 超过MAXTRANS(255)ITL的上限值了，要么是这个块没有更多的空间来容纳更多一个ITL了，(一个ITL 24字节)。

 

若争用contention缘起于排他的exclusive TX队列申请，则表明有并发事务试图锁住同一行数据。 大多数情况下这是由于应用引起的。

实际上log file sync(其实是写redo慢)也会造成buffer busy wait。

 

若争用contention缘起于共享shared TX 队列申请，则有可能是一个或多个数据块无法扩展事务表所导致的。在这种情况下可以考虑增加更大的INITTRANS来解决问题：

对于该问题建议做10046 level 8+ buffer cache dump配合V$SESSION fileid/blockid/objno来诊断问题。

 

从版本9iR2开始可以通过动态性能视图的V$SEGMENT_STATISTICS来判断对象是否经历ITL 等待 ，但是实例参数 STATISTICS_LEVEL必须设置为typical 以上，可以参考statspack中segment by ITL的环节，当然AWR里也有类似的信息：

select owner, object_name from v$segment_statistics where
statistic_name = ‘ITL waits’ and value > 0;

 

TX锁的 ID1/ID2解释

ID1 是用来存放 USN==> Undo Segment Number以及相关的undo segment transaction slot，通过以下转换获得：

(undo_segment_number << 16) + slot

ID2是事务槽位序列号 transaction slot sequence number 或曰wrap number。

 

 

 

另外一些以共享模式share mode mode=4/request=4(V$LOCK)去申请、获得TX enqueue锁的场景：

 

  1. 等待块上的ITL slot
  2. 由于主键、唯一索引约束而等待
  3. 由于位图索引bitmap index而等待
  4. 因为2 phase commit，2段提交而等待

 

其他的一些官方调优手册中也提出了一些可能引起TX V$LOCK. lmode/request =4的shared lock contention的情况：

  • 当2个session试图向一个unique index插入同一个索引键值时，后插入的那个session如此等待，知道先插入的session commit/rollback，则后一个session对应的ORA-0001或插入成功。
  • 当一个session因为bitmap index fragment而等待。位图索引bitmap index是一些索引键值加上ROWID的范围。位图索引中的每一条记录可以映射为实际表上的多条记录。 若2个session试图更新的行映射到同样的位图索引记录，则后者的session需要在前者commit/rollback之前一直等request=4的TX lock。
  • 当等待一个PREPARED transaction时也会出现mode 4的TX
  • 还有就是使用dbms_repair时也可能看到mode 4的TX

 

一些相关的BUG

 

Bug 3159414 documents another case where we might see a TX request in mode 4 when constraints are involved.
Bug 5849679 documents an undetected deadlock case with RAC.

 

 

Occurs when attempting to access a row that is locked in exclusive mode. more…

Solutions

1) Waits due to row locked by an active transaction

See if certain jobs can be run at other times; Investigate application code problems such as commits occurring later than necessary or unnecessary repetitive updates.

2) Waits due to Unique or Primary Key Constraint enforcement

A possible, but risky solution is to drop the constraint and possibly check constraints at a later point (perhaps via a batch job).

3) Waits due to Insufficient ‘ITL’ slots in the Block(INITRANS and MAXTRANS)

Increase the INITRANS and MAXTRANS (9i and below) settings

4) Waits due to rows being covered by the same BITMAP index fragment

Rebuild index

Compressed Indexes

Expanded Definition

A TX lock is acquired exclusive (X) when a transaction initiates its first change and is held until the transaction does a COMMIT or ROLLBACK. This ensures a read consistent image of the block before the transaction started. It is used mainly as a queuing mechanism so that other sessions can wait for the transaction to complete.

Filed Under: Oracle Tagged With:

enq: TT – contention等待事件

2011/08/08 by 2 Comments

TT 队列锁在官方文档中介绍为TT, Temporary Table，但是实际在版本8i之后该队列锁更多参与在表空间管理事务中。 也可以称enqueue TT为tablespace lock。

作用

该enqueue TT队列锁用以在各种类型的表空间操作执行过程中避免出现死锁dead lock。 该enqueue lock的 ID2表明正在执行的操作种类，ID1表明该操作对应的表空间号V$Tablespace.TS#。

ID1/ ID2的含义

id1是tablespace number V$Tablespace.TS#，ID2表明执行中的操作类型。以下是操作类型对应代码;

 

0- 用以避免在drop tablespace和创建rollback segment之间发生死锁

1- 用以串行化在给定表空间上创建数据文件

2- 用以避免在TSPITR tablespace point in time recovery期间发生其他类型操作

4- 用以在创建tablespace时锁住该tablespace id

8- 用以避免在ALTER TABLESPACE期间发生死锁

16- 即16进制的0x10，用以同步 分配和回收盘区，allocation and deallocation of extents.

32+ 用在增加数据文件add datafile和创建表空间时，其中ID2为 32(十进制的)+相对文件号relative file number

 

 

最常见的TT enqueue争用是 TT-00000x-0000010  即allocation and deallocation of extents.

如果10g以后遇到类似问题 ， 建议先了解一下extent management、segment management(ASSM、MSSM?)管理方式等，是否打开了回收站recyclebin功能，以及该表空间上回收站对象的extent数量。

 

enqueue TT相关的一些BUG列表如下：

 
Bug 8313598 – ORA-60 on TT enqueue from DBMS_SPACE.ISDATAFILEDROPPABLE_NAME [ID 8313598.8]
Bug 4732503 – Self-deadlock on TT enqueue [ID 4732503.8]
Bug 6809093 – Hang due to TT enqueue waits on an UNDO tablespace [ID 6809093.8]
Bug 14055559 System hang due to TT enqueue contention with BIGFILE tablespace resize
Bug 3833893 SMON may hold the TT enqueue preventing alter of TEMPORARY TABLESPACE
Sessions Hang Due to Self Deadlock on TT Enqueue [ID 948668.1]
Bug 3397983 – Sessions may hang waiting for TT enqueue [ID 3397983.8]
Bug 3467364 – Adding datafiles serializes on the TT enqueue [ID 3467364.8]
Bug 8332021 – Cannot add a datafiles when sessions reporting ORA-1653 / TT enqueue contention between datafile addition and other segment extension operations [ID 8332021.8]
Bug 2272671 – DEADLOCK possible on CREATE INDEX ONLINE (KGL lock v. TT enqueue) [ID 2272671.8]
Bug 6762619 : ENQUEUE “TT” REMAINS WHEN ACCESSING DBA_FREE_SPACE
Bug 6858962 : TT AND US ENQUEUE DEADLOCK
Bug 9439759 : TT ENQUEUE IS NOT RELEASED WHEN CANCEL QUERY
Bug 6265482 : TT ENQUEUE REMAINS AFTER CANCEL “SELECT * FROM DBA_EXTENTS”
Bug 7217723 : ORA-00060 DEADLOCK DETECTED DUE TO TT AND US ENQUEUE
Bug 4732503 : SELF-DEADLOCK TT ENQUEUE ON 9.2.0.7 SIMILAR TO 3425298
Bug 9403168 : WAIT FOR TT-ENQUEUE FOR INSERT TO BASCIFILES DURING ADDING DATAFILE TO SAME TBS
Bug 9451566 : DEADLOCK ON TT-ENQUEUE
Bug 3833893 : SMON GETS “TT ENQUEUE” OF TEMP IF DATEFILE OF TEMP DOESN’T EXIST.
Bug 9948775 : SELF-DEADLOCK ON TT ENQUEUE
 

 

The TT portion of the wait event name stands for Temporary Table and typically indicates issues with the temporary tablespace or related storage. Look for other process that are manipulating temporary tablespaces or tempfiles during the time when these waits were noticed.

Solutions

If an ALTER DATABASE TEMPFILE DROP hangs due to a TT enqueue that is retained by the SMON process in Oracle 9.2.0.6 or lower, Oracle bug 3833893 is responsible. This problem has been solved in Oracle 9.2.0.7. For more information, see Note 867681.

Longer waits for the TT enqueue are generally caused by Oracle bugs. If you experience problems with this enqueue type, you should contact Oracle Support for further assistance.

Filed Under: Oracle Tagged With:

enq: TM – contention等待事件

2011/08/08 by 3 Comments

介绍

TM enqueue ，这里的TM 是指Table Manipulation,最常见的enqueue 之一， enq: TM – contention 最常见的enqueue 并发争用等待之一。

 

使用

TM 锁在下列场景中被申请：

  1.  在OPS(早期的RAC)中LGWR会以ID1=0 &  ID2=0去申请该队列锁来检查 DML_LOCKS 在所有实例中是全0还是全非0
  2. 当一个单表或分区 需要做不同的表/分区操作时，ORACLE需要协调这些操作，所以需要申请该队列锁。包括：
  3. 启用参考约束 referential constraints
  4. 修改约束从DIASABLE NOVALIDATE 到DISABLE VALIDATE
  5. 重建IOT
  6. 创建视图或者修改ALTER视图时可能需要申请该队列锁
  7. 分析表统计信息或validate structure时
  8. 一些PDML并行DML操作
  9. 所有可能调用kkdllk()函数的操作
  10. 太多太多了。。。

 

减少ENQ: TM – Contention

 

我们可以通过ID1作为object_id来确认大家争用的到底是什么对象，之后我们可以确定具体如何减少这些争用，具体到SQL。

 

ID1 ID2的含义

ID1 要么是0(LGWR)  要么是object_number， 即DBA_OBJECTS.OBJECT_ID，对应锁住对象的对象号。Id2 is 0 for a normal table / partition lock and 1 for a partition-wait lock.

 

 

 

 

 

 

 

Waits on this event typically occur because an index is missing on the column(s) containing a foreign key constraint. In this case Oracle is forced to acquire a TM lock on the child table during DELETE, INSERT and UPDATE statements. However, there are other cases where this can occur, e.g. a LOCK TABLE command is being used.

Solutions

Review all foreign key constraints to ensure corresponding indexes are in place. Script displays the problem table in the Objects tab for the SQL statement. Also review the Blockers tab to see what the blocker is doing. The following script will show all unindexed columns from foreign key constraints for a specific user and it can also be customized to include only the one table :

SELECT * FROM (
SELECT c.table_name, cc.column_name, cc.position column_position
FROM user_constraints c, user_cons_columns cc
WHERE c.constraint_name = cc.constraint_name
AND c.constraint_type = ‘R’
AND c.owner = upper(‘&&owner’) and cc.owner = upper(‘&&owner’)
MINUS
SELECT i.table_name, ic.column_name, ic.column_position
FROM user_indexes i, user_ind_columns ic
WHERE i.index_name = ic.index_name
AND c.owner = upper(‘&&owner’) and cc.owner = upper(‘&&owner’)
)
ORDER BY table_name, column_position;

 

 

1. Bug 4153376 documents a change in the mode that this enqueue takes out for SELECT FOR
UPDATE statements.
2. Bug 5849679 documents an undetected deadlock case with RAC.
3. Bug 6053354 documents a possible area of contention if using indexes that reside in a read-only
tablespace.

Filed Under: Oracle Tagged With:

Identify ksusetxn DID:An Deadlock ID

2011/05/10 by Leave a Comment

我们在查看10704 event trace(Print out information about what enqueues are being obtained)或deadlock detected trace死锁检测跟踪日志时，总是会看到名为”DID”的名词，影响”DID”这个名词被正确理解的一个原因是你很难通过search engine正确找到相关的正确解释(被误解)。

那么DID到底是什么东西呢？我们来看一下trace中的DID:

=====================10704 enqueue trace========================

ksqgtl *** CU-913f5a28-00000000 mode=6 flags=0x10010 timeout=300 ***
ksqgtl: no transaction
ksqgtl: use existing ksusetxn DID
ksqgtl:
        ksqlkdid: 0002-001E-00000026

*** 2011-05-09 23:44:15.210
*** ksudidTrace: ksqgtl
        ksusesdi:   0002-001E-00000025
        ksusetxn:   0002-001E-00000026
ksqgtl: RETURNS 0

*** 2011-05-09 23:44:15.212
ksqrcl: CU,913f5a28,0
ksqrcl: returns 0

*** 2011-05-09 23:44:15.212
ksqgtl *** TM-00013abc-00000000 mode=6 flags=0x401 timeout=21474836 ***
ksqgtl: xcb=0x97227118, ktcdix=2147483647, topxcb=0x97227118
        ktcipt(topxcb)=0x0

*** 2011-05-09 23:44:15.212
ksucti: init txn DID from session DID
ksqgtl:
        ksqlkdid: 0002-001E-00000026

*** 2011-05-09 23:44:15.212
*** ksudidTrace: ksqgtl
        ktcmydid(): 0002-001E-00000026
        ksusesdi:   0002-001E-00000025
        ksusetxn:   0002-001E-00000026

===========================global enqueue deadlock detected==========================

Global Wait-For-Graph(WFG) at ddTS[0.1] :
BLOCKED 0xda9da740 5 wq 2 cvtops x1 TX 0x110001.0x8c(ext 0x5,0x0)[34000-0001-0000006E] inst 1
BLOCKER 0x99a2bf50 5 wq 1 cvtops x28 TX 0x110001.0x8c(ext 0x5,0x0)[1E000-0002-0000001D] inst 2
BLOCKED 0x99448ad8 5 wq 2 cvtops x1 TX 0x9000d.0x7b9(ext 0x2,0x0)[1E000-0002-0000001D] inst 2
BLOCKER 0xda983008 5 wq 1 cvtops x28 TX 0x9000d.0x7b9(ext 0x2,0x0)[34000-0001-0000006E] inst 1

DID即Deadlock id，在Oracle死锁检测时充当一个进程的identifier.可以看到lmd RAC后台进程的global enqueue deadlock detected日志中的DID与单实例死锁检测日志或10704事件跟踪日志中的DID在格式上存在区别，但他们的内容是一样的,我们以单实例中的DID为例:

002-001E-00000026

002:instance number,实例号

001E: 十进制的30，ORACLE PID，ORACLE进程号

0026:十进制的38，类似于v$session.serial#，ORACLE PID的一个串行值，复用ORACLE PID后会递增

global enqueue deadlock detected日志中的DID也是类似的，仅仅是把instance number和oracle pid的顺序颠倒了，同时pid要从高位读起。

Filed Under: Oracle Tagged With: , ,

Oracle Enqueue Lock Type Reference including 11g new locks

2011/04/26 by 1 Comment

内部视图x$ksqst记录了以enqueue type分类的队列管理统计信息(Enqueue management statistics by type)，ksqstwat列反映了等待次数，而ksqstwtim列反映了累积等待时间，实际上v$enqueue_stat动态性能视图的CUM_WAIT_TIME列数据也来源于ksqstwtim:

SQL> select * from v$fixed_view_definition where view_name=upper('gv$enqueue_stat');

VIEW_NAME
------------------------------
VIEW_DEFINITION
--------------------------------------------------------------------------------
GV$ENQUEUE_STAT
select inst_id, ksqsttyp, sum(ksqstreq), sum(ksqstwat), sum(ksqstsgt),
 sum(ksqstfgt), sum(ksqstwtm)    from X$KSQST group by inst_id, ksqsttyp having
sum(ksqstreq) > 0

SQL> desc X$KSQST
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ADDR                                               RAW(8)
 INDX                                               NUMBER
 INST_ID                                            NUMBER
 KSQSTTYP                                           VARCHAR2(2)
 KSQSTREQ                                           NUMBER
 KSQSTWAT                                           NUMBER
 KSQSTSGT                                           NUMBER
 KSQSTFGT                                           NUMBER
 KSQSTWTM                                           NUMBER
 KSQSTRSN                                           VARCHAR2(64)
 KSQSTEXPL                                          VARCHAR2(4000)
 KSQSTEVIDX                                         NUMBER

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0      Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

/* 11g中引入了大量新的enqueue类型，在这里列出 */

SQL> select  KSQSTTYP,KSQSTEXPL from x$ksqst group by  KSQSTTYP,KSQSTEXPL order by KSQSTTYP;
(null)
AB Lock held to ensure that ABMR process is initialized
AB Lock held to ensure that only one ABMR is started in the cluster
AD Synchronizes accesses to a specific ASM disk AU
AE Prevent Dropping an edition in use
AF This enqueue is used to serialize access to an advisor task
AG Synchronizes generation use of a particular workspace
AM ASM group block
AM Allow one ASM password file update per cluster at a time
AM Allow only one AMDU dump when block read failure
AM Block ASM cache freeze
AM Check id1 of call for specific purpose
AM Client group use
AM Prevent DB instance registration during ASM instance shutdown
AM Prevent same file deletion race
AM Prevents a user from being dropped if it owns any open files
AM Registers DB instance to ASM client state object hash
AM Reserve a background COD entry
AM Reserve a rollback COD entry
AM Serializes access to ASM file descriptors
AM Serializes block repairs
AM Start ASM cache freeze
AM Synchronizes disk based allocations/deallocations
AM Synchronizes disk offlines
AO Synchornizes access to objects and scalar variables
AS Synchronizes new service activation
AT Serializes ‘alter tablespace’ operations
AV Serialize inst reg and first DG use
AV Serialize relocating volume extents
AV Serialize taking the AVD DG enqueue
AV prevent DG number collisions
AW Global access synchronization to the AW$ table
AW In-use generation state for a particular workspace
AW Row lock synchronization for the AW$ table
AW Synchronizes user accesses to a particular workspace
AY Affinity Dictionary test affinity synchronization
BB 2PC distributed transaction branch across RAC instances
BF Allocate a bloom filter in a parallel statement
BF PMON bloom filter recovery
BR Lock held to allow cleanup from backup mode during an RMAN proxy-copy backup
BR Lock held to perform a new controlfile autobackup
BR Lock held to prevent file from decreasing in physical size during RMAN backup
BR Lock held to prevent multiple process to update the headers at the same time
BR Lock held to request controlfile autobackups
BR Lock held to serialize file header access during multi-section restore
BR Lock held to serialize section access during multi-section restore
CA Synchronizes various IO calibration runs
CF Synchronizes accesses to the controlfile
CI Coordinates cross-instance function invocations
CL Synchronizes accesses to label cache for label comparison
CL Synchronizes accesses to label cache when dropping a label
CM indicate ASM diskgroup is mounted
CM serialize access to instance enqueue
CM serialize asm diskgroup dismount
CN during descriptor initialization
CN during registration
CN during transaction commit to see concurrent registrations
CO enqueue held be Master in Cleanout Optim
CQ Serializes access to cleanup client query cache registrations
CR Coordinates fast block range reuse ckpt
CT Lock held by one instance while change tracking is enabled, to guarantee access to thread-specific resources
CT Lock held during change tracking space management operations that affect just the data for one thread
CT Lock held during change tracking space management operations that affect the entire change tracking file
CT Lock held to ensure that change tracking data remains in existence until a reader is done with it
CT Lock held to ensure that only one CTWR process is started in a single instance
CT Lock held while enabling or disabling change tracking in RAC
CT Lock held while enabling or disabling change tracking, to ensure that it is only enabled or disabled by one user at a time
CU Recovers cursors in case of death while compiling
CX Index Specific Lock on CTX index
DB Synchronizes modification of database wide supplementallogging attributes
DD Synchronizes local accesses to ASM disk groups
DF Enqueue held by foreground or DBWR when a datafile is brought online in RAC
DG Synchronizes accesses to ASM disk groups
DL Lock to prevent index DDL during direct load
DM Enqueue held by foreground or DBWR to syncrhonize database mount/open with other operations
DN Serializes group number generations
DO Represents an active disk online operation
DO Synchronizes Staleness Registry creation
DO Synchronizes disk onlines and their recovery
DO Synchronizes startup of MARK process
DP Synchronizes access to LDAP parameters
DR Serializes the active distributed recovery operation
DS Prevents a database suspend during LMON reconfiguration
DT Serializes changing the default temporary table spaceand user creation
DV Synchronizes access to lower-version Diana (PL/SQL intermediate representation)
DW Serialize in memory dispenser operations
DX Serializes tightly coupled distributed transaction branches
FA Synchronizes accesses to open ASM files
FB Ensures that only one process can format data blcoks in auto segment space managed tablespaces
FC LGWR opens an ACD thread
FC SMON recovers an ACD thread
FD Synchronization
FE Serializes flashback archive recovery
FG only 1 process in the cluster may do ACD relocation in a disk group
FG resolve race condition to acquire Disk Group Redo Generation Enqueue
FL Enqueue used to synchronize Flashback Database and and deletion of flashback logs.
FL Synchronization
FM Synchronizes access to global file mapping state
FP Synchronizes various File Object(FOB) operations
FR begin recovery of disk group
FR indicate this ACD thread is alive
FR wait for lock domain detach
FS Enqueue used to synchronize recovery and file operations or synchronize dictionary check
FT allow LGWR to generate redo in this thread
FT prevent LGWR from generating redo in this thread
FU This enqueue is used to serialize the capture of the DB Feature           Usage and High Water Mark Statistics
FX ARB relocates ACD extent
HD Serializes accesses to ASM SGA data structures
HP Synchronizes accesses to queue pages
HQ Synchronizes the creation of new queue IDs
HV Lock used to broker the high water mark during parallel inserts
HW Lock used to broker the high water mark during parallel inserts
IA
ID Lock held to prevent other processes from performing controlfile transaction while NID is running
IL Synchronizes accesses to internal label data structures
IM Serializes block recovery for IMU txn
IR Synchronizes instance recovery
IR Synchronizes parallel instance recovery and shutdown immediate
IS Enqueue used to synchronize instance state changes
IT Synchronizes accesses to a temp object’s metadata
JD Synchronizes dates between job queue coordinator and slave processes
JI Lock held during materialized view operations (like refresh, alter) to prevent concurrent operations on the same materialized view
JQ Lock to prevent multiple instances from running a single job
JS Lock got during event notification
JS Lock got when adding subscriber to event q
JS Lock got when doing window open/close
JS Lock got when dropping subscriber to event q
JS Lock obtained when cleaning up q memory
JS Lock on internal scheduler queue
JS Lock to prevent job from running elsewhere
JS Lock to recover jobs running on crashed RAC inst
JS Scheduler evt code and AQ sync
JS Scheduler non-global enqueues
JS Synchronizes accesses to the job cache
JX statement
JX release SQL statement resources
KD Determine DBRM master
KM Synchronizes various Resource Manager operations
KO Coordinates fast object checkpoint
KP Synchronizes kupp process startup
KQ Synchronization of ASM cached attributes
KT Synchronizes accesses to the current Resource Manager plan
MD Lock held during materialized view log DDL statements
MH Lock used for recovery when setting Mail Host for AQ e-mail notifications
MK changing values in enc$
ML Lock used for recovery when setting Mail Port for AQ e-mail notifications
MN Synchronizes updates to the LogMiner dictionary and prevents multiple instances from preparing the same LogMiner session
MO Serialize MMON operations for restricted sessions
MR Lock used to coordinate media recovery with other uses of datafiles
MR Lock used to disallow concurrent standby role transition attempt
MS Lock held during materialized view refresh to setup MV log
MV Held during online datafile move operation or cleanup
MW This enqueue is used to serialize the calibration of the           manageability schedules with the Maintenance Window
MX Lock held to generate a response to the storage server information request when an instance is starting up
OC Synchronizes write accesses to the outline cache
OD Lock to prevent concurrent online DDLs
OL Synchronizes accesses to a particular outline name
OQ Synchronizes access to olapi history allocation
OQ Synchronizes access to olapi history closing
OQ Synchronizes access to olapi history flushing
OQ Synchronizes access to olapi history globals
OQ Synchronizes access to olapi history parameter CB
OT CTX Generic Locks
OW initializing the wallet context
OW terminate the wallet context
PD Prevents others from updating the same property
PE Synchronizes system parameter updates
PF Synchronizes accesses to the password file
PG Synchronizes global system parameter updates
PH Lock used for recovery when setting Proxy for AQ HTTP notifications
PI Communicates remote Parallel Execution Server Process creation status
PL Coordinates plug-in operation of transportable tablespaces
PR Synchronizes process startup
PS Parallel Execution Server Process reservation and synchronization
PT Synchronizes access to ASM PST metadata
PV Synchronizes instance shutdown_slvstart
PV Synchronizes slave start_shutdown
PW DBWR 0 holds enqueue indicating prewarmed buffers present in cache
PW Direct Load needs to flush prewarmed buffers if DBWR 0 holds enqueue
RB Serializes ASM rollback recovery operations
RC Coordinates access to a result-set
RD update RAC load info
RE Synchronize block repair/resilvering operations
RF Captures recent Fast-Start Failover Observer heartbeat information
RF Ensures atomicity of log transport setup
RF Ensures r/w atomicity of DG configuration metadata
RF Identifies which configuration metadata file is current
RF Means for detecting when database is being automatically disabled
RF Records when FSFO Primary Shutdown is suspended
RF Synchronizes apply instance failure detection and failover operation
RF Synchronizes critical apply instance among primary instances
RF Synchronizes selection of the new apply instance
RK wallet master key rekey
RL RAC wallet lock
RN Coordinates nab computations of online logs during recovery
RO Coordinates fast object reuse
RO Coordinates flushing of multiple objects
RP Enqueue held when resilvering is needed or when datablock is repaired from mirror
RR Concurrent invocation of DBMS_WORKLOAD_* package API
RS Lock held to make alert level persistent
RS Lock held to prevent aging list update
RS Lock held to prevent deleting file to reclaim space
RS Lock held to prevent file from accessing during space reclaimation
RS Lock held to prevent file from accessing while reusing circular record
RS Lock held to read alert level
RS Lock held to write alert level
RT Thread locks held by CKPT to synchronize thread enable and disable
RT Thread locks held by LGWR, DBW0, and RVWR to indicate mounted or open status
RU Results of rolling migration CIC
RU Serializes rolling migration operations
RW Lock held by CREATE/ALTER/DROP materialized viewwhile updating materialized view flags in detail tables
RX Synchronizes relocating ASM extents
SB Synchronizes Logical Standby metadata operations
SB Synchronizes table instantiation and EDS operations
SE Synchronizes transparent session migration operations
SF Lock used for recovery when setting Sender for AQ e-mail notifications
SH Should seldom see this contention as this Enqueue is always  acquired in no-wait mode
SI Prevents multiple streams tabel instantiations
SJ Serializes cancelling task executed by slave process
SK Serialize shrink of a segment
SL sending lock escalate to LCK0
SL sending lock req for undo to LCK0
SL sending lock req to LCK0
SO Synchronizes access to Shared Object (PL/SQL Shared Object Manager)
SP (1) due to one-off patch
SP (2) due to one-off patch
SP (3) due to one-off patch
SP (4) due to one-off patch
SQ Lock to ensure that only one process can replenish the sequence cache
SR Coordinates replication / streams operations
SS Ensures that sort segments created during parallel DML operations aren’t prematurely cleaned up
ST Synchronizes space management activities in dictionary-managed tablespaces
SU Serializes access to SaveUndo Segment
SW Coordinates the ‘alter system suspend’ operation
TA Serializes operations on undo segments and undo tablespaces
TB Synchronizes writes to the SQL Tuning Base Existence Cache
TC Lock held to guarantee uniqueness of a tablespace checkpoint
TC Lock of setup of a unqiue tablespace checkpoint in null mode
TD KTF dumping time/scn mappings in SMON_SCN_TIME table
TE KTF broadcasting
TF Serializes dropping of a temporary file
TH Serializes threshold in-memory chain access
TK Lock held by MMON to prevent other MMON spawning of Autotask Slave
TK Serializes spawned Autotask Slaves
TL Serializes threshold log table read and update
TM Synchronizes accesses to an object
TO Synchronizes DDL and DML operations on a temp object
TP Lock held during purge and dynamic reconfiguration of fixed tables.
TQ Streams DDL on queue table
TQ TM access to the queue table
TS Serializes accesses to temp segments
TT Serializes DDL operations on tablespaces
TW Lock held by one instance to wait for transactions on all instances to finish
TX Allocating an ITL entry in order to begin a transaction
TX Lock held by a transaction to allow other transactions to wait for it
TX Lock held on a particular row by a transaction to prevent other transactions from modifying it
TX Lock held on an index during a split to prevent other operations on it
UL Lock used by user applications
US Lock held to perform DDL on the undo segment
WA Lock used for recovery when setting Watermark for memory usage in AQ notifications
WF This enqueue is used to serialize the flushing of snapshots
WG acquire lobid local enqueue when deleting fso
WG acquire lobid local enqueue when locking fso
WL Coordinates access to redo log files and archive logs
WL Serialize access to RAC-wide SGA
WL Serialize access to RFS global state
WL Testing redo transport access/locking
WM Synchronizes new WLM Plan activation
WP This enqueue handles concurrency between purging and baselines
WR Coordinates access to logs by Async LNS and ARCH/FG
XC Lock obtained when incrementing XDB configuration version number
XD Serialize Auto Drop/Add Exadata disk operations
XD Serialize OFFLINE Exadata disk operations
XD Serialize ONLINE Exadata disk operations
XH Lock used for recovery when setting No Proxy Domains for AQ HTTP notifications
XL Keep multiple processes from faulting in the same extent chunk
XQ prevent relocation during _recovery_asserts checking
XQ wait for recovery before doing relocation
XQ wait for relocation before doing block purification
XR Lock held during database force logging mode
XR Lock held during database quiesce
XY Lock used for internal testing
ZA lock held to add partition to std audit table
ZF lock held to add partition to fga audit table
ZG Coordinates file group operations
ZH Synchronizes analysis and insert into compression$, prevents multiple threads analyzing the same table during a load
ZZ lock held for updating global context hash tables

早期metalink上提供的一个lock type list(Values for the TYPE Column: System Types)

System Type Description System Type Description
BL Buffer hash table instance NA..NZ Library cache pin instance (A..Z = namespace)
CF Control file schema global enqueue PF Password File
CI Cross-instance function invocation instance PI, PS Parallel operation
CU Cursor bind PR Process startup
DF Data file instance QA..QZ Row cache instance (A..Z = cache)
DL Direct loader parallel index create RT Redo thread global enqueue
DM Mount/startup db primary/secondary instance SC System commit number instance
DR Distributed recovery process SM SMON
DX Distributed transaction entry SN Sequence number instance
FS File set SQ Sequence number enqueue
HW Space management operations on a specific segment SS Sort segment
IN Instance number ST Space transaction enqueue
IR Instance recovery serialization global enqueue SV Sequence number value
IS Instance state TA Generic enqueue
IV Library cache invalidation instance TS Temporary segment enqueue (ID2=0)
JQ Job queue TS New block allocation enqueue (ID2=1)
KK Thread kick TT Temporary table enqueue
LA .. LP Library cache lock instance lock (A..P = namespace) UN User name
MM Mount definition global enqueue US Undo segment DDL
MR Media recovery WL Being-written redo log instance
Enqueue Type Description
enq: AD – allocate AU Synchronizes accesses to a specific OSM disk AU
enq: AD – deallocate AU Synchronizes accesses to a specific OSM disk AU
enq: AF – task serialization This enqueue is used to serialize access to an advisor task
enq: AG – contention Synchronizes generation use of a particular workspace
enq: AO – contention Synchronizes access to objects and scalar variables
enq: AS – contention Synchronizes new service activation
enq: AT – contention Serializes ‘alter tablespace’ operations
enq: AW – AW$ table lock Global access synchronization to the AW$ table
enq: AW – AW generation lock In-use generation state for a particular workspace
enq: AW – user access for AW Synchronizes user accesses to a particular workspace
enq: AW – AW state lock Row lock synchronization for the AW$ table
enq: BR – file shrink Lock held to prevent file from decreasing in physical size during RMAN backup
enq: BR – proxy-copy Lock held to allow cleanup from backup mode during an RMAN proxy-copy backup
enq: CF – contention Synchronizes accesses to the controlfile
enq: CI – contention Coordinates cross-instance function invocations
enq: CL – drop label Synchronizes accesses to label cache when dropping a label
enq: CL – compare labels Synchronizes accesses to label cache for label comparison
enq: CM – gate Serialize access to instance enqueue
enq: CM – instance Indicate OSM disk group is mounted
enq: CT – global space management Lock held during change tracking space management operations that affect the entire change tracking file
enq: CT – state Lock held while enabling or disabling change tracking, to ensure that it is only enabled or disabled by one user at a time
enq: CT – state change gate 2 Lock held while enabling or disabling change tracking in RAC
enq: CT – reading Lock held to ensure that change tracking data remains in existence until a reader is done with it
enq: CT – CTWR process start/stop Lock held to ensure that only one CTWR process is started in a single instance
enq: CT – state change gate 1 Lock held while enabling or disabling change tracking in RAC
enq: CT – change stream ownership Lock held by one instance while change tracking is enabled, to guarantee access to thread-specific resources
enq: CT – local space management Lock held during change tracking space management operations that affect just the data for one thread
enq: CU – contention Recovers cursors in case of death while compiling
enq: DB – contention Synchronizes modification of database wide supplemental logging attributes
enq: DD – contention Synchronizes local accesses to ASM disk groups
enq: DF – contention Enqueue held by foreground or DBWR when a datafile is brought online in RAC
enq: DG – contention Synchronizes accesses to ASM disk groups
enq: DL – contention Lock to prevent index DDL during direct load
enq: DM – contention Enqueue held by foreground or DBWR to synchronize database mount/open with other operations
enq: DN – contention Serializes group number generations
enq: DP – contention Synchronizes access to LDAP parameters
enq: DR – contention Serializes the active distributed recovery operation
enq: DS – contention Prevents a database suspend during LMON reconfiguration
enq: DT – contention Serializes changing the default temporary table space and user creation
enq: DV – contention Synchronizes access to lower-version Diana (PL/SQL intermediate representation)
enq: DX – contention Serializes tightly coupled distributed transaction branches
enq: FA – access file Synchronizes accesses to open ASM files
enq: FB – contention Ensures that only one process can format data blocks in auto segment space managed tablespaces
enq: FC – open an ACD thread LGWR opens an ACD thread
enq: FC – recover an ACD thread SMON recovers an ACD thread
enq: FD – Marker generation Synchronization
enq: FD – Flashback coordinator Synchronization
enq: FD – Tablespace flashback on/off Synchronization
enq: FD – Flashback on/off Synchronization
enq: FG – serialize ACD relocate Only 1 process in the cluster may do ACD relocation in a disk group
enq: FG – LGWR redo generation enq race Resolve race condition to acquire Disk Group Redo Generation Enqueue
enq: FG – FG redo generation enq race Resolve race condition to acquire Disk Group Redo Generation Enqueue
enq: FL – Flashback database log Synchronization
enq: FL – Flashback db command Enqueue used to synchronize Flashback Database and deletion of flashback logs.
enq: FM – contention Synchronizes access to global file mapping state
enq: FR – contention Begin recovery of disk group
enq: FS – contention Enqueue used to synchronize recovery and file operations or synchronize dictionary check
enq: FT – allow LGWR writes Allow LGWR to generate redo in this thread
enq: FT – disable LGWR writes Prevent LGWR from generating redo in this thread
enq: FU – contention This enqueue is used to serialize the capture of the DB Feature, Usage and High Water Mark Statistics
enq: HD – contention Serializes accesses to ASM SGA data structures
enq: HP – contention Synchronizes accesses to queue pages
enq: HQ – contention Synchronizes the creation of new queue IDs
enq: HV – contention Lock used to broker the high water mark during parallel inserts
enq: HW – contention Lock used to broker the high water mark during parallel inserts
enq: IA – contention
enq: ID – contention Lock held to prevent other processes from performing controlfile transaction while NID is running
enq: IL – contention Synchronizes accesses to internal label data structures
enq: IM – contention for blr Serializes block recovery for IMU txn
enq: IR – contention Synchronizes instance recovery
enq: IR – contention2 Synchronizes parallel instance recovery and shutdown immediate
enq: IS – contention Enqueue used to synchronize instance state changes
enq: IT – contention Synchronizes accesses to a temp object’s metadata
enq: JD – contention Synchronizes dates between job queue coordinator and slave processes
enq: JI – contention Lock held during materialized view operations (like refresh, alter) to prevent concurrent operations on the same materialized view
enq: JQ – contention Lock to prevent multiple instances from running a single job
enq: JS – contention Synchronizes accesses to the job cache
enq: JS – coord post lock Lock for coordinator posting
enq: JS – global wdw lock Lock acquired when doing wdw ddl
enq: JS – job chain evaluate lock Lock when job chain evaluated for steps to create
enq: JS – q mem clnup lck Lock obtained when cleaning up q memory
enq: JS – slave enq get lock2 Get run info locks before slv objget
enq: JS – slave enq get lock1 Slave locks exec pre to sess strt
enq: JS – running job cnt lock3 Lock to set running job count epost
enq: JS – running job cnt lock2 Lock to set running job count epre
enq: JS – running job cnt lock Lock to get running job count
enq: JS – coord rcv lock Lock when coord receives msg
enq: JS – queue lock Lock on internal scheduler queue
enq: JS – job run lock – synchronize Lock to prevent job from running elsewhere
enq: JS – job recov lock Lock to recover jobs running on crashed RAC inst
enq: KK – context Lock held by open redo thread, used by other instances to force a log switch
enq: KM – contention Synchronizes various Resource Manager operations
enq: KP – contention Synchronizes kupp process startup
enq: KT – contention Synchronizes accesses to the current Resource Manager plan
enq: MD – contention Lock held during materialized view log DDL statements
enq: MH – contention Lock used for recovery when setting Mail Host for AQ e-mail notifications
enq: ML – contention Lock used for recovery when setting Mail Port for AQ e-mail notifications
enq: MN – contention Synchronizes updates to the LogMiner dictionary and prevents multiple instances from preparing the same LogMiner session
enq: MR – contention Lock used to coordinate media recovery with other uses of datafiles
enq: MS – contention Lock held during materialized view refresh to setup MV log
enq: MW – contention This enqueue is used to serialize the calibration of the manageability schedules with the Maintenance Window
enq: OC – contention Synchronizes write accesses to the outline cache
enq: OL – contention Synchronizes accesses to a particular outline name
enq: OQ – xsoqhiAlloc Synchronizes access to olapi history allocation
enq: OQ – xsoqhiClose Synchronizes access to olapi history closing
enq: OQ – xsoqhistrecb Synchronizes access to olapi history globals
enq: OQ – xsoqhiFlush Synchronizes access to olapi history flushing
enq: OQ – xsoq*histrecb Synchronizes access to olapi history parameter CB
enq: PD – contention Prevents others from updating the same property
enq: PE – contention Synchronizes system parameter updates
enq: PF – contention Synchronizes accesses to the password file
enq: PG – contention Synchronizes global system parameter updates
enq: PH – contention Lock used for recovery when setting Proxy for AQ HTTP notifications
enq: PI – contention Communicates remote Parallel Execution Server Process creation status
enq: PL – contention Coordinates plug-in operation of transportable tablespaces
enq: PR – contention Synchronizes process startup
enq: PS – contention Parallel Execution Server Process reservation and synchronization
enq: PT – contention Synchronizes access to ASM PST metadata
enq: PV – syncstart Synchronizes slave start shutdown
enq: PV – syncshut Synchronizes instance shutdown_slvstart
enq: PW – perwarm status in dbw0 DBWR 0 holds enqueue indicating prewarmed buffers present in cache
enq: PW – flush prewarm buffers Direct Load needs to flush pre-warmed buffers if DBWR 0 holds enqueue
enq: RB – contention Serializes OSM rollback recovery operations
enq: RF – synch: per-SGA Broker metadata Ensures r/w atomicity of DG configuration metadata per unique SGA
enq: RF – synchronization: critical ai Synchronizes critical apply instance among primary instances
enq: RF – new AI Synchronizes selection of the new apply instance
enq: RF – synchronization: chief Anoints 1 instance’s DMON as chief to other instances’ DMONs
enq: RF – synchronization: HC master Anoints 1 instance’s DMON as health check master
enq: RF – synchronization: aifo master Synchronizes apply instance failure detection and fail over operation
enq: RF – atomicity Ensures atomicity of log transport setup
enq: RN – contention Coordinates nab computations of online logs during recovery
enq: RO – contention Coordinates flushing of multiple objects
enq: RO – fast object reuse Coordinates fast object reuse
enq: RP – contention Enqueue held when resilvering is needed or when data block is repaired from mirror
enq: RS – file delete Lock held to prevent file from accessing during space reclamation
enq: RS – persist alert level Lock held to make alert level persistent
enq: RS – write alert level Lock held to write alert level
enq: RS – read alert level Lock held to read alert level
enq: RS – prevent aging list update Lock held to prevent aging list update
enq: RS – record reuse Lock held to prevent file from accessing while reusing circular record
enq: RS – prevent file delete Lock held to prevent deleting file to reclaim space
enq: RT – contention Thread locks held by LGWR, DBW0, and RVWR to indicate mounted or open status
enq: SB – contention Synchronizes Logical Standby metadata operations
enq: SF – contention Lock used for recovery when setting Sender for AQ e-mail notifications
enq: SH – contention Should seldom see this contention as this Enqueue is always acquired in no-wait mode
enq: SI – contention Prevents multiple streams table instantiations
enq: SK – contention Serialize shrink of a segment
enq: SQ – contention Lock to ensure that only one process can replenish the sequence cache
enq: SR – contention Coordinates replication / streams operations
enq: SS – contention Ensures that sort segments created during parallel DML operations aren’t prematurely cleaned up
enq: ST – contention Synchronizes space management activities in dictionary-managed tablespaces
enq: SU – contention Serializes access to SaveUndo Segment
enq: SW – contention Coordinates the ‘alter system suspend’ operation
enq: TA – contention Serializes operations on undo segments and undo tablespaces
enq: TB – SQL Tuning Base Cache Update Synchronizes writes to the SQL Tuning Base Existence Cache
enq: TB – SQL Tuning Base Cache Load Synchronizes writes to the SQL Tuning Base Existence Cache
enq: TC – contention Lock held to guarantee uniqueness of a tablespace checkpoint
enq: TC – contention2 Lock of setup of a unique tablespace checkpoint in null mode
enq: TD – KTF dump entries KTF dumping time/scn mappings in SMON_SCN_TIME table
enq: TE – KTF broadcast KTF broadcasting
enq: TF – contention Serializes dropping of a temporary file
enq: TL – contention Serializes threshold log table read and update
enq: TM – contention Synchronizes accesses to an object
enq: TO – contention Synchronizes DDL and DML operations on a temp object
enq: TQ – TM contention TM access to the queue table
enq: TQ – DDL contention TM access to the queue table
enq: TQ – INI contention TM access to the queue table
enq: TS – contention Serializes accesses to temp segments
enq: TT – contention Serializes DDL operations on tablespaces
enq: TW – contention Lock held by one instance to wait for transactions on all instances to finish
enq: TX – contention Lock held by a transaction to allow other transactions to wait for it
enq: TX – row lock contention Lock held on a particular row by a transaction to prevent other transactions from modifying it
enq: TX – allocate ITL entry Allocating an ITL entry in order to begin a transaction
enq: TX – index contention Lock held on an index during a split to prevent other operations on it
enq: UL – contention Lock used by user applications
enq: US – contention Lock held to perform DDL on the undo segment
enq: WA – contention Lock used for recovery when setting Watermark for memory usage in AQ notifications
enq: WF – contention This enqueue is used to serialize the flushing of snapshots
enq: WL – contention Coordinates access to redo log files and archive logs
enq: WP – contention This enqueue handles concurrency between purging and baselines
enq: XH – contention Lock used for recovery when setting No Proxy Domains for AQ HTTP notifications
enq: XR – quiesce database Lock held during database quiesce
enq: XR – database force logging Lock held during database force logging mode
enq: XY – contention Lock used for internal testing
Filed Under: Oracle Tagged With: ,

RAC Deadlock For Example

2009/06/21 by 1 Comment

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
Filed Under: Oracle, Oracle RAC Tagged With: , , ,

沪ICP备14014813号-2

沪公网安备 31010802001379号