Understanding Oracle Enqueue Locks, Resource Structures, and Hash Tables

Queue and Shared Resources

In Oracle, every object except the PGA (Program Global Area) is a shared resource that can be accessed concurrently by multiple sessions or processes, so access must be protected by a queue (enqueue). Each type of shared resource has its own queue, identified by a two‑byte name such as TM, TX, or JQ. The full list of queue names is documented in the V$LOCK view.

Queue Identifier

The TM queue lock is the DML lock used when a table is modified. Oracle creates a separate TM queue for each table; the identifier follows the format TM‑OID‑0, where OID is the object ID of the table. For example, a table with OID 6636 has the queue identifier TM‑6636‑0. Generally, queue identifiers are composed as queue‑name‑ID1‑ID2, with ID1 and ID2 being parameters specific to the queue type.

Resource Structure

When a session issues a DML statement on a table, Oracle creates a resource structure (KSQRS) for that table in the SGA. Each resource structure maintains three linked lists: owners, waiters, and converters. These lists are represented by lock structures (Ksqlk) that contain session IDs, lock modes, and other details.

Resource Structure Hash Table

To locate a resource structure quickly, Oracle stores them in a hash table. The hash value is computed from the resource identifier, and each hash bucket holds a chain of resource structures. The number of hash buckets is controlled by the hidden parameter _enqueue_hash, whose default value (375) is derived from the SESSION parameter: ((sessions‑10)*2)+55. Because the resource table can contain many more rows than hash buckets (e.g., 992 rows vs. 375 buckets), multiple structures share a bucket, which can increase contention on the _enqueue_hash_chain_latches latch that protects the bucket chains.

Observing Queues and Locks

Example DML statements create TM queues and corresponding lock structures:

-- Session 10
insert into a1 values(1,1,1);
-- Session 12
insert into a1 values(2,2,2);

Querying the V$LOCK view shows the TM lock addresses (e.g., 7B6D5C40) and object IDs (e.g., 6657). To inspect the underlying structures, you can enable trace events and query internal X$ tables:

alter session set events 'immediate trace name enqueues level 4';
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');

The current usage of enqueue resources can be seen in V$RESOURCE_LIMIT:

select * from v$resource_limit where resource_name like 'enqueue%';

Parameters Controlling Queues

The total number of enqueue locks is governed by the hidden parameter _enqueue_locks (default 2230). The transactions parameter defines how many TX locks are available, and DML_LOCKS defines the number of TM locks; both are subsets of the total enqueue locks.