Oracle Locks之DML锁

Oracle通过锁来实现数据库的并发控制

Oracle Database automatically locks a resource on behalf of a transaction to prevent other transactions from doing something that requires exclusive access to the same resource.  The database automatically acquires different types of locks at different levels of restrictiveness depending on the resource and the operation being performed.

Note:

The database never locks rows when performing simple reads.

Oracle Database locks are divided into the following categories.

Oracle 数据库锁分为如下三种(DML,DDL,System Lock)：

Lock	Description
DML Locks	Protect data. For example, table locks lock entire tables, while row locks lock selected rows. See "DML Locks".
DDL Locks	Protect the structure of schema objects—for example, the dictionary definitions of tables and views. See "DDL Locks".
System Locks	Protect internal database structures such as data files. Latches, mutexes, and internal locks are entirely automatic. See "System Locks".

DML Locks

A DML lock, also called a data lock, guarantees the integrity of data accessed concurrently by multiple users. For example, a DML lock prevents two 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 statements automatically acquire the following types of locks:

• Row Locks (TX)

• Table Locks (TM)

   

DML锁有：行级（事务级）锁和表级锁

In the following sections, 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). Enterprise Manager might display TM for any table lock, rather than indicate the mode of table lock (such as RS or SRX).

If a transaction obtains a lock for a row, then the transaction also acquires a lock for the table containing the row. The table lock prevents conflicting DDL operations that would override data changes in a current transaction. Figure 9-2 illustrates an update of the third row in a table. Oracle Database automatically places an exclusive lock on the updated row and a subexclusive lock on the table.

如果一个事务获得了一个行级锁，那么它会同时获得包含该行的表级锁，避免DDL操作导致当前事务中的数据修改而引起冲突。

下图是表级锁TM和行级锁TX的示意图

 

Storage of Row Locks

Unlike some databases, which use a lock manager to maintain a list of locks in memory, Oracle Database stores lock information in the data block that contains the locked row.

一些数据库管理系统，在内存中通过锁管理来维护一系列的锁，而Oracle数据库通过包含了锁住行的数据块来存储锁的相关信息。

The database uses a queuing mechanism for acquisition of row locks. If a transaction requires a lock for an unlocked row, then the transaction places a lock in the data block. Each row modified by this transaction points to a copy of the transaction ID stored in the block header (see "Overview of Data Blocks").

使用队列的机制来获取行级锁。

When a transaction ends, the transaction ID remains in the block header. If a different 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 asks to be notified when the lock is released. Otherwise, the transaction acquires the lock.

See Also:

Oracle Database Reference to learn about  V$TRANSACTION

Table Locks (TM)

A table lock, also called a TM lock, is acquired by a transaction when a table is modified by an INSERT, UPDATE, DELETE, MERGE, SELECT with the FORUPDATE clause, or LOCK TABLE statement. 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.

A table lock can be held in any of the following modes:

表级锁包含了以下几种模式：

• Row Share (RS)

  This lock, 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. A row share lock is the least restrictive mode of table lock, offering the highest degree of concurrency for a table.

  行共享：表级锁中最低限度的锁表模式，能够提供最高的并发度。此时不希望其他用户或绘画对改行进行更新操作。

• Row Exclusive Table Lock (RX)

  This lock, also called a subexclusive table lock (SX), generally indicates that the transaction holding the lock has updated table rows or issuedSELECT ... 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 subshare table locks for the same table.

  行级排他锁，表示获得该锁的事务将对表进行DML操作，SX锁允许其他事务对相同的表进行查询、插入、更新、修改或者锁其他行。因此SX锁允许多个事务获得SX锁和S锁，但是不能获得X锁

• Share Table Lock (S)

  A share table lock held by a transaction allows other transactions to query the table (without using SELECT ... FOR UPDATE), but updates are allowed only if a single transaction holds the share table lock. Because multiple transactions may hold a share table lock concurrently, holding this lock is not sufficient to ensure that a transaction can modify the table.

  共享锁，不允许任何用户更新表，但是允许其他用户发出select ... for update添加RS锁

• Share Row Exclusive Table Lock (SRX)

  This lock, 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.

  同时只有一个事务可以获得该共享行级排他锁，此时只允许其他事务查询表，但不能进行DML操作，也不能使用select ... for update。

• Exclusive Table Lock (X)

  This lock is the most restrictive, prohibiting other transactions from performing any type of DML statement or placing any type of lock on the table.

  排他锁，其他事务不能对该表进行任何DML和DDL操作，也不能添加任何类型的锁。

  各个模式的锁之间的兼容性如下：

  

See Also:

• Oracle Database SQL Language Reference

• Oracle Database Advanced Application Developer's Guide to learn more about table locks