oracle lock 06 - dml 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:

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 modeso 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

 

TimeSession 1Session 2Session 3Explanation

t0

SELECT employee_id, salary FROM employees WHERE  employee_idIN ( 100, 101 );EMPLOYEE_ID  SALARY-----------  ------100          512101          600

SELECT employee_id, salary FROM employees WHERE  employee_idIN ( 100, 101 );EMPLOYEE_ID  SALARY-----------  ------100          512101          600

SELECT employee_id, salary FROM employees WHERE  employee_idIN ( 100, 101 );EMPLOYEE_ID  SALARY-----------  ------100          512101          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.employeesSET 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_idIN ( 100, 101 );EMPLOYEE_ID  SALARY-----------  ------100          612101          600

SELECT employee_id, salary FROM employees WHERE  employee_idIN ( 100, 101 );EMPLOYEE_ID  SALARY-----------  ------100          512101          600

SELECT employee_id, salary FROM employees WHERE  employee_idIN ( 100, 101 );EMPLOYEE_ID  SALARY-----------  ------100          512101          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_idIN ( 100, 101 );EMPLOYEE_ID  SALARY-----------  ------100          612101          600

SELECT employee_id, salaryFROM employees WHERE  employee_idIN ( 100, 101 );EMPLOYEE_ID  SALARY-----------  ------100          512101          700

SELECT employee_id, salary FROM employees WHERE  employee_idIN ( 100, 101 );EMPLOYEE_ID  SALARY-----------  ------100          512101          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 LOCKTABLE 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 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.

3. Share Table Lock (S)

A share table lock held by a transaction allows other transactions to query the table (without usingSELECT ... 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.