enqueue lock定义，enqueue resource和enqueue lock分配、等待事件

一、 enqueue lock定义及分类
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.
Oracle uses locks for many different purposes. The following are the most
important ones to understand for performance tuning.
1、 Transaction Locks and Row-Level Locks
Oracle's much vaunted row-level locks are subtle. When a transaction modifies a row, its transaction identifier is recorded in an entry in the interested transaction list (ITL) in the header of the data block itself, and the row header is modified to point to that ITL entry. Once these changes have been made, no lock is retained.The ITL entry for the uncommitted transaction, together with the row header that references it, constitutes an implicit lock on the row.
When another transaction wants to modify the same row, and sees that an
uncommitted transaction has modified that row, that transaction waits, not on a row-level lock, but on the transaction lock for the blocking transaction.
When the blocking transaction commits or rolls back, its transaction lock will be released. Its implicit row-level locks are thereby released, and so the blocked transaction can then proceed. Note that rolling back to a savepoint does not free previously blocked transactions that were waiting for a row-level lock.
2、 Buffer Locks
Row-level locks protect data integrity at the lowest feasible level of granularity,and remain in force for the duration of a transaction. However, Oracle also needs short-term block-level locks to be in force while accessing or modifying blocks in its cache.
Buffer locks are used to provide simple read/write locking for blocks in the database buffer cache. Although they are often taken for granted and seldom mentioned, buffer locks are essential to data integrity, and can feature prominently in certain performance tuning scenarios.
3、 Data Dictionary Locks
The definitions of database objects in the data dictionary must be protected while they are being referenced. This is necessary to prevent those objects from being dropped, and to prevent their definitions from being changed, while they are being used. Dictionary locks must be held while dependent SQL statements are being parsed or executed, and must be retained for the duration of dependent transactions.
Several types of locks are used for dictionary locking. All of these are covered in some detail later in this chapter. The data dictionary rows themselves are locked with row cache enqueue locks. Dependent SQL statements are protected with library cache pins, and dependent transactions hold DML (Data Manipulation Language) locks. Logically, both DML locks and library cache pins are dependent on the corresponding row cache enqueue locks. However, this dependency is implicit in the code, rather than explicit in the structures.

二、 enqueue resource和enqueue lock的结构及各类enqueue情况
1、 Enqueue Resources概念
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.
2、 Enqueue Locking概念
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.
3、 各类enqueue情况（enqueue resource和enqueue lock分配、等待事件，以及各类enqueue详细资料）
1) Row Cache Enqueues(在Shared pool分配)
A cache of rows from the data dictionary is kept in the shared pool. This cache serves not only to reduce physical access to the data dictionary tables in the SYSTEM tablespace, but also enables fine-grained locking of individual data dictionary rows. The need for data dictionary locking was introduced at the start of this chapter (see Section 4.1.3).
The locks on the data dictionary rows themselves are called row cache enqueue locks. These locks are implemented in much the same way as general enqueue locks. The cached data dictionary row acts as the resource structure, and enqueue lock structures are dynamically allocated from the shared pool as required. Locks can be requested, converted, and released, and requests can wait and time out,just like the general enqueue locks. However, row cache enqueue locks are not included in V$LOCK. In fact, they are not visible anywhere except in system and process state dumps.
Depending on the operation, some row cache enqueue locks are requested in nowait mode and an ORA-54 error is returned if the lock is not immediately available. Otherwise, row cache lock requests are enqueued if necessary, and the process waits on a row cache lock wait. The parameters for this wait are shown in Table 4.4.
 
Table 4.4. Wait Parameters (row cache lock waits)
Parameter Description
p1 A number corresponding to the CACHE# column of V$ROWCACHE
representing the data dictionary table for which a row lock is needed
p2 The mode in which the lock is already held
p3 The mode in which the lock is needed
The numeric codes used for the lock modes in the parameters for this wait are those for instance locks, rather than local locks, even when running singleinstance Oracle. However, this wait is relatively rare in single-instance Oracle,resulting only from resource conflicts, whereas it is routine in parallel server because new lock requests must be socialized via the distributed lock manager.
Oracle does not expect row cache enqueue lock acquisitions and conversions to block for more than a few seconds. Therefore, row cache lock waits time out every 3 seconds, and if the lock has still not been acquired after 100 timeouts (5 minutes), an internal deadlock is assumed, and the operation is aborted. A message is written to the alert log saying that a process "WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK," and a process state dump is written to a trace file. Except for DDL against a long-running, in-use function, procedure, or package, this error should be treated as an Oracle bug and reported to Oracle Support.

