[Oracle] Lock Type, 락 오브젝트 확인하기

락 뷰와 세션 뷰를 조인하여 트랜잭션 락을 조회해 봅니다. 두 가지 뷰는 SID 컬럼으로 조인할 수 있습니다. TX, TM, UL과 같은 락 타입에 대한 설명은 아래에 있습니다.

락 오브젝트 확인하기

SELECT LK.SID,
       SE.USERNAME,
       SE.OSUSER,
       SE.MACHINE,
       DECODE (LK.TYPE,
               'TX', 'Transaction',
               'TM', 'DML',
               'UL', 'PL/SQL User Lock',
               LK.TYPE)
          LOCK_TYPE,
       DECODE (LK.LMODE,
               0, 'None',
               1, 'Null',
               2, 'Row-S (SS)',
               3, 'Row-X (SX)',
               4, 'Share',
               5, 'S/Row-X (SSX)',
               6, 'Exclusive',
               TO_CHAR (LK.LMODE))
          MODE_HELD,
       DECODE (LK.REQUEST,
               0, 'None',
               1, 'Null',
               2, 'Row-S (SS)',
               3, 'Row-X (SX)',
               4, 'Share',
               5, 'S/Row-X (SSX)',
               6, 'Exclusive',
               TO_CHAR (LK.REQUEST))
          MODE_REQUESTED,
       TO_CHAR (LK.ID1) LOCK_ID1,
       TO_CHAR (LK.ID2) LOCK_ID2,
       DECODE (BLOCK,  0, 'No',  1, 'Yes',  2, 'Global') BLOCK,
       SE.LOCKWAIT
  FROM V$LOCK LK, V$SESSION SE
 WHERE (LK.TYPE = 'TX') AND (LK.SID = SE.SID)

유저 락 확인 쿼리

락 뷰와 세션 뷰에 더불어 오브젝트 뷰를 통해 DML과 PL/SQL 유저 락을 확인하는 쿼리입니다.

SELECT LK.SID,
       SE.USERNAME,
       SE.OSUSER,
       SE.MACHINE,
       DECODE (LK.TYPE,
               'TX', 'Transaction',
               'TM', 'DML',
               'UL', 'PL/SQL User Lock',
               LK.TYPE)
          LOCK_TYPE,
       DECODE (LK.LMODE,
               0, 'None',
               1, 'Null',
               2, 'Row-S (SS)',
               3, 'Row-X (SX)',
               4, 'Share',
               5, 'S/Row-X (SSX)',
               6, 'Exclusive',
               TO_CHAR (LK.LMODE))
          MODE_HELD,
       DECODE (LK.REQUEST,
               0, 'None',
               1, 'Null',
               2, 'Row-S (SS)',
               3, 'Row-X (SX)',
               4, 'Share',
               5, 'S/Row-X (SSX)',
               6, 'Exclusive',
               TO_CHAR (LK.REQUEST))
          MODE_REQUESTED,
       TO_CHAR (LK.ID1) LOCK_ID1,
       TO_CHAR (LK.ID2) LOCK_ID2,
       OB.OWNER,
       OB.OBJECT_TYPE,
       OB.OBJECT_NAME,
       DECODE (LK.BLOCK,  0, 'No',  1, 'Yes',  2, 'Global') BLOCK,
       SE.LOCKWAIT
  FROM V$LOCK LK, DBA_OBJECTS OB, V$SESSION SE
 WHERE     LK.TYPE IN ('TM', 'UL')
       AND LK.SID = SE.SID
       AND LK.ID1 = OB.OBJECT_ID(+)

Lock Type

락 뷰에는 TYPE 컬럼이 영문 약자로 나와있는데요, 해당 값에 대한 설명은 락 타입 뷰에 있답니다. TX는 트랜잭션 락을 뜻합니다. TM은 DML(Data Manipulation Language: 데이터 조작 언어), 즉 INSERT, UPDATE, DELETE 락을 뜻합니다. 그리고 UL은 PL/SQL 유저 락을 뜻합니다.

  SELECT TYPE, NAME, DESCRIPTION
    FROM V$LOCK_TYPE
ORDER BY TYPE;

Auto BMR

A general class of locks used by auto BMR for various purposes

ASM Disk AU Lock

Synchronizes accesses to a specific ASM disk AU

Edition Lock

Prevent Dropping an edition in use

Advisor Framework

This enqueue is used to serialize access to an advisor task

Analytic Workspace Generation

Synchronizes generation use of a particular workspace

GES Deadlock Test

Lock used for internal testing

ASM Enqueue

