ORA-00054 资源正忙 “resource busy and acquire with NOWAIT specified”错误

ORA-00054 资源正忙 “resource busy and acquire with NOWAIT specified”错误

[oracle@mlab2 data]$ oerr ora 00054
00054, 00000, “resource busy and acquire with NOWAIT specified or timeout expired”
// *Cause: Interested resource is busy.
// *Action: Retry if necessary or increase timeout.

 

引起该错误的常见原因是: 所需要的资源正忙或者参数DDL_LOCK_TIMEOUT(11g)指定的DDL锁超时指标被达到,注意在11g之前默认情况下DDL语句以NOWAIT形式出现

对于该错误的建议是 稍后重试SQL命令语句,推荐是在业务空闲时期执行,或者指定更大的DD_LOCK_TIMEOUT,或者不要使用显示的NOWAIT子句

 

关于该ORA-00054错误的详细说明:

在绝大多数情况下ORA-00054都是最常见不过的报错信息,常在SQL语句被其他用户的工作阻塞时出现,该被阻塞的SQL命令存在2种可能:

在语句中指定了”NOWAIT”子句,因此该语句将不被阻塞等待,而直接报错即ORA-00054错误
语句本身是DDL,在11g之前或者DDL_LOCK_TIMEOUT=0,该DDL语句直接返回ORA-54错误
简而言之 DDL或 SELECT .. FOR UPDATE NOWAIT都可能因为存在不兼容的锁模式而遇到ORA-54错误

 

相关的诊断步骤:

该ORA-00054错误一般不需要特别去诊断,除非他频繁影响应用正常运作或者意外出现。 最简单的我们可以通过该语句本身去定位哪些对象(主要是TABLE)被以其他进程以不兼容的模式锁住。

对于重复执行的DDL语句,若采用PL/SQL存储过程DBMS_SQL来调用则可以由于本PL/SQL程序块的一些依赖关系导致该DDL访问的对象被锁。

对于SELECT FOR UPDATE语句,可以尝试去掉其NOWAIT子句来检查该语句如何被阻塞。 若存在阻塞可以通过V$LOCk检测阻塞情况,例如:

SELECT * FROM V$LOCK WHERE request!=0;
–找出被阻塞的进程

 

也可以直接参考更高效的语句 DIAG LOCK:https://www.askmaclean.com/archives/script-diagnostic-oracle-locks.html

 

其次有必要检查一些,是否外键列都有了必要的索引?(这可能导致额外的表锁,虽然在10g以后得到优化)

若是SELECT … FOR UPDATE引起的锁,是否有把2表连接(JOIN)的操作?这样可能导致父和子行都被锁住,若仅仅想锁住child row,则需要加入以下子句:

FOR UPDATE OF COLUMN_ON_CHILD_TABLE

 

 

 

Script:Diagnostic Oracle Locks

以下脚本可以用于诊断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] ---

Know more about commit

COMMIT操作是RDBMS中事务结束的标志,在Oracle中与commit紧密相关的是SCN(System Change Number)。

引入SCN的最根本目的在于:

  1. 为读一致性所用
  2. 为redolog中的记录排序,以及恢复

SCN由SCN Base和Scn Wrap组成,是一种6个字节的结构(structure)。其中SCN Base占用4个字节,而SCN wrap占用2个字节。但在实际存储时SCN-like的stucture常会占用8个字节。

 ub4 kscnbas
ub2 kscnwrp
struct kcvfhcrs, 8 bytes                 @100                              Creation Checkpointed at scn
ub4 kscnbas                        @100      0x000a8849
ub2 kscnwrp                        @104      0x0000

在Oracle中一个事务的开始包含以下操作:

  1. 绑定一个可用的rollback segment
  2. 在事务表(transaction table)上分配一个必要的槽位
  3. 从rollback segment中分配undo block

注意system rollback segment是一种特殊的回滚段,在10g以后普通回滚段的类型都变成了”TYPE2 UNDO”,而唯有system rollback segment的类型仍为”ROLLBACK”,这是由其特殊性造就的:

