Automatic and Manual Locking Mechanisms During SQL Operations

This appendix（附录） describes mechanisms（机制） that lock data either automatically or as specified by the user during SQL statements.
For a general discussion（一般性讨论） of locking mechanisms（锁机制） in the context of data concurrency and consistency（数据并发性和一致性）, see Oracle Database Concepts（Oracle 数据库概念）.
This appendix（附录） contains the following sections:
■ Automatic Locks in DML Operations
■ Automatic Locks in DDL Operations
■ Manual Data Locking

Automatic Locks in DML Operations
The purpose（目的） of a DML lock, also called a data lock, is to guarantee the integrity of data（保证数据完整性）being accessed concurrently by multiple users（多用户并发访问）.
For example, a DML lock can prevent（防止） multiple customers from buying the last copy of a book available from an online bookseller.
DML locks prevent destructive interference of simultaneous conflicting DML or DDL operations（DML锁可以防止同时冲突的DML或DDL操作的破坏性干扰。）.
DML statements automatically acquire（获得） locks at both the table level and the row level.
In the sections that follow, the acronym in parentheses（括号内首字母缩写） after each type of lock or lock mode is the abbreviation（缩写） used in the Locks Monitor（锁监控） of Oracle Enterprise Manager.
Enterprise Manager might display “TM” for any table lock, rather than indicate the mode of table lock (such as RS or SRX).

The types of row and table locks are summarized（概述） here.
For a more complete discussion of the types of row and table locks, see Oracle Database Concepts.

Row Locks (TX) A row lock, also called a TX lock, is a lock on a single row of a table.
A transaction acquires a row lock（事务获得行锁） for each row modified by one of the following statements: INSERT, UPDATE, DELETE, MERGE, and SELECT … FOR UPDATE.
The row lock exists until the transaction commits or rolls back.

When a transaction obtains a row lock for a row, the transaction also acquires a table lock for the table in which the row resides（存在）. The table lock prevents conflicting（不一致的） DDL operations that would override data changes in a current transaction.（覆盖数据在当前事务中的变化）

Table Locks (TM) A transaction automatically acquires a table lock (TM lock) when a table is modified with the following statements: INSERT, UPDATE, DELETE, MERGE, and
SELECT … FOR UPDATE.
These DML operations require table locks to reserve（保留） DML access to the table on behalf（代表） of a transaction and to prevent DDL operations that would conflict with（与…冲突） the transaction.
You can explicitly（明确地） obtain a table lock using the LOCK TABLE statement, as described in “Manual Data Locking” on page B-5.

A table lock can be held in any of the following modes:
A row share lock (RS), also called a subshare table lock (SS), indicates that the transaction holding（持有） the lock on the table has locked rows in the table and intends to（打算） update them.
An SS lock is the least restrictive mode of table lock（表级锁限制最小的模式）, offering the highest degree of concurrency for a table（为表提供最高级的并发性）.

A row exclusive lock (RX)（行排他锁）, also called a subexclusive table lock (SX), indicates that the transaction holding the lock（持有该锁的事务） has updated table rows or issued（发出） SELECT … FOR UPDATE.
An SX lock allows other transactions to query, insert, update, delete, or lock rows concurrently（同时地） in the same table.
Therefore, SX locks allow multiple transactions to obtain simultaneous（同时获得） SX and SS locks for the same table.

A share table lock (S) held by one transaction allows other transactions to query the table (without using SELECT … FOR UPDATE) but allows updates only if a single transaction holds the share table lock.
Multiple transactions may hold a share table
lock concurrently, so holding this lock is not sufficient（足够的） to ensure that a transaction can modify the table.

A share row exclusive table lock (SRX), also called a share-subexclusive table lock (SSX), is more restrictive（限制） than a share table lock.
Only one transaction at a time can acquire（获得） an SSX lock on a given table.
An SSX lock held by a transaction allows other transactions to query the table (except for SELECT … FOR UPDATE) but not to update the table.