ASM instance general-purpose enqueue

MultiWriter Object Access

Synchronizes access to objects and scalar variables

ASM Relocation Lock

Protects locked extent pointers during ASM file relocation

Service Operations

Synchronizes new service activation

Alter Tablespace

Serializes 'alter tablespace' operations

ASM volume locks

id1 is for persistent DG number locking. id2 is for other volume serialization

Analytic Workspace

Synchronizes access to Analytic Workspace resources

KSXA Test Affinity Dictionary

Affinity Dictionary test affinity synchronization

Global Transaction Branch

2PC distributed transaction branch across RAC instances

BLOOM FILTER

Synchronize access to a bloom filter in a parallel statement

Backup/Restore

Lock held by a backup/restore operation to allow other operations to wait for it

Calibration

Lock used by IO Calibration

Controlfile Transaction

Synchronizes accesses to the controlfile

Cross-Instance Call Invocation

Coordinates cross-instance function invocations

Label Security cache

Synchronizes accesses to label cache and label tables

ASM Instance Enqueue

ASM instance and gate enqueue

KTCN REG enq

Enqueue held for registrations for change notifications

KTUCLO Master Slave enq

Enqueue held for determining Master Slaves

Cleanup querycache registrations

Serializes access to cleanup client query cache registrations

Reuse Block Range

Coordinates checkpointing of block range reuse

Block Change Tracking

A general class of locks used by change tracking for various purposes

Cursor

Recovers cursors in case of death while compiling

TEXT: Index Specific Lock

Index Specific Lock on CTX index

DbsDriver

Synchronizes modification of database wide supplementallogging attributes

ASM Local Disk Group

Synchronizes local accesses to ASM disk groups

Datafile Online in RAC

Enqueue held by foreground or DBWR when a datafile is brought online in RAC

ASM Disk Group Modification

Synchronizes accesses to ASM disk groups

GES Internal

Coordinates Global Enqueue Service deadlock detection

Direct Loader Index Creation

Lock to prevent index DDL during direct load

Database Mount/Open

Enqueue held by foreground or DBWR to synchronize database mount/open with other operations

Diskgroup number generator

Serializes Group number generations

ASM Disk Online Lock

Synchronizes disk onlines and their recovery

LDAP Parameter

Synchronizes access to LDAP parameters

ASM RBAL doorbell

Inter-RBAL process metadata invalidation notification

Distributed Recovery

Serializes the active distributed recovery operation

Database Suspend

Prevents a database suspend during LMON reconfiguration

Default Temporary Tablespace

Serializes changing the default temporary table space and user creation

Diana Versioning

Synchronizes access to lower-version Diana (PL/SQL intermediate representation)

In memory Dispenser

Serialize in memory dispenser operations

Distributed Transaction

Serializes tightly coupled distributed transaction branches

Library Cache Lock 2

Synchronizes accesses to library cache objects

ASM File Access Lock

Synchronizes accesses to open ASM files

Format Block

Ensures that only one process can format data blocks in auto segment space managed tablespaces

Disk Group Chunk Mount

controls access to an ACD chunk

Flashback Database

Coordinate flashback database

KTFA Recovery

Flashback archive Enqueue to serialize recovery

ACD Relocation Gate Enqueue

ACD relocation serialization

Flashback database log

Synchronize access to flashback database log

File Mapping

Synchronizes access to global file mapping state

File Object

Synchronizes various File Object(FOB) operations

Disk Group Recovery

enqueue to control ASM recovery

File Set / Dictionary Check

Enqueue used to synchronize recovery and file operations or synchronize dictionary check

Disk Group Redo Generation

controls the privilege to generate redo in a thread

DBFUS

This enqueue is used to serialize the capture of the DB Feature Usage and High Water Mark Statistics

Flashback Writer

Coordinate RVWR on multiple instances

ACD Xtnt Info CIC

serialize ACD relocation CIC

ASM Freezing Cache Lock

Freezes ASM Cache for a diskgroup

Library Cache Pin 2

Synchronizes accesses to the contents of library cache objects

ASM Disk Header

Serializes accesses to ASM SGA data structures

Queue Page

Synchronizes accesses to queue pages

Hash Queue

Synchronizes the creation of new queue IDs

Direct Loader High Water Mark

Lock used to broker the high water mark during parallel inserts

Segment High Water Mark

Lock used to broker the high water mark during parallel inserts

Internal

NID

Lock held by a NID operation to allow other operations to wait for it

Label Security

Synchronizes accesses to internal label data structures

Kti blr lock

Serializes block recovery for an IMU txn

Instance Recovery

Synchronizes instance recovery

Instance State

Enqueue used to synchronize instance state changes

In-Mem Temp Table Meta Creation

Synchronizes accesses to a temp object's metadata

Library Cache Invalidation

Synchronizes library cache object invalidations across instances

INSTANCE LOCK

Proctects the lock name space used by the instance

Job Queue Date

Synchronizes dates between job queue coordinator and slave processes

Materialized View

Lock held during materialized view operations (like refresh, alter) to prevent concurrent operations on the same materialized view

Job Queue

Lock to prevent multiple instances from running a single job

Job Scheduler

Synchronizes accesses to the job cache

SQL STATEMENT QUEUE

synchronize SQL statement queue operations

Scheduler Master DBRM

Determine DBRM master

ASM Cached Attributes

Synchronization of ASM cached attributes

Kick Instance to Switch Logs

Lock held by open redo thread, used by other instances to force a log switch

LOB KSI LOCK

KSI lock for buffer cache and wgc concurrency

Scheduler

Synchronizes various Resource Manager operations

Multiple Object Checkpoint

Coordinates checkpointing of multiple objects

Kupp Process Startup

Synchronizes kupp process startup

ASM Attributes Enque

Single Inst Sync of ASM attributes

Scheduler Plan

Synchronizes accesses to the current Resource Manager plan

Library Cache Lock 1

Synchronizes accesses to library cache objects

Materialized View Log DDL

Lock held during materialized view log DDL statements

AQ Notification Mail Host

Lock used for recovery when setting Mail Host for AQ e-mail notifications

Master Key

Serializes enc$ access

AQ Notification Mail Port

Lock used for recovery when setting Mail Port for AQ e-mail notifications

LogMiner

Synchronizes updates to the LogMiner dictionary and prevents multiple instances from preparing the same LogMiner session

MMON restricted session

Serialize MMON operations for restricted sessions

Media Recovery

Lock used to coordinate media recovery with other uses of datafiles

Materialized View Refresh Log

Lock held during materialized view refresh to setup MV log

Online Datafile Move

Synchronizes online datafile move operation or cleanup

MWIN Schedule

This enqueue is used to serialize the calibration of the manageability schedules with the Maintenance Window

ksz synch

Used to synchronize storage server info across all nodes

Library Cache Pin 1

Synchronizes accesses to the contents of library cache objects

Outline Cache

Synchronizes write accesses to the outline cache

Online DDLs

Lock to prevent concurrent online DDLs

Outline Name

Synchronizes accesses to a particular outline name

OLAPI Histories

Synchronizes access to olapi history resources

TEXT: Generic Lock

Generic Lock on CTX index

Encryption Wallet

Serializes wallet initialization and access

Property Lock

Serializes property update

Parameter

Synchronizes system parameter updates

Password File

Synchronizes accesses to the password file

Global Parameter

Synchronizes global system parameter updates

AQ Notification Proxy

Lock used for recovery when setting Proxy for AQ HTTP notifications

Remote PX Process Spawn Status

Communicates remote Parallel Execution Server Process creation status

Transportable Tablespace

Coordinates plug-in operation of transportable tablespaces

ASM PST Signalling

Signals inter-instance access to ASM PST metadata

Process Startup

Synchronizes process startup

PX Process Reservation

Parallel Execution Server Process reservation and synchronization

ASM Partnership and Status Table

Gates inter-node synchronization of ASM PST metadata

KSV slave startup

Synchronizes slave start_shut

Buffer Cache PreWarm

Coordinates Direct Loads with Prewarmed cache buffers

Row Cache

Coordinates updates and accesses to row cache objects

ASM Rollback Recovery

Serializes ASM rollback recovery operations

Result Cache: Enqueue

Accessing a result in the result-set cache

RAC Load

synchronize update of RAC load info

Block Repair/Resilvering

Synchronizes block repair/mirror resilvering operations

Data Guard Broker

Synchronizes broker lock operation involving lock value

wallet_set_mkey

Serializes wallet master key rekey

RAC Encryption Wallet Lock

RAC encryption wallet lock

GES Resource Remastering

Coordinates Global Enqueue Service resource remastering

Redo Log Nab Computation

Coordinates nab computations of online logs during recovery

Multiple Object Reuse

Coordinates flushing of multiple objects

Resilver / Repair

Enqueue held when resilvering is needed or when datablock is repaired from mirror

Workload Capture and Replay

Prevents concurrent invokers of DBMS_WORKLOAD_*

Reclaimable Space

Lock held by a space reclaimable operation to allow other operations to wait for it

Redo Thread

Thread locks held by LGWR, DBW0, and RVWR to indicate mounted or open status

Rolling Migration

Serilized rolling migration operations

Materialized View Flags

Lock used when creating or readingmaterialized view flags in detail tables

ASM Extent Relocation Lock

Synchronizes relocating ASM extents

LogicalStandby

Synchronizes Logical Standby metadata operations

System Change Number

Coordinates system change number generation on multiple instances

Session Migration

Lock used by transparent session migration

AQ Notification Sender

Lock used for recovery when setting Sender for AQ e-mail notifications

Active Session History Flushing

To prevent multiple concurrent flushers

Streams Table Instantiation

Prevents muiltiple streams table instantiations

KTSJ Slave Task Cancel

Serializes cancelling task executed by slave process

Shrink Segment

Serialize shrink of a segment

Serialize Lock request

Request serialization to LCK0

SMON Serialization

Lock to check SMON global work in RAC

Shared Object

Synchronizes access to Shared Object(PL/SQL Shared Object Manager)

Spare Enqueue

Spare enqueue for one-off backports

Sequence Cache

Lock to ensure that only one process can replenish the sequence cache

Synchronized Replication

Coordinates replication / streams operations

Sort Segment

Ensures that sort segments created during parallel DML operations aren't prematurely cleaned up

Space Transaction

Synchronizes space management activities in dictionary-managed tablespaces

SaveUndo Segment

Serializes access to SaveUndo Segment

Sequence Ordering

Lock to ensure ordered sequence allocation in RAC mode

Suspend Writes

Coordinates the 'alter system suspend' operation

Instance Undo

Serializes operations on undo segments and undo tablespaces

SQL Tuning Base Existence Cache

Synchronizes writes to the SQL Tuning Base Existence Cache

Tablespace Checkpoint

Lock held to guarantee uniqueness of a tablespace checkpoint

KTF map table enqueue

Serializes updates and inserts to the SMON_SCN_TIME mapping table

KTF broadcast

Serializes broadcasts for flushes to SMON_SCN_TIME

Temporary File

Serializes dropping of a temporary file

Threshold Chain

Serializes threshold in-memory chain access

Auto Task Serialization

Prevents more than one AutoTask Background Slave from being spawned

Log Lock

Serializes threshold log table read and update

DML

Synchronizes accesses to an object

Temp Object

Synchronizes DDL and DML operations on a temp object

Runtime Fixed Table Purge

Lock used for fixed runtime table purge and definition

Queue table enqueue

Synchronizes access to queue table

Temporary Segment

Serializes accesses to temp segments

Tablespace

Serializes DDL operations on tablespaces

Cross-Instance Transaction

Lock held by one instance to wait for transactions on all instances to finish

Transaction

Lock held by a transaction to allow other transactions to wait for it

User-defined

Lock used by user applications

Undo Segment

Lock held to perform DDL on the undo segment

Library Cache Lock 3

Synchronizes accesses to library cache objects

AQ Notification Watermark

Lock used for recovery when setting Watermark for memory usage in AQ notifications

AWR Flush

This enqueue is used to serialize the flushing of snapshots

Write gather local enqueue

Long term lock on wgc file state

Being Written Redo Log

Coordinates access to redo log files and archive logs

WLM Plan Operations

Synchronizes new WLM Plan activation

AWR Purge

This enqueue handles concurrency between purging and baselines

LNS archiving log

Coordinates access to logs by Async LNS and ARCH/FG

LogWriter Standby

Used by LogWriter to coordinate communication to standby databases

ASM Group Block lock

Prevents client diskgroup use during storage reconfiguration

XDB Configuration

Synchronization access to XDB configuration

Auto Online Exadata disks

Serialize Auto Management of Exadata disks

AQ Notification No-Proxy

Lock used for recovery when setting No Proxy Domains for AQ HTTP notifications

ASM Extent Fault Lock

Keep multiple processes from faulting in the same extent chunk

ASM Extent Relocation Enqueue

ASM extent relocation

Quiesce / Force Logging

Lock held during database quiesce or for database force logging

Internal Test

Lock used for internal testing

Library Cache Pin 3

Synchronizes accesses to the contents of library cache objects

Audit Partition

Lock held for adding partition to Aud table

FGA Partition

Lock held for adding partition to Fga table

File Group

Coordinates file group operations

Compression Analyzer

Synchronizes analysis and insert into compression$, prevents multiple threads analyzing the same table during a load

Global Context Action

Lock held for updating Global context hash tables