SQL> col segment_name for a20
SQL> col rollback for a20
SQL> select segment_name,segment_type from dba_segments where segment_type='ROLLBACK';
SEGMENT_NAME         SEGMENT_TYPE
-------------------- ------------------
SYSTEM               ROLLBACK

System rollback segment面向的是SYSTEM表空间上数据字典对象相关事务的数据,以及由对用户数据产生的递归SQL调用所产生的数据。

Oracle不使用基于内存锁管理器的行锁,Oracle中的row lock是基于数据块的。数据块中的Interested Transaction List(ITL)是行锁的重要标志。
ITL的分配遵循以下的原则:

  1. 找出未被使用的ITL
  2. 找出最老的已经事务提交的ITL
  3. 做部分的块清理,直到有可用的ITL
  4. 扩展ITL区域,一条ITL占用24字节

当事务提交COMMIT时,需要完成以下步骤的操作:

  1. 得到一个SCN值
  2. 使用得到的SCN更新事务表中的槽位
  3. 在redo log buffer中创建一条commit记录
  4. 将redo log buffer刷新到磁盘上的在线日志文件
  5. 释放表和行上的锁(may cause delayed block cleanout)

Oracle Enqueue Lock Type Reference including 11g new locks

内部视图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

Row Cache lock Problem

一套AIX上的11.1.0.7系统,应用启动时出现大量row cache lock等待,具体的systemstate dump信息如下:

