Decoding Oracle Enqueue Locks: Queues, Resource Structures, and Hash Buckets

In Oracle, almost every component except the PGA is a shared resource that must be protected, so operations on these resources are serialized through enqueue queues. Each type of shared resource (e.g., tables, indexes, transactions) has its own queue, identified by two‑character names such as TM, TX, JQ, etc.; the full list can be found in the V$LOCK view.

Queue Identifiers

Taking TM as an example, it is the DML queue lock obtained before modifying a table. For each table a separate TM queue exists, named using the format TM‑OID‑0, where OID is the object ID. If table AA has OID 6636, its queue identifier is TM‑6636‑0. Generally, queue names follow the pattern queue‑name‑ID1‑ID2, with ID1 and ID2 being specific parameters for that queue type.

Resource Structures

When a session accesses a particular table, Oracle creates a resource structure (KSQRS) in the SGA to hold the owners, waiters, and converters for that queue. Each resource structure contains three lock structures (Ksqlk) representing linked lists of sessions and lock modes.

The three lock‑structure lists are:

If a session holds the lock, the lock structure appears in the owners list.

If a session is waiting to acquire the lock, it appears in the waiters list.

If a session holds the lock but is waiting for it to be converted to another mode, it appears in the converters list.

The total number of rows in the resource table is controlled by the initialization parameter ENQUEUE_RESOURCES and can be inspected via X$KSQRS, V$RESOURCE, and V$RESOURCE_LIMIT. Example query output shows a current utilization of 32 rows out of 968 allocated, with the maximum utilization also at 32 and the limit reported as UNLIMITED.

Resource Structure Hash Table

To locate a resource structure quickly, Oracle hashes the resource identifier into a hash bucket. The hash table size is fixed (default 375 buckets, derived from the _enqueue_hash parameter based on the SESSION count) and does not require protection, but each bucket’s chain is protected by the _enqueue_hash_chain_latches latch.

If the number of resource structures (e.g., 992 rows) exceeds the number of hash buckets, multiple structures share a bucket, which can increase contention on the hash‑chain latch.

Practical Example

Two sessions insert rows into table a1:

sid=10 pid=11> insert into a1 values(1,1,1);
sid=12 pid=12> insert into a1 values(2,2,2);

Querying v$resource shows a TM queue for the table:

sid=9 pid=10> select * from v$resource where type='TM';
ADDR      TY   ID1   ID2
7B6D5C40 TM  6657   0

Querying v$lock reveals two lock entries for the same TM queue:

sid=13 pid=13> select * from v$lock where sid>=8;
ADDR      KADDR      SID TY ID1   ID2 LMODE REQUEST CTIME BLOCK
7AF3A088 7AF3A0A0   10  TM 6657  0   3    0       452    0
7AF3A190 7AF3A1A0   12  TM 6657  0   3    0       84     0

Diagnosing Enqueue Locks

To trace enqueue activity, enable the event:

alter session set events 'immediate trace name enqueues level 4';

Key hidden parameters: _enqueue_hash – number of hash buckets (default 375, calculated as ((sessions‑10)*2)+55). _enqueue_hash_chain_latches – number of latches protecting hash‑chain lists. _enqueue_locks – total number of enqueue locks (default 2230).

Useful diagnostic queries:

set linesize 800
col KTCXBNAM for a20
select * from x$ktcxb where ktcxblkp in (select kaddr from v$lock where type='TX');
select * from x$ktadm where ksqlkadr in (select kaddr from v$lock where type='TM');
select * from v$resource_limit where resource_name like 'enqueue%';

The enqueue_locks count includes both TX locks (controlled by the transactions parameter) and TM locks (controlled by DML_LOCKS), which are subsets of the total enqueue locks.

Key Takeaways

Understanding Oracle’s enqueue mechanism—queues, resource structures, and hash buckets—helps DBAs diagnose lock contention, tune hidden parameters, and interpret diagnostic views such as V$RESOURCE, V$LOCK, and X$ tables.