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:
1. Row Locks (TX)
2. Table Locks (TM)
Row Locks (TX)
A row lock, also called a TX lock, is a lock on a single row of table. A transaction acquires a row lock for each row modified by an INSERT, UPDATE, DELETE, MERGE, or SELECT ... FOR UPDATE statement. The row lock exists until the transaction commits or rollsback.
Row locks primarily serve as a queuing mechanism to prevent two transactions from modifying the same row. The database always locks a modified row in exclusive mode so that other transactions cannot modify the row until the transaction holding the lock commits or rolls back. Row locking provides the finest grain locking possible and so provides the best possible concurrency and throughput.
注意:If a transaction terminates because of database instance failure, then block-level recovery makes a row available before the entire transaction is recovered.
If a transaction obtains alock 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.
Figure 9-2 Row and Table Locks
Row Locks and Concurrency
Table 9-6 illustrates how Oracle Database uses row locks for concurrency. Three sessions query the same rows simultaneously. Session 1 and 2 proceed to make uncommitted updates to different rows, while session 3 makes no updates. Each session sees its own uncommitted updates but not the uncommitted updates of any other session.
Table 9-6 Data Concurrency Example
Time | Session 1 | Session 2 | Session 3 | Explanation |
---|---|---|---|---|
t0 | SELECT employee_id, salary FROM employees WHERE employee_id IN ( 100, 101 ); EMPLOYEE_ID SALARY ----------- ------ 100 512 101 600 | SELECT employee_id, salary FROM employees WHERE employee_id IN ( 100, 101 ); EMPLOYEE_ID SALARY ----------- ------ 100 512 101 600 | SELECT employee_id, salary FROM employees WHERE employee_id IN ( 100, 101 ); EMPLOYEE_ID SALARY ----------- ------ 100 512 101 600 | Three different sessions simultaneously query the ID and salary of employees 100 and 101. The results returned by each query are identical. |
t1 | UPDATE hr.employees SET salary=salary+100 WHERE employee_id=100; | Session 1 updates the salary of employee 100, but does not commit. In the update, the writer acquires a row-level lock for the updated row only, thereby preventing other writers from modifying this row. | ||
t2 | SELECT employee_id,
salary FROM employees
WHERE employee_id
IN ( 100, 101 );
EMPLOYEE_ID SALARY
----------- ------
100 612
101 600
| SELECT employee_id, salary FROM employees WHERE employee_id IN ( 100, 101 ); EMPLOYEE_ID SALARY ----------- ------ 100 512 101 600 | SELECT employee_id, salary FROM employees WHERE employee_id IN ( 100, 101 ); EMPLOYEE_ID SALARY ----------- ------ 100 512 101 600 | Each session simultaneously issues the original query. Session 1 shows the salary of 612 resulting from the t1 update. The readers in session 2 and 3 return rows immediately and do not wait for session 1 to end its transaction. The database uses multiversion read consistency to show the salary as it existed before the update in session 1. |
t3 | UPDATE hr.employees SET salary=salary+100 WHERE employee_id=101; | Session 2 updates the salary of employee 101, but does not commit the transaction. In the update, the writer acquires a row-level lock for the updated row only, preventing other writers from modifying this row. | ||
t4 | SELECT employee_id,
salary FROM employees
WHERE employee_id
IN ( 100, 101 );
EMPLOYEE_ID SALARY
----------- ------
100 612
101 600
| SELECT employee_id,
salaryFROM employees
WHERE employee_id
IN ( 100, 101 );
EMPLOYEE_ID SALARY
----------- ------
100 512
101 700
| SELECT employee_id, salary FROM employees WHERE employee_id IN ( 100, 101 ); EMPLOYEE_ID SALARY ----------- ------ 100 512 101 600 | Each session simultaneously issues the original query. Session 1 shows the salary of 612 resulting from the t1 update, but not the salary update for employee 101 made in session 2. The reader in session 2 shows the salary update made in session 2, but not the salary update made in session 1. The reader in session 3 uses read consistency to show the salaries before modification by session 1 and 2. |
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.
The database uses a queuing mechanism for acquisition of row locks. If a transaction requires a lock for anunlocked 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.
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.
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 FOR
UPDATE
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:
1. Row Share Table Lock(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.
2. 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 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 subshare table locks for the same table.
3. 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.
4. 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.
5. 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.