FILE VERSIONS
-----------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, Real Application Clusters, Data Mining and Real Application Testing options
ORACLE_HOME = /oracle/product/11.1.0/database
System name: AIX
Node name: HA5_4ADB01
Release: 3
Version: 5
Machine: 000687C2D900
Instance name: HN4A1
TRACE FILE
---------------------------
Filename=HN4A1_ora_2061038.trc
Chains most likely to have caused the hang:
[a] Chain 1 Signature: 'row cache lock'< ='row cache lock' (cycle)
Chain 1 Signature Hash: 0x75bdd0c
[b] Chain 2 Signature: 'row cache lock'<='row cache lock' (cycle)
Chain 2 Signature Hash: 0x75bdd0c
[c] Chain 3 Signature: 'row cache lock'<='row cache lock' (cycle)
Chain 3 Signature Hash: 0x75bdd0c
===============================================================================
Cycles:
-------------------------------------------------------------------------------
Chain 1:
-------------------------------------------------------------------------------
Oracle session identified by:
{
instance: 1 (hn4a.hn4a1)
os id: 2114372
process id: 211, oracle@HA5_4ADB01
session id: 610
session serial #: 5
}
is waiting for 'row cache lock' with wait info:
{
p1: 'cache id'=0x7
p2: 'mode'=0x0
p3: 'request'=0x5
time in wait: 0.491964 sec
heur. time in wait: 26.859741 sec
timeout after: 2.508036 sec
wait id: 311
blocking: 1 session
wait history:
1.     event: 'row cache lock'
wait id: 310             p1: 'cache id'=0x7
time waited: 2.929713 sec    p2: 'mode'=0x0
p3: 'request'=0x5
2.     event: 'row cache lock'
wait id: 309             p1: 'cache id'=0x7
time waited: 2.929726 sec    p2: 'mode'=0x0
p3: 'request'=0x5
3.     event: 'row cache lock'
wait id: 308             p1: 'cache id'=0x7
time waited: 2.929720 sec    p2: 'mode'=0x0
p3: 'request'=0x5
}
and is blocked by
=> Oracle session identified by:
{
instance: 1 (hn4a.hn4a1)
os id: 2118476
process id: 209, oracle@HA5_4ADB01
session id: 616
session serial #: 5
}
which is waiting for 'row cache lock' with wait info:
{
p1: 'cache id'=0x7
p2: 'mode'=0x0
p3: 'request'=0x5
time in wait: 0.491910 sec
heur. time in wait: 26.859705 sec
timeout after: 2.508090 sec
wait id: 308
blocking: 1 session
wait history:
1.     event: 'row cache lock'
wait id: 307             p1: 'cache id'=0x7
time waited: 2.929713 sec    p2: 'mode'=0x0
p3: 'request'=0x5
2.     event: 'row cache lock'
wait id: 306             p1: 'cache id'=0x7
time waited: 2.929729 sec    p2: 'mode'=0x0
p3: 'request'=0x5
3.     event: 'row cache lock'
wait id: 305             p1: 'cache id'=0x7
time waited: 2.929715 sec    p2: 'mode'=0x0
p3: 'request'=0x5
}
and is blocked by the session at the start of the chain.
Chain 1 Signature: 'row cache lock'< ='row cache lock' (cycle)
Chain 1 Signature Hash: 0x75bdd0c
...==>many similar chains
PROCESS 211:
----------------------------------------
SO: 0x7000008a1370178, type: 2, owner: 0x0, flag: INIT/-/-/0x00 if: 0x3 c: 0x3
proc=0x7000008a1370178, name=process, file=ksu.h LINE:10706 ID:, pg=0
(process) Oracle pid:211, ser:3, calls cur/top: 0x7000008ac61a0e8/0x7000008ac61a0e8
flags : (0x0) -
flags2: (0x0),  flags3: (0x0)
int error: 0, call error: 0, sess error: 0, txn error 0
ksudlp FALSE at location: 0
(post info) last post received: 0 0 201
last post received-location: kqr.h LINE:2181 ID:kqrbgl: compatible mode
last process to post me: 70000089134b800 1 6
last post sent: 0 0 0
last post sent-location: No post
last process posted by me: none
(latch info) wait_event=0 bits=0
Process Group: DEFAULT, pseudo proc: 0x7000008a153a4c8
O/S info: user: oracle, term: UNKNOWN, ospid: 2114372
OSD pid info: Unix process pid: 2114372, image: oracle@HA5_4ADB01
Short stack dump:
< -ksedsts()+0254<-ksdxfstk()+0028<-ksdxcb()+05d8<-sspuser()+0074<-44c0<-skgpwwait()+00b8<-ksliwat()+0c48<-kslwaitctx()+0150<-kqrigt()+06ac<-kqrLockAndPinPo()+0250<-kqrpre1()+061c<-kqrpre()+001c<-kziavua()+0384<-kpolnb()+0ffc<-kpoauth()+07a0<-opiodr()+0b98<-ttcpip()+115c<-opitsk()+1700<-opiino()+09f0<-opiodr()+0b98<-opidrv()+0440<-sou2o()+0090<-opimai_real()+01b0<-main()+0090<-__start()+0098
service name: SYS$USERS
client details:
O/S info: user: aiuap, term: unknown, ospid: 1234
machine: HA5-4A26 program: JDBC Thin Client
application name: JDBC Thin Client, hash value=2546894660
...
proc=0x7000008a1370178, name=row cache enqueues, file=kqr.h LINE:2004 ID:, pg=0
row cache enqueue: count=1 session=7000008916dee00 object=7000008dfd84428, request=X
savepoint=0x3e
row cache parent object: address=7000008dfd84428 cid=7(dc_users)
hash=481062cf typ=21 transaction=700000888da17a0 flags=00000002
own=7000008dfd844f0[7000008bf6e18f0,7000008bf6e18f0] wat=7000008dfd84500[7000008bf5d2750,7000008ef34e818] mode=X
status=VALID/-/-/-/-/-/-/-/-
request=N release=FALSE flags=8
instance lock=QK 481062cf bb6f8fe9
...
LIBRARY OBJECT LOCK: 7000008ef5069d8 handle=7000008dfd22338 mod=N
pnc=0 pns=0 cbb=1 rpr=1 exc=1 ilh=0 ctx=0
use=7000008916dee00 ses=7000008a15c44f0 cnt=1 flg=CNB/[0001] spn=0x4c3d09ea
LIBRARY HANDLE:7000008dfd22338 bid=105620 hid=a3c59c94 lmd=N pmd=0 sta=VALD
name=select privilege#,level from sysauth$ connect by grantee#=prior privilege# and privilege#>0 start with grantee#=:1 and privilege#>0
hash=8973f7c629f09e3f081962c2a3c59c94 idn=0

