Introduction to Oracle Locking and Enqueue

from:http://www.confio.com/English/Tips/Locking_and_Enqueue.php

 

 

Introduction to Oracle Locking and Enqueue

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 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.

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 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 enqueue wait event is the best indication of locking in Oracle databases.

Enqueue Wait Event in Oracle 9i

In Oracle 9i, when a session is waiting on the “enqueue” wait event, this indicates a wait for a 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.

Enqueue Wait Events in Oracle 10g

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 locking problems now that the "enqueue" wait event from 9i and before has been broken up into over 200 distinct events in 10gR2.