오라클 LOCK TYPE 과 LOCK MODE
DB CAFE
thumb_up 추천메뉴 바로가기
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
1 오라클 LOCK TYPE 과 LOCK MODE[편집]
- Oracle Lock 은 Lock Type 과 Lock Mode 가 있음
2 Lock Type[편집]
- 락이 발생한 대상(Target)을 가르킴
- Lock 이 Temp Tablespace 에서 발생한 건지,
- Undo Segment 에서 발생한 건지,
- Log Miner 와 관계된 건지를 확인
- 대표적인 Lock Type 은 'TX' (Transaction Lock), 'TM' (Table Lock) 등이 있고, 약 290여개 Lock Type 이 존재
4 Lock Type 종류[편집]
- V$LOCK_TYPE
TYPE NAME DESCRIPTION
---------- ---------------------------------------- -----------------------------------------------------------------
AB Auto BMR A general class of locks used by auto BMR for various purposes
AC Application Continuity Synchronizes new service activation
AD ASM Disk AU Lock Synchronizes accesses to a specific ASM disk AU
AE Edition Lock Prevent Dropping an edition in use
AF Advisor Framework This enqueue is used to serialize access to an advisor task
AG Analytic Workspace Generation Synchronizes generation use of a particular workspace
AH ASM Relocation Lock High Protects locked extent pointers during ASM file relocation
AK GES Deadlock Test Lock used for internal testing
AM ASM Enqueue ASM instance general-purpose enqueue
AO MultiWriter Object Access Synchronizes access to objects and scalar variables
AP SPM Autopurge This enqueue is used to serialize the purging of SQL plan baselines
AQ Queue Partitioning local enqueue Synchronizes partition operations on queue table within instance
AR ASM Relocation Lock Protects locked extent pointers during ASM file relocation
AS Service Operations Synchronizes new service activation
AT Alter Tablespace Serializes 'alter tablespace' operations
AU DBKR This enqueue is used to serialize ADR purge operation
AV ASM volume locks id1 is for persistent DG number locking. id2 is for other volume serialization
AW Analytic Workspace Synchronizes access to Analytic Workspace resources
AY KSXA Test Affinity Dictionary Affinity Dictionary test affinity synchronization
AZ Emon-SRVNTFN_Q Create Synchronize creation of AQ_SRVNTFN_Q among emon slaves
BA non durable sub bmap alloc Synchronizes access to globl Bitmap Alloc
BB Global Transaction Branch 2PC distributed transaction branch across RAC instances
BC BA Container Backup Appliance Container
BE Critical Block Allocation Lock used to serialize allocation of critical blocks
BF BLOOM FILTER Synchronize access to a bloom filter in a parallel statement
BI BA Contained File Identification Backup Appliance Contained File Identification
BM clonedb bitmap file access synchronizes clonedb bitmap file operations
BO DRM Banned-Object List Coordinates dynamic remastering banned-object list
BR Backup/Restore Lock held by a backup/restore operation to allow other operations to wait for it
BS Backup/Restore spare enq KRB space usage
BU Buddy Instance Context synchronizes access to Recovery Set constructed by RMS0
BV BA Group Rebuild/Validate Backup Appliance Container Rebuild/Validate
BZ BA Contained File Resize Backup Appliance Contained File Resize
CA Calibration Lock used by IO Calibration
CB CBAC Master Lock Synchronizes accesses to the CBAC roles cached in KGL
CC Column Key Serializes Column Encryption Key Cache
CF Controlfile Transaction Synchronizes accesses to the controlfile
CI Cross-Instance Call Invocation Coordinates cross-instance function invocations
CL Label Security cache Synchronizes accesses to label cache and label tables
CM ASM Instance Enqueue ASM instance and gate enqueue
CN KTCN REG enq Enqueue held for registrations for change notifications
CO KTUCLO Master Slave enq Enqueue held for determining Master Slaves
CP Pluggable Database Coordinate pluggable database operations
CQ Cleanup querycache registrations Serializes access to cleanup client query cache registrations
CR Reuse Block Range Coordinates checkpointing of block range reuse
CT Block Change Tracking A general class of locks used by change tracking for various purposes
CU Cursor Recovers cursors in case of death while compiling
CX TEXT: Index Specific Lock Index Specific Lock on CTX index
DA Instance DP Array Slave Process Spawn reservation and synchronization
DB DbsDriver Synchronizes modification of database wide supplementallogging attributes
DD ASM Local Disk Group Synchronizes local accesses to ASM disk groups
DE Planned Draining RAC enqueue Enqueue to serialize draining structure updates
DF Datafile Online in RAC Enqueue held by foreground or DBWR when a datafile is brought online in RAC
DG ASM Disk Group Modification Synchronizes accesses to ASM disk groups
DI GES Internal Coordinates Global Enqueue Service deadlock detection
DL Direct Loader Index Creation Lock to prevent index DDL during direct load
DM Database Mount/Open Enqueue held by foreground or DBWR to synchronize database mount/open or pluggable database open with other operations
DN Diskgroup number generator Serializes Group number generations
DO ASM Disk Online Lock Synchronizes disk onlines and their recovery
DP LDAP Parameter Synchronizes access to LDAP parameters
DQ ASM RBAL doorbell Inter-RBAL process metadata invalidation notification
DR Distributed Recovery Serializes the active distributed recovery operation
DS Database Suspend Prevents a database suspend during LMON reconfiguration
DT Default Temporary Tablespace Serializes changing the default temporary table space and user creation
DV Diana Versioning Synchronizes access to lower-version Diana (PL/SQL intermediate representation)
DW In memory Dispenser Serialize in memory dispenser operations
DX Distributed Transaction Serializes tightly coupled distributed transaction branches
E Library Cache Lock 2 Synchronizes accesses to library cache objects
FA ASM File Access Lock Synchronizes accesses to open ASM files
FB Format Block Ensures that only one process can format data blocks in auto segment space managed tablespaces
FC Disk Group Chunk Mount controls access to an ACD chunk
FD Flashback Database Coordinate flashback database
FE KTFA Recovery Flashback archive Enqueue to serialize recovery
FG ACD Relocation Gate Enqueue ACD relocation serialization
FH Flush Stat Serializes flush of ILM stats to disk
FL Flashback database log Synchronize access to flashback database log
FM File Mapping Synchronizes access to global file mapping state
FO Oracle File System Operation Synchronize Oracle File System Operations
FP File Object Synchronizes various File Object(FOB) operations
FR Disk Group Recovery enqueue to control ASM recovery
FS File Set / Dictionary Check Enqueue used to synchronize recovery and file operations or synchronize dictionary check
FT Disk Group Redo Generation controls the privilege to generate redo in a thread
FU DBFUS This enqueue is used to serialize the capture of the DB Feature Usage and High Water Mark Statistics
FW Flashback Writer Coordinate RVWR on multiple instances
FX ACD Xtnt Info CIC serialize ACD relocation CIC
FZ ASM Freezing Cache Lock Freezes ASM Cache for a diskgroup
G Library Cache Pin 2 Synchronizes accesses to the contents of library cache objects
HC HSM cache Add or Remove from HSM cache
HD ASM Disk Header Serializes accesses to ASM SGA data structures
HM Hang Manager Test Resource Resource to Generate Hangs for Hang Manager Testing
HP Queue Page Synchronizes accesses to queue pages
HQ Hash Queue Synchronizes the creation of new queue IDs
HV Direct Loader High Water Mark Lock used to broker the high water mark during parallel inserts
HW Segment High Water Mark Lock used to broker the high water mark during parallel inserts
IA Internal
IC IOServer Client Gets a unique client ID
ID NID Lock held by a NID operation to allow other operations to wait for it
IF IOServer File Synchronizes file operations
II IOS Instance fencing lock Signals fencing of a client in an instance
IK IOS instance node fencing Signals cluster/node fencing status
IL Label Security Synchronizes accesses to internal label data structures
IM Kti blr lock Serializes block recovery for an IMU txn
IN in-memory segment Serialize in-memory segment create/drop
IP PDB Instance State Enqueue used to synchronize instance state changes for PDBs
IR Instance Recovery Synchronizes instance recovery
IS Instance State Enqueue used to synchronize instance state changes
IT In-Mem Temp Table Meta Creation Synchronizes accesses to a temp object's metadata
IV Library Cache Invalidation Synchronizes library cache object invalidations across instances
IZ INSTANCE LOCK Proctects the lock name space used by the instance
JD Job Queue Date Synchronizes dates between job queue coordinator and slave processes
JG Job Scheduler1 Synchronizes accesses to the job cache1
JI Materialized View Lock held during materialized view operations (like refresh, alter) to prevent concurrent operations on the same materialized view
JQ Job Queue Lock to prevent multiple instances from running a single job
JS Job Scheduler Synchronizes accesses to the job cache
JX SQL STATEMENT QUEUE synchronize SQL statement queue operations
JZ Join group dictionary Synchronizes modifications to im_domain$ and im_joingroup$
KA Kernel Service ACL Global ACL State (Disabled=0/Enabled=1
KD Scheduler Master DBRM Determine DBRM master
KE ASM Cached Attributes Synchronization of ASM cached attributes
KI KJCI Serialization Enqueue Enqueue to Serialize KJCI Cross-Instance Calls
KK Kick Instance to Switch Logs Lock held by open redo thread, used by other instances to force a log switch
KL LOB KSI LOCK KSI lock for buffer cache and wgc concurrency
KM Scheduler Synchronizes various Resource Manager operations
KO Multiple Object Checkpoint Coordinates checkpointing of multiple objects
KP Kupp Process Startup Synchronizes kupp process startup
KQ ASM Attributes Enque Single Inst Sync of ASM attributes
KR Enqueue-Delete Rule Synchronize delete rule and add rule to ruleset
KT Scheduler Plan Synchronizes accesses to the current Resource Manager plan
KV KEY VECTOR Synchronizes access to a key vector in an IMA query
KZ ASM:Purge Audit Coordinate GEN0 on multiple instances
L Library Cache Lock 1 Synchronizes accesses to library cache objects
MC SGA Log Operation Lock held during dml/ddl for a securefile log
MD Materialized View Log DDL Lock held during materialized view log DDL statements
MF SGA Log-Bkt Flush Lock held during flushing a log bucket
MG MGA service synchronize MGA service
MH AQ Notification Mail Host Lock used for recovery when setting Mail Host for AQ e-mail notifications
MK Master Key Serializes enc$ access
ML AQ Notification Mail Port Lock used for recovery when setting Mail Port for AQ e-mail notifications
MM MMON Management MMON AutoTask Management
MN LogMiner Synchronizes updates to the LogMiner dictionary and prevents multiple instances from preparing the same LogMiner session
MO MMON restricted session Serialize MMON operations for restricted sessions
MP SGA Log Partition Lock held during partitioning an instance log
MR Media Recovery Lock used to coordinate media recovery with other uses of datafiles
MS Materialized View Refresh Log Lock held during materialized view refresh to setup MV log
MV Online Datafile Move Synchronizes online datafile move operation or cleanup
MW MWIN Schedule This enqueue is used to serialize the calibration of the manageability schedules with the Maintenance Window
MX ksz synch Used to synchronize storage server info across all nodes
N Library Cache Pin 1 Synchronizes accesses to the contents of library cache objects
OC Outline Cache Synchronizes write accesses to the outline cache
OD Online DDLs Lock to prevent concurrent online DDLs
OE DLM Enqueue Owner Serialization Enqueue to serialize access to an Omni Enqueue Owner
OL Outline Name Synchronizes accesses to a particular outline name
OP Label Security Profile Synchronizes access to the ols$profile table
OQ OLAPI Histories Synchronizes access to olapi history resources
OT TEXT: Generic Lock Generic Lock on CTX index
OW Encryption Wallet Serializes wallet initialization and access
PA Privilege Capture API Lock held for modifying or reading a privilege capture
PB PDB DDL Operations Enqueue used to synchronize PDB DDL operations
PC Enqueue for kzppctxs Lock used for accesing kzppctx in SGA
PD Property Lock Serializes property update
PE Parameter Synchronizes system parameter updates
PF Password File Synchronizes accesses to the password file
PG Global Parameter Synchronizes global system parameter updates
PH AQ Notification Proxy Lock used for recovery when setting Proxy for AQ HTTP notifications
PI Remote PX Process Spawn Status Communicates remote Parallel Execution Server Process creation status
PJ DV Policy Admin API Lock held for Database Vault policy administration API
PL Transportable Tablespace Coordinates plug-in operation of transportable tablespaces
PM ASM PST Signalling Signals inter-instance access to ASM PST metadata
PP GES Plug and Play Enqueue Coordinates Adding Plug and Play Instances
PQ Queue Ptning global enq Synchronizes partitioning operations on queue table accross instances
PR Process Startup Synchronizes process startup
PS PX Process Reservation Parallel Execution Server Process reservation and synchronization
PT ASM Partnership and Status Table Gates inter-node synchronization of ASM PST metadata
PV KSV slave startup Synchronizes slave start_shut
PW Buffer Cache PreWarm Coordinates Direct Loads with Prewarmed cache buffers
PX PDB Recovery Operations Enqueue used to synchronize recovery-related PDB operations
PZ Password File Metadata Cache Synchronizes changes to the password file metadata cache
Q Row Cache Coordinates updates and accesses to row cache objects
RA Real-Time ADDM Flood Control Coordinate Flood Control in RAC
RB ASM Rollback Recovery Serializes ASM rollback recovery operations
RC Result Cache: Enqueue Accessing a result in the result-set cache
RD RAC Load synchronize update of RAC load info
RE Block Repair/Resilvering Synchronizes block repair/mirror resilvering operations
RF Data Guard Broker Synchronizes broker lock operation involving lock value
RG Replication Global Coordinates instance-wide replication / streams operations
RH SQLQ Autopurge This enqueue is used to serialize the purging of SQL quarantine configurations
RI Reader Farm SQL Isolation Reader Farm SQL Isolation
RK wallet_set_mkey Serializes wallet master key rekey
RL RAC Encryption Wallet Lock RAC encryption wallet lock
RM GES Resource Remastering Coordinates Global Enqueue Service resource remastering
RN Redo Log Nab Computation Coordinates nab computations of online logs during recovery
RO Multiple Object Reuse Coordinates flushing of multiple objects
RP Resilver / Repair Enqueue held when resilvering is needed or when datablock is repaired from mirror
RQ Enqueue-Cross SCN Synchronize enqueue-dequeue-cross on SCN generation
RR Workload Capture and Replay Prevents concurrent invokers of DBMS_WORKLOAD_*
RS Reclaimable Space Lock held by a space reclaimable operation to allow other operations to wait for it
RT Redo Thread Thread locks held by LGWR, DBW0, and RVWR to indicate mounted or open status
RU Rolling Migration Serilized rolling migration operations
RW Materialized View Flags Lock used when creating or readingmaterialized view flags in detail tables
RX ASM Extent Relocation Lock Synchronizes relocating ASM extents
RZ RFS Logminer FLC Synchronizes updates to logminer foreign log cache
SA Purge SRL Coordinates access to SRL by RFS and recovery process
SB LogicalStandby Synchronizes Logical Standby metadata operations
SC System Change Number Coordinates system change number generation on multiple instances
SE Session Migration Lock used by transparent session migration
SF AQ Notification Sender Lock used for recovery when setting Sender for AQ e-mail notifications
SG OLS groups Synchronizes access to ols$groups table
SH Active Session History Flushing To prevent multiple concurrent flushers
SI Streams Table Instantiation Prevents muiltiple streams table instantiations
SJ KTSJ Slave Task Cancel Serializes cancelling task executed by slave process
SK Shrink Segment Serialize shrink of a segment
SL Serialize Lock request Request serialization to LCK0
SM SMON Serialization Lock to check SMON global work in RAC
SN PDB SGA ALLOCATION Synchronize PDB SGA Allocation
SO Shared Object Synchronizes access to Shared Object(PL/SQL Shared Object Manager)
SP Spare Enqueue 0 Spare enqueue 0 for one-off backports
SQ Sequence Cache Lock to ensure that only one process can replenish the sequence cache
SR Synchronized Replication Coordinates replication / streams operations
SS Sort Segment Ensures that sort segments created during parallel DML operations aren't prematurely cleaned up
ST Space Transaction Synchronizes space management activities in dictionary-managed tablespaces
SU SaveUndo Segment Serializes access to SaveUndo Segment
SV Sequence Ordering Lock to ensure ordered sequence allocation in RAC mode
SW Suspend Writes Coordinates the 'alter system suspend' operation
SX Spare Enqueue 1 Spare enqueue 1 for one-off backports
SY IM chunk populate Lock used to serialize in-memory chunk populates
SZ IM segment populate Lock used to serialize in-memory segment populates
TA Instance Undo Serializes operations on undo segments and undo tablespaces
TB SQL Tuning Base Existence Cache Synchronizes writes to the SQL Tuning Base Existence Cache
TC Tablespace Checkpoint Lock held to guarantee uniqueness of a tablespace checkpoint
TD KTF map table enqueue Serializes updates and inserts to the SMON_SCN_TIME mapping table
TE KTF broadcast Serializes broadcasts for flushes to SMON_SCN_TIME
TF Temporary File Serializes dropping of a temporary file
TG In Memory Temp Object access to imcdt global resource
TH Threshold Chain Serializes threshold in-memory chain access
TI In Memory Temp Object HT access to an imcdt object HT
TK Auto Task Serialization Prevents more than one AutoTask Background Slave from being spawned
TL Log Lock Serializes threshold log table read and update
TM DML Synchronizes accesses to an object
TO Temp Object Synchronizes DDL and DML operations on a temp object
TP Runtime Fixed Table Purge Lock used for fixed runtime table purge and definition
TQ Queue table enqueue Synchronizes access to queue table
TR Tablespace Rekey Coordinates tablespace rekey
TS Temporary Segment Serializes accesses to temp segments
TT Tablespace Serializes DDL operations on tablespaces
TU TOP UPD Enqueue to serialize updates to topology data
TW Cross-Instance Transaction Lock held by one instance to wait for transactions on all instances to finish
TX Transaction Lock held by a transaction to allow other transactions to wait for it
TZ in-memory Serialize in-memory area create/drop
UF AWR undostat stat flush for PDB This enqueue is used to serialize undo stat flush per PDB
UL User-defined Lock used by user applications
US Undo Segment Lock held to perform DDL on the undo segment
UT Transfer Unified Audit Records Lock held for transfer of unified audit records
V Library Cache Lock 3 Synchronizes accesses to library cache objects
WA AQ Notification Watermark Lock used for recovery when setting Watermark for memory usage in AQ notifications
WD AWR mail package retrieval Serialize retrieving AWR mail package
WE AWR auto-export Serialize auto-export of AWR data
WF AWR Flush This enqueue is used to serialize the flushing of snapshots
WG Write gather local enqueue Long term lock on wgc file state
WI AWR import Serialize the import of AWR dump files
WL Being Written Redo Log Coordinates access to redo log files and archive logs
WM WLM Plan Operations Synchronizes new WLM Plan activation
WN read_write_snapshot Synchronizes read write snapshot creation
WP AWR Purge This enqueue handles concurrency between purging and baselines
WR LNS archiving log Coordinates access to logs by Async LNS and ARCH/FG
WS Logwriter member Used by Logwriter to coordinate communication to databases
WT AWR CDB-Wide Table Lock This enqueue serializes CDB-Wide Operation on AWR Table
XB ASM Group Block lock Prevents client diskgroup use during storage reconfiguration
XC XDB Configuration Synchronization access to XDB configuration
XD Auto Online Exadata disks Serialize Auto Management of Exadata disks
XH AQ Notification No-Proxy Lock used for recovery when setting No Proxy Domains for AQ HTTP notifications
XK GES Test-Lock for KSIRESTXDV Lock used for internal testing
XL ASM Extent Fault Lock Keep multiple processes from faulting in the same extent chunk
XM RM Master Lock RMON which process this lock is the Master
XQ ASM Extent Relocation Enqueue ASM extent relocation
XR Quiesce / Force Logging Lock held during database quiesce or for database force logging
XY Internal Test Lock used for internal testing
Y Library Cache Pin 3 Synchronizes accesses to the contents of library cache objects
ZA Audit Partition Lock held for adding partition to Aud table
ZB in-memory TS Serialize in-memory tablespace create/drop
ZC IM FastStart Admin Lock used to serialize in-memory FS tasks
ZD IM FastStart CU Lock used to serialize in-memory FS CU
ZF FGA Partition Lock held for adding partition to Fga table
ZG File Group Coordinates file group operations
ZH Compression Analyzer Synchronizes analysis and insert into compression$, prevents multiple threads analyzing the same table during a load
ZL Foreign Log Maintenance Synchronizes updates to foreign log metadata by LogMiner reader and RFS processes
ZP Private Temp Table synchronize Synchronizes add/remove/read of PTT-GTT mapping
ZQ quiesce enq Serialize SMU create and load quiesce
ZR ZDLRA Server Lock held by a recovery server operation to allow other operations to wait for it
ZS Spillover Audit files Lock held for exclusive access on a Spillover audit file
ZX repopulation file access synchronizes file operations during repopulation
ZZ Global Context Action Lock held for updating Global context hash tables