众多wait chain的源头均是row cache lock,systemstate转储中该等待事件相关的p1,也就是cache id均为7;cache#为7的字典缓存代表着dc_users,即字典中的用户信息(包括用户名,密码等信息):
[Read more…]

Know Oracle Lock Mode

Value   Name(s)                    Table method (TM lock)
0   No lock                    n/a
1   Null lock (NL)             Used during some parallel DML operations (e.g. update) by
the pX slaves while the QC is holding an exclusive lock.
2   Sub-share (SS)             Until 9.2.0.5/6 "select for update"
Row-share (RS)             Since 9.2.0.1/2 used at opposite end of RI during DML
Lock table in row share mode
Lock table in share update mode
3   Sub-exclusive(SX)          Update (also "select for update" from 9.2.0.5/6)
Row-exclusive(RX)          Lock table in row exclusive mode
Since 11.1 used at opposite end of RI during DML
4   Share (S)                  Lock table in share mode
Can appear during parallel DML with id2 = 1, in the PX slave sessions
Common symptom of "foreign key locking" (missing index) problem
5   share sub exclusive (SSX)  Lock table in share row exclusive mode
share row exclusive (SRX)  Less common symptom of "foreign key locking" but likely to be more
frequent if the FK constraint is defined with "on delete cascade."
6   Exclusive (X)              Lock table in exclusive mode

Summary of Locks Obtained by DML Statements

SQL Statement Row Locks Table Lock Mode RS RX S SRX X
SELECTFROM table... none Y Y Y Y Y
INSERT INTO table Yes SX Y Y N N N
UPDATE table Yes SX Y* Y* N N N
MERGE INTO table Yes SX Y Y N N N
DELETE FROM table Yes SX Y* Y* N N N
SELECTFROM table FOR UPDATE OF Yes SX Y* Y* N N N
LOCK TABLE table IN
ROW SHARE MODE SS Y Y Y Y N
ROW EXCLUSIVE MODE SX Y Y N N N
SHARE MODE S Y N Y N N
SHARE ROW EXCLUSIVE MODE SSX Y N N N N
EXCLUSIVE MODE X N N N N N
* Yes, if no conflicting row locks are held by another transaction. Otherwise, waits occur.

 

mode 1: NL Null N
mode 2: SS RS Row-S Row Share(d) SubShare Intended Share (IS) L
mode 3: SX RX Row-X Row Exclusive SubExclusive Intended Exclusive (IX) R
mode 4: S Share S
mode 5: SSX SRX S/Row-X Share(d) Row Exclusive Share-SubExclusive C
mode 6: X Exclusive X



compatible ? SS,RS SX,RX S SSX,SRX X
SS,RS yes yes yes yes no
SX,RX yes yes no no no
S yes no yes no no
SSX, SRX yes no no no no
X no no no no no

GES (global enqueue resources) enqueues having different values for the lock mode:

#define KJUSERNL 0          /* no permissions */    (Null)
#define KJUSERCR 1          /* concurrent read */   (Row-S (SS))
#define KJUSERCW 2          /* concurrent write */  (Row-X (SX))
#define KJUSERPR 3          /* protected read */    (Share)
#define KJUSERPW 4          /* protected write */   (S/Row-X (SSX))
#define KJUSEREX 5          /* exclusive access */  (Exclusive)
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
5 means KJUSEREX ,cross instance "TX mode 6" locks

沪ICP备14014813号

沪公网安备 31010802001379号