An Enqueue Wait occurs when a session is waiting for a database-level lock that is currently held by another session in an incompatible mode. Unlike latches, enqueues are sophisticated, "heavyweight" locking mechanisms that use a FIFO (First-In-First-Out) queue. If you are second in line, you are guaranteed to be the next person to get the lock once it's released.

1. Latch vs. Enqueue: The Key Difference

2. Common Enqueue Wait Types

In modern Oracle versions (12c through 23c), you won't just see a generic "enqueue" wait. Oracle tells you exactly what kind of lock is causing the holdup. These are prefixed with enq:.

A. enq: TX - row lock contention

The most common enqueue wait.

• The Cause: Session A has updated a row but hasn't committed or rolled back yet. Session B tries to update or delete that same row and must wait.

• The Fix: Find the "Blocking Session" and either have them commit or investigate why their transaction is taking so long.

B. enq: TM - contention

This is a "DML Enqueue" that protects the structure of a table.

• The Cause: Usually caused by unindexed Foreign Keys. When you delete a row in a parent table, Oracle may need to lock the entire child table to ensure referential integrity if the child column isn't indexed.

• The Fix: Index your foreign keys!

C. enq: ST - contention

The "Space Management" enqueue.

• The Cause: Occurs when many sessions are trying to allocate or deallocate space (extents) at the same time, often in temporary tablespaces or during massive data loads.

• The Fix: Use Locally Managed Tablespaces (LMT) and ensure your extent sizes are uniform.

D. enq: HW - contention

The "High Water Mark" enqueue.

• The Cause: Multiple sessions are trying to insert data into the same table so fast that they are fighting over who gets to move the "High Water Mark" (the boundary of used space in a file).

• The Fix: Use Automatic Segment Space Management (ASSM).

3. How to Diagnose Enqueue Waits

When a session is stuck in an enqueue wait, the first place to look is V$SESSION.

SELECT sid, 
       serial#, 
       event, 
       blocking_session, 
       seconds_in_wait 
FROM v$session 
WHERE event LIKE 'enq%';

• Event: Tells you the type (e.g., enq: TX - row lock contention).

• Blocking_Session: This is the "ID" of the person holding the lock. This is the person you need to talk to!

4. The "NOWAIT" Alternative

If your application can't afford to wait in a queue, you can use the NOWAIT clause in your SQL. Instead of hanging, Oracle will immediately return an error (ORA-00054: resource busy) if the lock isn't available.

SELECT * FROM orders WHERE order_id = 500 FOR UPDATE NOWAIT;

5. Summary: Why Enqueues Matter

Enqueue waits are rarely a "database bug." They are almost always an application design issue. High enqueue waits mean your users are stepping on each other's toes.

1. Row Locks: Keep transactions short. Never leave a transaction open while waiting for user input.

2. TM Locks: Index those foreign keys!

3. Space Locks: Use modern storage management (LMT and ASSM).

Final Thought

If a Latch is a turnstile, an Enqueue is a DMV waiting line. If the line is out the door, you don't necessarily need a faster clerk (CPU); you need to figure out why everyone is showing up at the same time with the same paperwork.