Enqueues are sophisticated locks for managing access to shared resources like tables, rows, jobs, and redo threads. An enqueue can be requested in different levels/mode: null, row share, row exclusive, share, share row exclusive or exclusive. This wait event indicates a wait for a lock that is held by another session (or sessions) in an incompatible mode to the requested mode.
Isolating contention:
Once an enqueue resource contention problem has been identified with Ignite, one can quickly isolate the SQL and sessions that are suffering. Often, the SQL is a good clue to what objects have locking contention.
During a period of time when locking is typically a problem, use the following query to find out what session is requesting a lock, the type and mode of the requested lock and the session that is blocking.
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;
Solutions
There are many types of locks in Oracle and each has a unique contention remedy. The following are the most common sources of contention:
TX Transaction Lock
Generally due to application or table setup issues. See Metalink Note:62354.1 for example scenarios which can cause TX lock waits.
TM DML enqueue
Generally due to application issues, particularly if foreign key constraints have not been indexed. The following two articles describe referential integrity issues related to TM locking:
Example TM locks During Referential Integrity Enforcement — Metalink Note:38373.1
TM locks and Foreign Key Constraints — Metalink Note:33453.1
ST Space management enqueue
Usually caused by too much space management occurring (Eg: small extent sizes, lots of sorting etc..) See Metalink Note:33567.1 for more information about the ST enqueue.
Comment