Enqueue

Enqueue waits are locking mechanisms that control the access to shared resources. There are various modes of enqueues.

The following query gives you the detail of Sessions holding the lock, the lock type, mode.

SQL> select DECODE(request,0,'Holder: ','Waiter: ')sid sess, id1, id2, lmode, request, type
FROM V$LOCK
WHERE (id1, id2, type) IN (SELECT id1, id2, type FROM V$LOCK WHERE request>0)
ORDER BY id1, request ;

The most common enqueue waits are discussed below,

TYPE: TM (Table Lock)
LMODE: 3
CAUSE: Unindexed Foreign Key
SOLUTION: The holder has to issue commit or rollback. To avoid this kind of lock in first place create indexes on the foreign key columns. You can do this by taking the ID1 column value in v$lock. This ID1 value is the object ID of the child table. Use dba_objects dictionary table and get the object name. Create the index on the foreign key column.


TYPE: TX (Row level lock)
LMODE: 6
CAUSE: Updating or deleting rows that are currently locked by another transaction.
SOLUTION: Application issue. The lock is released when the holding session issues a commit or rollback. Killing the holding session will rollback the transaction.
RESOURCE LOCKED: Issue the following query to find the resource that is locked.

SQL> select c.sid waiter_sid, a.object_name, a.object_typefrom dba_objects a, v$session b, v$session_wait cwhere (a.object_id = b.row_wait_obj# or a.data_object_id = b.row_wait_obj#)and b.sid = c.sidand chr(bitand(c.P1,-16777216)/16777215) chr(bitand(c.P1,16711680)/65535) = ’TX’and c.event = ’enqueue’;


TYPE: TX (ITL Shortage)
LMODE: 4
CAUSE: i) ITL (Interested Transaction List) Shortage. ii) Unique Key Enforcement. iii) Bitmap index Entry.
SOLUTION: To see whether the wait is due to ITL shortage dump the data block and see how many ITL slots are being used.

SQL> Alter system dump datafile block

If it is indeed due to ITL shortage, then increase the INITRANS value of the object. Also increase the PCTFREE value of the objects.

ii) If the Wait is due to the Unique key Enforcement (i.e, if more than one session inserts the same value that has unique or primary key then the insert will not succeed). If the first session that inserted the value commits then the waiting session will receive the unique constraint violation error. If the first session rollsback then the second session succeeds.

iii) Bitmap Index Entry: A bitmap entry covers a range of ROWIDs. When a bitmap entry is locked all the ROWIDs that correspond to the bitmap entry are locked. When multiple users attempt to delete or update different rows that have the same bitmap entry then a wait for TX in mode 4 will occur.

It is difficult to find whether the lock was due to unique key enforcement or bitmap index entry by merely looking in to the V$Lock view. You have to capture the SQL statements that holder and waiter have issued. If the statement is an insert then wait is due to the unique key enforcement. If the statement is update or delete then the wait is due to the bitmap index entry.

Thanks