An exclusive table lock (X) is the most restrictive mode of table lock, allowing the transaction that holds the lock exclusive write access to the table.
Only one transaction can obtain an X lock for a table.

Locks in DML Operations
Oracle Database automatically obtains row-level and table-level locks on behalf of DML operations（代表DML操作的行级锁和表级锁）.
The type of operation determines the locking behavior（锁定行为）. Table B–1 summarizes the information in this section.

Note: The implicit SX locks shown for the DML statements in
Table B–1 can sometimes be exclusive (X) locks for a short time owing to side effects from constraints（由于限制的副作用）.

Locks When Rows Are Queried
A query can be explicit（显式的）, as in the SELECT statement, or implicit（隐式的）, as in most INSERT,
MERGE, UPDATE, and DELETE statements.
The only DML statement that does not necessarily include a query component（成分） is an INSERT statement with a VALUES clause.
Because queries only read data, they are the SQL statements least likely to interfere with（最不可能的干扰） other SQL statements.
The following characteristics（特性） apply to（适用于） a query without the FOR UPDATE clause:
■ The query acquires no data locks.
Therefore, other transactions can query and
update a table being queried, including the specific rows being queried.
Because queries without the FOR UPDATE clause do not acquire any data locks to block（阻碍） other operations, such queries are often referred to as（被称为） nonblocking queries.
■ The query does not have to wait for any data locks to be released（释放）.
Therefore, the query can always proceed（发生）.
An exception to this rule is that queries may have to
wait for data locks in some very specific cases（特殊情况） of pending distributed transactions（分布式事务）.

Locks When Rows Are Modified
Some databases use a lock manager（锁管理器） to maintain（维持） a list of locks in memory.
Oracle Database, in contrast（恰恰相反）, stores lock information in the data block that contains the locked row.
Each row lock affects only a single row.
Oracle Database uses a queuing mechanism（队列机制） for acquisition（获得） of row locks.
If a transaction requires a row lock, and if the row is not already locked, then the transaction acquires a lock in the row’s data block.
The transaction itself has an entry（条目） in the interested transaction list (ITL) section of the block header.
Each row modified by this transaction points to（指向） a copy of the transaction ID stored in the ITL.
Thus, 100 rows in the same block modified by a single transaction require 100 row locks, but all 100 rows reference a single transaction ID.
When a transaction ends, the transaction ID remains in the ITL section of the data block header.
If a new transaction wants to modify a row, then it uses the transaction ID to determine whether the lock is active.
If the lock is active, then the session of the new transaction asks to be notified when the lock is released; otherwise, the new transaction acquires the lock.

The characteristics of INSERT, UPDATE, DELETE, and SELECT … FOR UPDATE statements are as follows:
■ A transaction containing a DML statement acquires exclusive row locks on the rows modified by the statement.
Therefore, other transactions cannot update or delete the locked rows until the locking transaction either commits or rolls back.
■ In addition to these row locks, a transaction containing a DML statement that modifies data also requires at least a subexclusive table lock (SX) on the table that contains the affected rows.
If the transaction already holds an S, SRX, or X table lock for the table, which are more restrictive than an SX lock, then the SX lock is not needed and is not acquired.
If the containing transaction already holds only an
SS lock, however, then Oracle Database automatically converts the SS lock to an SX lock.
■ A transaction that contains a DML statement does not require row locks on any rows selected by a subquery or an implicit query.
In the following sample UPDATE statement, the SELECT statement in parentheses（括号） is a subquery, whereas the WHERE a > 5 clause is an implicit query:
UPDATE t SET x = ( SELECT y FROM t2 WHERE t2.z = t.z ) WHERE a > 5;
A subquery or implicit query inside a DML statement is guaranteed to be consistent as（保证一致性） of the start of the query and does not see the effects（不受影响） of the DML statement of which it forms a part.
■ A query in a transaction can see the changes made by previous（之前的） DML statements in the same transaction, but not the uncommitted changes of other transactions.

