Oracle enqueue waits indicate that a seesion is waiting for a lock that is held by another user (or sessions). For Oracle 9i, there is a specific Enqueue wait event. For Oracle 10g and later, the Enqueue wait event has been separated into more than 200 unique wait events, which each include more specific information about the related lock type.
About Oracle locking
In Oracle databases, many users may update the same information at roughly the same time. Locking allows one user to update data at a given moment so that another person cannot modify the same data. The data is locked by the transaction until it is committed or rolled back and this is known as data concurrency. Another purpose of Oracle locking is to ensure that all processes can always read the original data as they were at the time the query began even though other users could be modifying the underlying data. This is known as read consistency.
How Oracle locking can cause problems
Although locks are a necessity in Oracle, they can create performance issues. Each time a user issues a lock, another user would be prevented from processing the locked data. Oracle locking allows a variety of locks depending on the resources required – a single row, many rows, an entire table, many tables, etc. However, the larger the scope of the lock, the more users will be prevented from processing the data. The Oracle enqueue wait event is the best indication of locking in Oracle databases.
Oracle 9i enqueue wait events
In Oracle 9i, when a session is waiting on the “enqueue” wait event, this indicates a wait for an Oracle lock that is held by another user (or sessions) in an incompatible mode to the requested mode. When sessions are found waiting on an enqueue, the following query can be used to find out which session is requesting the lock, the type and mode of the requested lock and the session that is blocking the request:
>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
In Oracle 9i there are approximately 40 types of locks specified by the TYPE column in V$LOCK and each has a unique solution set. The following are examples of the types of locks:
TX: This enqueue is a transaction lock and is typically caused by incorrect application logic or table setup issues.
TM: This enqueue represents a DML lock and is generally due to application issues, particularly if foreign key constraints have not been indexed.
ST: When Oracle performs space management operations (such as allocating temporary segments for a sort, allocating extents for a table, etc), the user session waits on the ST enqueue.
Oracle 10g enqueue wait events
Oracle 10g makes the process of analyzing locks easier by separating the “enqueue” wait event from Oracle 9i into over 200 distinct wait events. Oracle also includes more information about the lock type within the wait event name. For example, an enqueue wait event named “enq: TX – row lock contention” indicates that row locking is occurring, while “enq: TX – index contention” indicates contention on an index. In Oracle 9i, both of these sessions would have been found waiting on the “enqueue” wait event with a lock type of “TX”, so Oracle 10g definitely helps isolate the specific issue.
In conclusion, Oracle 10g makes it much easier to track down the specific causes of Oracle locking problems now that the Oracle “enqueue” wait event from 9i and before has been broken up into over 200 distinct events in 10gR2.
Leave a Reply