Many of Oracle’s locks are called enqueue locks. To enqueue a lock request is to place that request on the queue for its resource. So although the word “enqueue” is strictly speaking a verb, it is used adjectivally in the term enqueue lock. It is also used as a noun when referring to a particular enqueue resource, such as the CF (control file) enqueue.
Oracle uses two classes of local locks—those for which the lock and resource data structures are dynamically allocated in the shared pool, and those that use fixed arrays for the lock and resource data structures. Although almost all types of lock requests may be enqueued, the term enqueue should be taken to refer exclusively to those locks that use the fixed arrays for the lock and resource data structures, unless otherwise qualified.
The fixed array for enqueue resources is sized by the ENQUEUE_RESOURCES parameter. The number of slots in this array that are in use varies from time to time, and these can be seen in V$RESOURCE . Each row in V$RESOURCE represents a resource that is currently locked in any mode by one or more sessions. These resources are not persistent in that they are no longer defined once all locks on the resource have been released.
Rows in V$RESOURCE are identified by a two-character code representing the type of resource, and two numeric fields used to encode either the resource identity or the activities protected by locks on the resource, depending on the resource type. For example, resources of type TX represent entries in the transaction table of a rollback segment. The high-order two bytes of the first identifier contain the rollback segment number, and the low-order two bytes contain the transaction table slot number, while the second identifier contains the rollback segment wrap or sequence number.
All enqueue operations access the enqueue resource structure via a hash table. The hash value is based on the resource type and the numeric identifiers. The length of the enqueue hash table is set by the _ENQUEUE_HASH parameter. The default value of this parameter is derived directly from the PROCESSES parameter, as follows:
| 45 + 2 * ( PROCESSES + ⌊PROCESSES/10⌋ ) |
Because _ENQUEUE_HASH is derived directly from PROCESSES rather than from ENQUEUE_RESOURCES, it may be necessary to tune _ENQUEUE_HASH explicitly if ENQUEUE_RESOURCES has been raised significantly from its default value. Otherwise lengthy enqueue hash chains may develop. As with all hash tables, if you have cause to tune the number of buckets, you should make it a prime number (see Hash Tables and Prime Numbers).
The enqueue hash chains are accessed under the protection of the enqueue hash chains latches. The number of child enqueue hash chains latches is set by the _ENQUEUE_HASH_CHAIN_LATCHES parameter, which defaults to the CPU_COUNT. In a high concurrency environment, sleeps may be recorded against the enqueue hash chains latches if the hash chains are allowed to become unduly long. However, sleeps against these latches should normally be regarded as a secondary result of contention for a higher-level latch, rather than attributed to long hash chains.
In addition to the enqueue resources, a second fixed array is used for enqueue locking—namely, the enqueue locks themselves. The size of the enqueue locks fixed array is set by the _ENQUEUE_LOCKS parameter, and the active rows can be seen in V$ENQUEUE_LOCK .
An enqueue lock structure is used by each session waiting for or holding a lock on a resource. If one or more sessions are waiting for locks on a resource, then their enqueue lock structures are linked together into a two-way linked list, with the enqueue resource structure as the list header. This linked list is maintained and serviced in the order in which the locks were requested. For example, if a lock is held in shared mode, and the first waiter requires access to the resource in exclusive mode, then other sessions that require shared access must queue for the resource behind the first waiter, despite the fact that their requests are compatible with the mode in which the resource is currently locked.
Similar two-way linked lists are used to link together the enqueue lock structures for sessions holding a lock on the resource, and for sessions waiting to change the mode of the lock that they are holding.
The operation of changing the mode of a lock is called an enqueue conversion. For example, if a transaction holds a lock on a particular table in sub-share mode, and needs to update a row of that table, then the enqueue lock must be converted to sub-exclusive mode. However, if the resource is currently locked in an incompatible mode by another session, then the conversion cannot proceed immediately and the enqueue lock structure is placed in the conversion queue. Enqueue conversions are serviced in order before new enqueue requests.
During enqueue operations, modifications to the enqueue resources and enqueue locks fixed array free lists (see the sidebar, “Fixed Array Free Lists”) are made under the protection of the enqueues latch. There is only one enqueues latch, and it is often taken and released twice during the course of a single enqueue operation. However, the relevant enqueue hash chains latch is held for the duration of the operation.
An enqueue wait occurs whenever an enqueue request or enqueue conversion cannot be granted immediately because another session is holding a lock on the resource in an incompatible mode. The blocked process records an enqueue wait. The wait parameters are shown in Table 4.3.
Table 4-3. Wait Parameters (enqueue waits)
|
Parameter |
Description |
|---|---|
|
p1 |
The high-order 2 bytes contain the ASCII codes for the resource type. The low-order 2 bytes contain the mode in which a lock is required. |
|
p2 |
The id1 identifier for the resource. |
|
p3 |
The id2 identifier for the resource. |
Whenever a session releases an enqueue lock, it examines the lock request and conversion queues for the resource and, if appropriate, posts the next process that will be able to acquire a lock on the resource.
Processes waiting in an enqueue wait also set an alarm before they begin to wait. The timeout duration is dependent on the type of resource. For most enqueues, the enqueue wait timeout is 3 seconds.
Consecutive waits during a single attempt to acquire an enqueue lock are recorded as separate waits in the session and system wait statistics. However, the enqueue waits statistic in V$SYSSTAT is only incremented by one, after the lock has been acquired, as are the enqueue requests and enqueue conversions statistics. Note also that the enqueue timeouts statistic in V$SYSSTAT does not represent the number of enqueue wait timeouts. Rather, this statistic is incremented when an enqueue request or enqueue conversion is aborted entirely. This can be due to a distributed transaction timeout, but usually relates to locks requested in no-wait mode.
If V$SYSSTAT shows a significant number of enqueue waits, then a breakdown of the resource types for which these waits have been sustained can be obtained from X$KSQST , or from the APT script enqueue_stats.sql . Unfortunately, X$KSQST does not contains any indication of the duration of the waits, so care is needed when interpreting these figures.
It is sometimes suggested that ENQUEUE_RESOURCES should be increased to combat enqueue waits. But please note that there is absolutely no substance to this suggestion. Oracle will return an ORA-52 or ORA-53 error if it fails to find a free slot in the enqueue resources or enqueue locks fixed arrays respectively. Beyond that, the setting of the ENQUEUE_RESOURCES and _ENQUEUE_LOCKS parameters is unimportant.
The V$RESOURCE_LIMIT view should be used to adjust your settings for the ENQUEUE_RESOURCES and _ENQUEUE_LOCKS parameters to ensure that you will not run out of slots in these arrays. You can afford to be generous, because slots in these arrays only take on the order of 72 bytes and 60 bytes respectively. I like to maintain headroom of at least 20% above the maximum utilization ever recorded.
Oracle performs automatic deadlock detection for enqueue locking deadlocks. Deadlock detection is initiated whenever an enqueue wait times out, if the resource type required is regarded as deadlock sensitive, and if the lock state for the resource has not changed. If any session that is holding a lock on the required resource in an incompatible mode is waiting directly or indirectly for a resource that is held by the current session in an incompatible mode, then a deadlock exists.
If a deadlock is detected, the session that was unlucky enough to find it aborts its lock request and rolls back its current statement in order to break the deadlock. Note that this is a rollback of the current statement only, not necessarily the entire transaction. Oracle places an implicit savepoint at the beginning of each statement, called the default savepoint, and it is to this savepoint that the transaction is rolled back in the first case. This is enough to resolve the technical deadlock. However, the interacting sessions may well remain blocked.
An ORA-60 error is returned to the session that found the deadlock, and if this exception is not handled, then depending on the rules of the application development tool, the entire transaction is normally rolled back, and a deadlock state dump written to the user dump destination directory. This, of course, resolves the deadlock entirely. The enqueue deadlocks statistic in V$SYSSTAT records the number of times that an enqueue deadlock has been detected.
Application developers can eliminate all risk of enqueue deadlocks by ensuring that transactions requiring multiple resources always lock them in the same order. However, in complex applications, this is easier said than done, particularly if an ad hoc query tool is used. To be safe, you should adopt a strict locking order, but you must also handle the ORA-60 exception appropriately. In some cases it may be sufficient to pause for three seconds, and then retry the statement. However, in general, it is safest to roll back the transaction entirely, before pausing and retrying.
Oracle resolves true enqueue deadlocks so quickly that overall system activity is scarcely affected. However, blocking locks can bring application processing to a standstill. For example, if a long-running transaction takes a shared mode lock on a key application table, then all updates to that table must wait.
There are numerous ways of attempting to diagnose blocking lock situations, normally with the intention of killing the offending session. I will mention just a few.
Blocking locks are almost always TX (transaction) locks or TM (table) locks . When a session waits on a TX lock, it is waiting for that transaction to either commit or roll back. The reason for waiting is that the transaction has modified a data block, and the waiting session needs to modify the same part of that block. In such cases, the row wait columns of V$SESSION can be useful in identifying the database object, file, and block numbers concerned, and even the row number in the case of row locks. V$LOCKED_OBJECT can then be used to obtain session information for the sessions holding DML locks on the crucial database object. This is based on the fact that sessions with blocking TX enqueue locks always hold a DML lock as well, unless DML locks have been disabled.
It may not be adequate, however, to identify a single blocking session, because it may, in turn, be blocked by another session. To address this requirement, Oracle’s utllockt.sql script gives a tree-structured report showing the relationship between blocking and waiting sessions. Some DBAs are loath to use this script because it creates a temporary table, which will block if another space management transaction is caught behind the blocking lock. Although this is extremely unlikely, the same information can be obtained from the DBA_WAITERS view if necessary. The DBA_WAITERS view is created by Oracle’s catblock.sql script.
Some application developers attempt to evade blocking locks by preceding all updates with a SELECT FOR UPDATE NOWAIT statement. However, if they allow user interaction between taking a sub-exclusive lock in this way and releasing it, then a more subtle blocking lock situation can still occur. If a user goes out to lunch while holding a sub-exclusive lock on a table, then any shared lock request on the whole table will block at the head of the request queue, and all other lock requests will queue behind it.
Diagnosing such situations and working out which session to kill is not easy, because the diagnosis depends on the order of the waiters. Most blocking lock detection utilities do not show the request order, and do not consider that a waiter can block other sessions even when it is not actually holding any locks. The APT script enqueue_locks.sql shows the locks held and wanted for each resource in order, together with the number of seconds that the lock has been held or wanted. This is intended to supplement other blocking lock detection utilities, such as Oracle’s utllockt.sql.
Application developers can greatly reduce the risk of blocking lock problems by adopting an optimistic locking strategy (see the sidebar, “Optimistic Locking”), and by cultivating an aversion to coarse granularity locking and so designing their applications to run without DML locks.
For distributed transactions, Oracle is unable to distinguish blocking locks and deadlocks, because not all of the lock information is available locally. To prevent distributed transaction deadlocks, Oracle times out any call in a distributed transaction if it has not received any response within the number of seconds specified by the _DISTRIBUTED_LOCK_TIMEOUT parameter. This timeout defaults to 60 seconds. If a distributed transaction times out, an ORA-2049 error is returned to the controlling session. Robust applications should handle this exception in the same way as local enqueue deadlocks.
Similarly, under release 8.0, parallel transactions, which consist of multiple sibling transaction branches, could deadlock undetectably with other simple transactions. If a simple transaction was blocked by one branch of a global transaction, and was blocking another, then Oracle’s normal deadlock detection mechanism in release 8.0 would fail to detect the deadlock. To prevent this, Oracle timed out any enqueue lock acquisition or conversion request in a branch of a parallel transaction as though it were a distributed transaction, and an ORA-99 error was returned. The PARALLEL_TRANSACTION_RESOURCE_TIMEOUT parameter, which defaulted to 300 seconds, was used to control this timeout. In release 8.1, the deadlock detection algorithm has been improved to detect these deadlocks, and so this timeout is no longer required.
There is an interested transaction list (ITL) in the variable header of each Oracle data block. When a new block is formatted for a segment, the initial number of entries in the ITL is set by the INITRANS parameter for the segment. Free space permitting, the ITL can grow dynamically if required, up to the limit imposed by the database block size, or the MAXTRANS parameter for the segment, whichever is less.
Every transaction that modifies a data block must record its transaction identifier and the rollback segment address for its changes to that block in an ITL entry. (However, for discrete transactions, there is no rollback segment address for the changes.) Oracle searches the ITL for a reusable or free entry. If all the entries in the ITL are occupied by uncommitted transactions, then a new entry will be dynamically created, if possible.
If the block does not have enough internal free space (24 bytes) to dynamically create an additional ITL entry, then the transaction must wait for a transaction using one of the existing ITL entries to either commit or roll back. The blocked transaction waits in shared mode on the TX enqueue for one of the existing transactions, chosen pseudo-randomly. The row wait columns in V$SESSION show the object, file, and block numbers of the target block. However, the ROW_WAIT_ROW# column remains unset, indicating that the transaction is not waiting on a row-level lock, but is probably waiting for a free ITL entry.
The most common cause of ITL entry shortages is a zero PCTFREE setting. Think twice before setting PCTFREE to zero on a segment that might be subject to multiple concurrent updates to a single block, even though those updates may not increase the total row length. The degree of concurrency that a block can support is dependent on the size of its ITL, and failing that, the amount of internal free space. Do not, however, let this warning scare you into using unnecessarily large INITRANS or PCTFREE settings. Large PCTFREE settings compromise data density and degrade table scan performance, and non-default INITRANS settings are seldom warranted.
One case in which a non-default INITRANS setting is warranted is for segments subject to parallel DML. If a child transaction of a PDML transaction encounters an ITL entry shortage, it will check whether the other ITL entries in the block are all occupied by its sibling transactions and, if so, the transaction will roll back with an ORA-12829 error, in order to avoid self-deadlock. The solution in this case is to be content with a lower degree of parallelism, or to rebuild the segment with a higher INITRANS setting. A higher INITRANS value is also needed if multiple serializable transactions may have concurrent interest in any one block.
Get Oracle Internals: An Introduction now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