Automatic Locks in DDL Operations
A data dictionary (DDL) lock protects the definition of a schema object while it is acted upon or referred to（执行或被引用） by an ongoing（正在进行的） DDL operation.
For example, when a user creates a procedure, Oracle Database automatically acquires DDL locks for all schema
objects referenced in the procedure definition.
The DDL locks prevent these objects from being altered or dropped before procedure compilation（编译） is complete.
Oracle Database acquires a DDL lock automatically on behalf of any DDL transaction requiring it.
Users cannot explicitly（显式的） request DDL locks.
Only individual schema objects（个人模式对象） that are modified or referenced are locked during DDL operations.
The whole data dictionary is never locked.
DDL operations also acquire DML locks on the schema object to be modified.

Exclusive DDL Locks
An exclusive DDL lock prevents other session from obtaining a DDL or DML lock.
Most DDL operations require exclusive DDL locks to prevent destructive interference（破坏性干扰） with other DDL operations that might modify or reference the same schema object.
For example, a DROP TABLE operation is not allowed to drop a table while an ALTER TABLE operation is adding a column to it, and vice versa（反之亦然）.
However, a query against（违反） the table is not blocked.
Exclusive DDL locks last for the duration（持续期间） of DDL statement execution and automatic commit.
During the acquisition（获得） of an exclusive DDL lock, if another DDL lock is already held on the schema object by another operation, then the acquisition waits until the older DDL lock is released and then proceeds.

Share DDL Locks
A share DDL lock for a resource prevents destructive interference with conflicting（相互冲突的破坏性干扰） DDL operations, but allows data concurrency（并发） for similar DDL operations.
For example, when a CREATE PROCEDURE statement is run, the containing transaction acquires share DDL locks for all referenced tables.
Other transactions can concurrently create procedures that reference the same tables and acquire concurrent share DDL locks on the same tables, but no transaction can acquire an exclusive DDL lock on any referenced table.
A share DDL lock lasts for the duration of DDL statement execution and automatic commit.
Thus, a transaction holding a share DDL lock is guaranteed that the definition of the referenced schema object is constant（不变的） for the duration of the transaction.

Breakable Parse Locks
A parse lock is held by a SQL statement or PL/SQL program unit for each schema object that it references.
Parse locks are acquired so that the associated（联系） shared SQL area can be invalidated（使无效） if a referenced object is altered or dropped.
A parse lock is called a breakable parse lock because it does not disallow any DDL operation and can be broken to allow conflicting DDL operations.
A parse lock is acquired in the shared pool during the parse phase（阶段） of SQL statement execution.
The lock is held as long as the shared SQL area for that statement remains in the shared pool.

Manual Data Locking
Oracle Database always performs locking automatically to ensure data concurrency（数据并发性）, data integrity（数据完整性）, and statement-level read consistency（语句级读一致性）.
However, you can override the Oracle default locking mechanisms.
This can be useful in situations（情况） such as the following:
■ When your application requires consistent data for the duration of the transaction, not reflecting（表明） changes by other transactions, you can achieve（获得） transaction-level read consistency（事务级读一致性） by using explicit locking（显式锁）, read-only transactions（只读事务）, serializable transactions（可串行化事务）, or by overriding（覆盖） default locking.
■ When your application requires that a transaction have exclusive access to a resource so that the transaction does not have to wait for other transactions to complete, you can explicitly lock（显式地锁定） the data for the duration of the transaction.
You can override automatic locking at two levels:
■ Transaction. You can override transaction-level locking with the following SQL
statements:
– SET TRANSACTION ISOLATION（隔离） LEVEL
– LOCK TABLE
– SELECT … FOR UPDATE
Locks acquired by these statements are released after the transaction commits or rolls back.
■ Session. A session can set the required transaction isolate level（事务隔离级别） with an ALTER SESSION SET ISOLATION LEVEL statement.

Note: When overriding Oracle default locking, the database administrator or application developer should ensure that data integrity is guaranteed, data concurrency is acceptable, and deadlocks are not possible or, if possible, are appropriately handled.
For more information on these criteria, see Oracle Database Concepts.