Venzi's Tech-Blog

30 December 2010

Oracle lock types

Filed under: Oracle — Venzi @ 16:19

Did you know that there are 202 different lock types in Oracle 11.2 but only 4 of them which are a direct result of a SQL statement? Here is the list:

TYPE NAME IS_USER DESCRIPTION
TM DML YES Synchronizes accesses to an object
TX Transaction YES Lock held by a transaction to allow other transactions to wait for it
UL User-defined YES Lock used by user applications
ZH Compression Analyzer YES Synchronizes analysis and insert into compression$, prevents multiple threads analyzing the same table during a load
AB Auto BMR NO A general class of locks used by auto BMR for various purposes
AD ASM Disk AU Lock NO Synchronizes accesses to a specific ASM disk AU
AE Edition Lock NO Prevent Dropping an edition in use
AF Advisor Framework NO This enqueue is used to serialize access to an advisor task
AG Analytic Workspace Generation NO Synchronizes generation use of a particular workspace
AK GES Deadlock Test NO Lock used for internal testing
AM ASM Enqueue NO ASM instance general-purpose enqueue
AO MultiWriter Object Access NO Synchronizes access to objects and scalar variables
AR ASM Relocation Lock NO Protects locked extent pointers during ASM file relocation
AS Service Operations NO Synchronizes new service activation
AT Alter Tablespace NO Serializes ‘alter tablespace’ operations
AU Audit index file NO Lock held to synchronize access XML to audit index file
AV ASM volume locks NO id1 is for persistent DG number locking. id2 is for other volume serialization
AW Analytic Workspace NO Synchronizes access to Analytic Workspace resources
AY KSXA Test Affinity Dictionary NO Affinity Dictionary test affinity synchronization
BB Global Transaction Branch NO 2PC distributed transaction branch across RAC instances
BF BLOOM FILTER NO Synchronize access to a bloom filter in a parallel statement
BR Backup/Restore NO Lock held by a backup/restore operation to allow other operations to wait for it
CA Calibration NO Lock used by IO Calibration
CF Controlfile Transaction NO Synchronizes accesses to the controlfile
CI Cross-Instance Call Invocation NO Coordinates cross-instance function invocations
CL Label Security cache NO Synchronizes accesses to label cache and label tables
CM ASM Instance Enqueue NO ASM instance and gate enqueue
CN KTCN REG enq NO Enqueue held for registrations for change notifications
CO KTUCLO Master Slave enq NO Enqueue held for determining Master Slaves
CQ Cleanup querycache registrations NO Serializes access to cleanup client query cache registrations
CR Reuse Block Range NO Coordinates checkpointing of block range reuse
CT Block Change Tracking NO A general class of locks used by change tracking for various purposes
CU Cursor NO Recovers cursors in case of death while compiling
DB DbsDriver NO Synchronizes modification of database wide supplementallogging attributes
DD ASM Local Disk Group NO Synchronizes local accesses to ASM disk groups
DF Datafile Online in RAC NO Enqueue held by foreground or DBWR when a datafile is brought online in RAC
DG ASM Disk Group Modification NO Synchronizes accesses to ASM disk groups
DI GES Internal NO Coordinates Global Enqueue Service deadlock detection
DL Direct Loader Index Creation NO Lock to prevent index DDL during direct load
DM Database Mount/Open NO Enqueue held by foreground or DBWR to synchronize database mount/open with other operations
DN Diskgroup number generator NO Serializes Group number generations
DO ASM Disk Online Lock NO Synchronizes disk onlines and their recovery
DP LDAP Parameter NO Synchronizes access to LDAP parameters
DQ ASM RBAL doorbell NO Inter-RBAL process metadata invalidation notification
DR Distributed Recovery NO Serializes the active distributed recovery operation
DS Database Suspend NO Prevents a database suspend during LMON reconfiguration
DT Default Temporary Tablespace NO Serializes changing the default temporary table space and user creation
DV Diana Versioning NO Synchronizes access to lower-version Diana (PL/SQL intermediate representation)
DW In memory Dispenser NO Serialize in memory dispenser operations
DX Distributed Transaction NO Serializes tightly coupled distributed transaction branches
E Library Cache Lock 2 NO Synchronizes accesses to library cache objects
FA ASM File Access Lock NO Synchronizes accesses to open ASM files
FB Format Block NO Ensures that only one process can format data blocks in auto segment space managed tablespaces
FC Disk Group Chunk Mount NO controls access to an ACD chunk
FD Flashback Database NO Coordinate flashback database
FE KTFA Recovery NO Flashback archive Enqueue to serialize recovery
FG ACD Relocation Gate Enqueue NO ACD relocation serialization
FL Flashback database log NO Synchronize access to flashback database log
FM File Mapping NO Synchronizes access to global file mapping state
FP File Object NO Synchronizes various File Object(FOB) operations
FR Disk Group Recovery NO enqueue to control ASM recovery
FS File Set / Dictionary Check NO Enqueue used to synchronize recovery and file operations or synchronize dictionary check
FT Disk Group Redo Generation NO controls the privilege to generate redo in a thread
FU DBFUS NO This enqueue is used to serialize the capture of the DB Feature Usage and High Water Mark Statistics
FW Flashback Writer NO Coordinate RVWR on multiple instances
FX ACD Xtnt Info CIC NO serialize ACD relocation CIC
FZ ASM Freezing Cache Lock NO Freezes ASM Cache for a diskgroup
G Library Cache Pin 2 NO Synchronizes accesses to the contents of library cache objects
HD ASM Disk Header NO Serializes accesses to ASM SGA data structures
HP Queue Page NO Synchronizes accesses to queue pages
HQ Hash Queue NO Synchronizes the creation of new queue IDs
HV Direct Loader High Water Mark NO Lock used to broker the high water mark during parallel inserts
HW Segment High Water Mark NO Lock used to broker the high water mark during parallel inserts
IA Internal NO
ID NID NO Lock held by a NID operation to allow other operations to wait for it
IL Label Security NO Synchronizes accesses to internal label data structures
IM Kti blr lock NO Serializes block recovery for an IMU txn
IR Instance Recovery NO Synchronizes instance recovery
IS Instance State NO Enqueue used to synchronize instance state changes
IT In-Mem Temp Table Meta Creation NO Synchronizes accesses to a temp object’s metadata
IV Library Cache Invalidation NO Synchronizes library cache object invalidations across instances
IZ INSTANCE LOCK NO Proctects the lock name space used by the instance
JD Job Queue Date NO Synchronizes dates between job queue coordinator and slave processes
JI Materialized View NO Lock held during materialized view operations (like refresh, alter) to prevent concurrent operations on the same materialized view
JQ Job Queue NO Lock to prevent multiple instances from running a single job
JS Job Scheduler NO Synchronizes accesses to the job cache
JX SQL STATEMENT QUEUE NO synchronize SQL statement queue operations
KD Scheduler Master DBRM NO Determine DBRM master
KE ASM Cached Attributes NO Synchronization of ASM cached attributes
KK Kick Instance to Switch Logs NO Lock held by open redo thread, used by other instances to force a log switch
KL LOB KSI LOCK NO KSI lock for buffer cache and wgc concurrency
KM Scheduler NO Synchronizes various Resource Manager operations
KO Multiple Object Checkpoint NO Coordinates checkpointing of multiple objects
KP Kupp Process Startup NO Synchronizes kupp process startup
KQ ASM Attributes Enque NO Single Inst Sync of ASM attributes
KT Scheduler Plan NO Synchronizes accesses to the current Resource Manager plan
L Library Cache Lock 1 NO Synchronizes accesses to library cache objects
MD Materialized View Log DDL NO Lock held during materialized view log DDL statements
MH AQ Notification Mail Host NO Lock used for recovery when setting Mail Host for AQ e-mail notifications
MK Master Key NO Serializes enc$ access
ML AQ Notification Mail Port NO Lock used for recovery when setting Mail Port for AQ e-mail notifications
MN LogMiner NO Synchronizes updates to the LogMiner dictionary and prevents multiple instances from preparing the same LogMiner session
MO MMON restricted session NO Serialize MMON operations for restricted sessions
MR Media Recovery NO Lock used to coordinate media recovery with other uses of datafiles
MS Materialized View Refresh Log NO Lock held during materialized view refresh to setup MV log
MV Online Datafile Move NO Synchronizes online datafile move operation or cleanup
MW MWIN Schedule NO This enqueue is used to serialize the calibration of the manageability schedules with the Maintenance Window
MX ksz synch NO Used to synchronize storage server info across all nodes
N Library Cache Pin 1 NO Synchronizes accesses to the contents of library cache objects
OC Outline Cache NO Synchronizes write accesses to the outline cache
OD Online DDLs NO Lock to prevent concurrent online DDLs
OL Outline Name NO Synchronizes accesses to a particular outline name
OQ OLAPI Histories NO Synchronizes access to olapi history resources
OW Encryption Wallet NO Serializes wallet initialization and access
PD Property Lock NO Serializes property update
PE Parameter NO Synchronizes system parameter updates
PF Password File NO Synchronizes accesses to the password file
PG Global Parameter NO Synchronizes global system parameter updates
PH AQ Notification Proxy NO Lock used for recovery when setting Proxy for AQ HTTP notifications
PI Remote PX Process Spawn Status NO Communicates remote Parallel Execution Server Process creation status
PL Transportable Tablespace NO Coordinates plug-in operation of transportable tablespaces
PM ASM PST Signalling NO Signals inter-instance access to ASM PST metadata
PR Process Startup NO Synchronizes process startup
PS PX Process Reservation NO Parallel Execution Server Process reservation and synchronization
PT ASM Partnership and Status Table NO Gates inter-node synchronization of ASM PST metadata
PV KSV slave startup NO Synchronizes slave start_shut
PW Buffer Cache PreWarm NO Coordinates Direct Loads with Prewarmed cache buffers
Q Row Cache NO Coordinates updates and accesses to row cache objects
RB ASM Rollback Recovery NO Serializes ASM rollback recovery operations
RC Result Cache: Enqueue NO Accessing a result in the result-set cache
RD RAC Load NO synchronize update of RAC load info
RE Block Repair/Resilvering NO Synchronizes block repair/mirror resilvering operations
RF Data Guard Broker NO Synchronizes broker lock operation involving lock value
RK wallet_set_mkey NO Serializes wallet master key rekey
RL RAC Encryption Wallet Lock NO RAC encryption wallet lock
RM GES Resource Remastering NO Coordinates Global Enqueue Service resource remastering
RN Redo Log Nab Computation NO Coordinates nab computations of online logs during recovery
RO Multiple Object Reuse NO Coordinates flushing of multiple objects
RP Resilver / Repair NO Enqueue held when resilvering is needed or when datablock is repaired from mirror
RR Workload Capture and Replay NO Prevents concurrent invokers of DBMS_WORKLOAD_*
RS Reclaimable Space NO Lock held by a space reclaimable operation to allow other operations to wait for it
RT Redo Thread NO Thread locks held by LGWR, DBW0, and RVWR to indicate mounted or open status
RU Rolling Migration NO Serilized rolling migration operations
RW Materialized View Flags NO Lock used when creating or readingmaterialized view flags in detail tables
RX ASM Extent Relocation Lock NO Synchronizes relocating ASM extents
SB LogicalStandby NO Synchronizes Logical Standby metadata operations
SC System Change Number NO Coordinates system change number generation on multiple instances
SE Session Migration NO Lock used by transparent session migration
SF AQ Notification Sender NO Lock used for recovery when setting Sender for AQ e-mail notifications
SH Active Session History Flushing NO To prevent multiple concurrent flushers
SI Streams Table Instantiation NO Prevents muiltiple streams table instantiations
SJ KTSJ Slave Task Cancel NO Serializes cancelling task executed by slave process
SK Shrink Segment NO Serialize shrink of a segment
SL Serialize Lock request NO Request serialization to LCK0
SM SMON Serialization NO Lock to check SMON global work in RAC
SO Shared Object NO Synchronizes access to Shared Object(PL/SQL Shared Object Manager)
SP Spare Enqueue NO Spare enqueue for one-off backports
SQ Sequence Cache NO Lock to ensure that only one process can replenish the sequence cache
SR Synchronized Replication NO Coordinates replication / streams operations
SS Sort Segment NO Ensures that sort segments created during parallel DML operations aren’t prematurely cleaned up
ST Space Transaction NO Synchronizes space management activities in dictionary-managed tablespaces
SU SaveUndo Segment NO Serializes access to SaveUndo Segment
SV Sequence Ordering NO Lock to ensure ordered sequence allocation in RAC mode
SW Suspend Writes NO Coordinates the ‘alter system suspend’ operation
TA Instance Undo NO Serializes operations on undo segments and undo tablespaces
TB SQL Tuning Base Existence Cache NO Synchronizes writes to the SQL Tuning Base Existence Cache
TC Tablespace Checkpoint NO Lock held to guarantee uniqueness of a tablespace checkpoint
TD KTF map table enqueue NO Serializes updates and inserts to the SMON_SCN_TIME mapping table
TE KTF broadcast NO Serializes broadcasts for flushes to SMON_SCN_TIME
TF Temporary File NO Serializes dropping of a temporary file
TH Threshold Chain NO Serializes threshold in-memory chain access
TK Auto Task Serialization NO Prevents more than one AutoTask Background Slave from being spawned
TL Log Lock NO Serializes threshold log table read and update
TO Temp Object NO Synchronizes DDL and DML operations on a temp object
TP Runtime Fixed Table Purge NO Lock used for fixed runtime table purge and definition
TQ Queue table enqueue NO Synchronizes access to queue table
TS Temporary Segment NO Serializes accesses to temp segments
TT Tablespace NO Serializes DDL operations on tablespaces
TW Cross-Instance Transaction NO Lock held by one instance to wait for transactions on all instances to finish
US Undo Segment NO Lock held to perform DDL on the undo segment
V Library Cache Lock 3 NO Synchronizes accesses to library cache objects
WA AQ Notification Watermark NO Lock used for recovery when setting Watermark for memory usage in AQ notifications
WF AWR Flush NO This enqueue is used to serialize the flushing of snapshots
WG Write gather local enqueue NO Long term lock on wgc file state
WL Being Written Redo Log NO Coordinates access to redo log files and archive logs
WM WLM Plan Operations NO Synchronizes new WLM Plan activation
WP AWR Purge NO This enqueue handles concurrency between purging and baselines
WR LNS archiving log NO Coordinates access to logs by Async LNS and ARCH/FG
WS LogWriter Standby NO Used by LogWriter to coordinate communication to standby databases
XB ASM Group Block lock NO Prevents client diskgroup use during storage reconfiguration
XC XDB Configuration NO Synchronization access to XDB configuration
XD Auto Online Exadata disks NO Serialize Auto Management of Exadata disks
XH AQ Notification No-Proxy NO Lock used for recovery when setting No Proxy Domains for AQ HTTP notifications
XL ASM Extent Fault Lock NO Keep multiple processes from faulting in the same extent chunk
XQ ASM Extent Relocation Enqueue NO ASM extent relocation
XR Quiesce / Force Logging NO Lock held during database quiesce or for database force logging
XY Internal Test NO Lock used for internal testing
Y Library Cache Pin 3 NO Synchronizes accesses to the contents of library cache objects
ZA Audit Partition NO Lock held for adding partition to Aud table
ZF FGA Partition NO Lock held for adding partition to Fga table
ZG File Group NO Coordinates file group operations
ZZ Global Context Action NO Lock held for updating Global context hash tables

The SQL to retrieve them:


SELECT type, name, is_user, description FROM V$LOCK_TYPE ORDER BY is_user DESC, type;

About these ads

3 Comments »

  1. Thanks alot for this . I saw the KO Checkpoint lock with one of my dml .It got stuck although i don’t know why it got locked as i cannot see any sessions running .

    Comment by Hassaan Yar Khan — 7 July 2011 @ 06:13 | Reply

    • So were you able to solve your issue or do you still see the lock and no active session to it?

      Comment by Venzi — 8 July 2011 @ 05:25 | Reply

  2. […] the relevance for 12c. Let’s start with the one on locks. As posted in late 2010 there are 202 different lock types in Oracle 11g. It’s actually 205 in 11.2.0.3 which was not out back then. Anyway, in 12c we have now 240 […]

    Pingback by Oracle 12c lock types | Venzi's Tech-Blog — 12 August 2013 @ 11:43 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Rubric Theme Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: