Oracle Enqueue Names

Oracle Enqueue Names

Enqueue（排队锁）是Oracle数据库中用于协调多进程并发访问共享资源的锁机制。

This appendix lists Oracle enqueues. Enqueues are shared memory structures (locks) that serialize access to database resources. They can be associated with a session or transaction. Enqueue names are displayed in the LOCK_TYPE column of the DBA_LOCK and DBA_LOCK_INTERNAL data dictionary views.

A resource uniquely identifies an object that can be locked by different sessions within an instance (local resource) or between instances (global resource). Each session that tries to lock the resource will have an enqueue on the resource.

Note:
The names of enqueues and their definitions may change from release to release.

The Oracle enqueues are:

BL, Buffer Cache ManagementBR, Backup/RestoreCF, Controlfile TransactionCI, Cross-instance Call InvocationCU, Bind EnqueueDF, DatafileDL, Direct Loader Index CreationDM, Database MountDR, Distributed Recovery ProcessDW, SecureFilesDX, Distributed TransactionFP, File ObjectFS, File SetHW, High-Water LockIN, Instance NumberIR, Instance RecoveryIS, Instance StateIV, Library Cache InvalidationJI, Enqueue used during AJV snapshot refreshJQ, Job QueueKK, Redo Log "Kick"KP, contention in Oracle Data Pump startup and shutdown processesKO, Multiple Object CheckpointL[A-P], Library Cache LockLS, Log Start or SwitchMM, Mount DefinitionMR, Media RecoveryN[A-Z], Library Cache PinPE, ALTER SYSTEM SET PARAMETER = VALUEPF, Password FilePI, Parallel SlavesPR, Process StartupPS, Parallel Slave SynchronizationQ[A-Z], Row CacheRO, Object ReuseRT, Redo ThreadRW, Row WaitSC, System Change NumberSM, SMONSN, Sequence NumberSQ, Sequence Number EnqueueSR, Synchronized ReplicationSS, Sort SegmentST, Space Management TransactionSV, Sequence Number ValueTA, Transaction RecoveryTC, Thread CheckpointTE, Extend TableTM, DML EnqueueTO, Temporary Table Object EnqueueTS, Temporary Segment (also TableSpace)TT, TablespaceTX, TransactionUL, User-defined LocksUN, User NameUS, Undo Segment, SerializationWL, Being Written Redo LogXA, Instance Attribute LockXI, Instance Registration LockZA, Exclusive Lock When Moving Audit Table

Enqueue状态查看

-- 查看当前enqueue等待
SELECT * FROM v$enqueue_stat WHERE cum_wait_time > 0;-- 详细enqueue持有/等待情况
SELECT e.addr, e.kaddr, e.sid, e.type, e.id1, e.id2, e.lmode, e.request, e.ctime
FROM v$lock e
WHERE e.type IN ('TX','TM','ST','TT','US','SQ','BL','HW')
ORDER BY e.ctime DESC;

注：Oracle不同版本可能有所差异，19c新增了TA(Tablespace Alter)等新enqueue类型。