2) Library Cache Locks and Pins(在Shared pool分配)
The library cache is not one cache, but many. It contains the pseudo code for PL/SQL program units. It contains parse trees and execution plans for shareable SQL statements. It also contains abstract representations in a form. called DIANA of the database objects referenced by the SQL statements. The information is needed in this form. for PL/SQL program unit compilation and SQL statement parsing and execution, despite the fact that the dictionary cache contains the same information in a different form. The library cache also contains control structures such as synonym translations, dependency tracking information, and library cache locks and pins.
Library cache locks are referred to as breakable parse locks in the Oracle documentation. They are applied to the library cache objects for SQL statements and PL/SQL program units, and recursively to the library cache objects for the database objects on which they depend. Library cache locks are held in shared mode during parse operations and are converted to null mode thereafter. If a DDL statement later modifies the definition of a database object, then the library cache information for that database object and all dependent library cache objects is invalidated by breaking the library cache locks.
Library cache locks can only be broken, however, when the library cache object is not also pinned. A pin is applied to the library cache object for a PL/SQL program unit or SQL statement while it is being compiled, parsed, or executed. Pins are normally held in shared mode, but are also held in exclusive mode while the library cache information for the object is being changed. The library cache objects for pipes and sequences are most subject to change. When a library cache object is pinned, pins are applied to all referenced objects in turn. When a pin is applied to the library cache object for a database object, then a corresponding row cache enqueue lock is acquired on the underlying data dictionary row,thereby preventing conflicting DDL.
Every object in the library cache has a handle that acts as the resource structure for library cache locks and pins. The handle, lock, and pin structures are all dynamically allocated within the shared pool. The handle implements two-way linked lists of locks held, locks waited for, pins held, and pins waited for. Sessions waiting for a lock or pin report a library cache lock or library cache pin wait respectively. The parameters for these waits are shown in Table 4.5.
 
Table 4.5. Wait Parameters (library cache lock and library cache pin waits)
Parameter Description
p1 The address in memory of the library cache handle.
p2 The memory address of the lock or pin structure.
p3 The mode of lock or pin required, and the namespace of the object,
encoded as 10 * mode + namespace. In this case, the modes are:
2 shared
3 exclusive
The namespaces are:
0 cursor
1 table, procedure, and others
2 package body
3 trigger
4 index
5 cluster
6 object
7 pipe

If there are multiple readers of a single pipe, then library cache pin waits on the library cache object for that pipe will be routine, but brief. Other than that,library cache waits are relatively rare, although much more likely to be prolonged.These waits time out after three seconds and, if they do time out, deadlock detection is performed. If a deadlock is found, the lock or pin request is aborted and an ORA-4020 error is returned. This error is normally caused by ad hoc DDL. It should not be necessary to code your applications to handle this error.

3) DML Locks
Library cache pins and the associated row cache enqueue locks protect object definitions for the duration of parse and execute calls. However, for transactions that consist of a series of statements, equivalent locks need to be held for the duration of the transaction.
More than that, the lock mode may need to be raised partway through the transaction. For example, a table may first be queried, and then updated. This, of course, is why lock conversions are necessary. If the existing lock were to be released, even momentarily, it would be possible for the referenced object to be dropped or changed, and the transaction would then be unable to either proceed or roll back.
The possibility of rollback, particularly rollback to a savepoint, adds another dimension of complexity to dictionary locking. Namely, if a transaction is rolled back beyond the point at which a lock was upgraded, then the lock must be downgraded correspondingly, as part of the rollback operation, in order to reduce the risk of artificial deadlocks.
The requirements of dictionary locking for transactions and, in particular, the maintenance of a history of lock conversions, is provided by DML locks in conjunction with TM enqueues. Every transaction holding a DML lock also holds a TM enqueue lock. The basic locking functionality is provided by the enqueue,and the DML lock adds the maintenance of the conversion history.
The fixed array of DML lock structures is sized by the DML_LOCKS parameter.Its free list is protected by the dml lock allocation latch , and the active slots are visible in V$LOCKED_OBJECT . As with enqueue resources and locks, the number of slots in the DML locks fixed array is unimportant to performance, as long as you don't run out of free slots and get an ORA-55 error. Once again,V$RESOURCE_LIMIT can be used to adjust your setting for DML_LOCKS to ensure that this does not happen. Each slot only takes on the order of 116 bytes,so having a generous number of slots is not a problem.
Disabling DML Locks
DML locks and the associated TM enqueue locks can be disabled, either entirely,or just for certain tables. To disable these locks entirely, the DML_LOCKS parameter must be set to zero. In a parallel server database, it must be set to zero in all instances. To disable such locks against a particular table, the DISABLE TABLE LOCKS clause of the ALTER TABLE statement must be used.
If locks are disabled for a table, then DML statements can still modify the table's blocks, and row-level locks are still held. However, the sub-shared mode table locks normally associated with queries, and the sub-exclusive mode table locks normally associated with DML, are not taken. Instead, transactions against the table are protected from conflicting DDL by simply prohibiting all attempts to take a lock on the entire table, and thus all DDL against the table.
There are two reasons for disabling DML locks and table locks. The first is to avoid the lock acquisition overhead. This is particularly important in parallelserver databases where the transactions are short. In such cases, it may take longer to acquire the TM instance lock than to complete the rest of the transaction.
In single-instance Oracle, the lock acquisition overhead is relatively trivial. However, the disabling of table locks should still be considered to efficiently prevent blocking lock problems. A large class of blocking lock problems is caused by attempts to lock an entire table, sometimes for ad hoc DDL such as creating an index, but often for ad hoc DML against a referenced table where the relationship is not supported by a foreign key index.
Foreign keys referring to tiny reference tables are often indexed to prevent such problems. However, the presence of such indexes adds a significant overhead to DML against the main table. It is better to do without these indexes, and prevent blocking locks by disabling table locks. Of course, table locks will need to be enabled temporarily for maintenance tasks such as updating the reference data or rebuilding indexes. However, that is no hardship, as such operations are normally performed during a special maintenance window.
Of course, it is preferable to disable table locks on each table individually, rather than to disable them entirely by setting the DML_LOCKS parameter to zero. If DML_LOCKS is zero, you can create temporary tables but never drop them, and you have to shut down and start up the system twice for maintenance operations such as rebuilding indexes.

4) Buffer Locks(在Shared pool分配)
A form. of enqueue locking is used to protect cached database blocks. For each buffer in the database buffer cache, there is a buffer header. The buffer headers constitute a fixed array in the permanent memory part of the shared pool. These buffer headers act as the resource structures for buffer locks. Sessions manipulate buffer headers, and thus buffers, via dynamically allocated structures known as buffer handles. The buffer handles act as the lock structures for buffer locks.
Buffer locks are taken only in shared and exclusive modes.[1] The buffer headers implement a two-way linked list of the buffer handles for sessions that are using the buffer, and another for the buffer handles of sessions waiting for the buffer.Sessions waiting for a buffer lock report either buffer busy waits, or buffer busy due to global cache waits, or write complete waits. The parameters for buffer busy waits are shown in Table 4.6.
[1] This is a simplification, but adequate for our purpose here.
 
Table 4.6. Wait Parameters (buffer busy waits)
Parameter Description
p1 The file number of the database block.
p2 The block number of the database block in its file.
p3 The reason for the wait. A or 1014 indicates that the buffer is locked
exclusively by a session that is busy reading a block from disk into the
buffer, and that the read has not yet completed. A reason of is used for
consistent gets, whereas 1014 is used for current mode block gets. Any
other number indicates that the buffer is locked exclusively for
modification by another session.

The timeout for buffer busy waits backs off from 1 to 3 seconds. If a buffer lock for a block that is in cache cannot be acquired within a certain number of timeouts, and if the session is holding buffer locks on one or more other buffers,then a buffer lock deadlock is assumed. The number of timeouts to wait before a buffer lock deadlock is assumed is dependent on the operation being attempted,and whether it is part of a discrete transaction. Because discrete transactions do not hold transaction locks, and thus row-level locks, they must acquire all the buffer locks they need before any modifications can be made, and hold them all until the transaction is ready to make its changes and commit. This means that discrete transactions hold more buffer locks than normal transactions, and hold them for much longer.
If a buffer lock deadlock is suspected, the session that timed out trying to acquire a buffer lock releases the buffer locks that it is holding on other buffers, and immediately enqueues them again, thereby falling to the end of the queue of waiting sessions. It also posts the first process that was waiting for a lock on each of the buffers concerned, and then yields the CPU. Although yielding the CPU does not really constitute a wait, a buffer deadlock wait is recorded and the exchange deadlocks statistic is incremented. Assumed buffer lock deadlocks signal event 370, which can be caught to investigate such problems.
In parallel server databases, buffers can be locked for global cache operations such as writes in response to ping requests, and consistent reads for direct memory transfers by the block server process. If a request for a buffer lock cannot proceed because the buffer is locked for a global cache operation, then a buffer busy due to global cache wait is recorded.
Similarly, when buffer lock requests cannot proceed because the buffers are locked by DBWn as part of a batch of blocks to be written, then write complete waits are recorded . The timeout for these waits is 1 second, and the parameters are as shown in Table 4.7.
 
Table 4.7. Wait Parameters (write complete waits)
Parameter Description
p1 The file number of the database block.
p2 The block number of the database block in its file.
p3 The reason for the wait. The normal reason code is 1029; however, other
values are seen at times.

5) Sort Locks(在SGA分配)
Sort locks apply to the disk space being used for disk sort operations. There are two types of sort locks: temporary table locks and sort segment locks. These correspond to temporary segments in PERMANENT tablespaces and TEMPORARY tablespaces respectively. There are fixed arrays in the SGA for each type of sort lock. Both arrays are sized by the SESSIONS parameter, which allows for the maximum possible usage of sort locks.
Sort locks are used merely to track disk sort space usage, and do not suffer from lock conflicts, waits, or deadlocks. However, you should not confuse sort locks with the ST (space transaction) enqueue , which is extremely prone to lock conflicts, waits, and even deadlocks. Contention for the ST enqueue is often associated with disk sorts, because it is needed for the creation, extension, and deallocation of temporary segments.

参考文档：

1、  OReilly.Oracle.8i Interal Service

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/95233/viewspace-628365/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/95233/viewspace-628365/