Oracle lock 1: DML lock
DML lock, also known as data lock, is used to ensure data integrity when multiple users operate on data. DML lock prevents conflicting DML and DDL operations at the same time.
DML Locks include Row Locks (TX) and Table Locks (TM). Different DML operations will automatically request the corresponding Locks.
Row Locks (TX)
A row lock is also called a TX lock. It is used to lock a row of data in a table. When a transaction performs INSERT, UPDATE, DELETE, MERGE, or SELECT... during the for update operation, the row lock is added to the data until the transaction executes the commit or roll back operation.
The row lock prevents two transactions from modifying the same row of data. When a transaction modifies a row of data, the database always adds an exclusive lock to the modified row so that other transactions cannot modify the row, the Database releases the corresponding lock only after the transaction executes the commit or roll back operation. Row locks are small-granularity locks that provide applications with the maximum ability to modify data in parallel.
When a transaction acquires a row lock, the transaction also needs to obtain the table lock of the table where the row of data is located. The table lock prevents conflicting DDL operations, that is, the database automatically adds an exclusive lock to the updated row and adds a sub-exclusive lock to the table where the row is located.
Row locks and concurrency
The following uses an example to understand the relationship between row locks and concurrency.
First, create the following table and initialize the data:
create table employees(employee_id number(10),salary number(10));insert into employees(employee_id,salary) values(100,512);insert into employees(employee_id,salary) values(101,600);......
Step 1: the three sessions query the employees with IDs 100 and 101 at the same time. The query results are consistent.
Session 1：SELECT employee_id, salary FROM employees WHERE employee_id IN (100, 101);EMPLOYEE_IDSALARY-------------------------100512101600Session 2：SELECT employee_id, salary FROM employees WHERE employee_id IN (100, 101);EMPLOYEE_IDSALARY-------------------------100512101600Session 3：SELECT employee_id, salary FROM employees WHERE employee_id IN (100, 101);EMPLOYEE_IDSALARY-------------------------100512101600
Step 2: Session 1 update an employee whose id is 100. In this update, the writer requests a row lock to prevent other writers from updating this row of data, if other writers update this row of data, it will be blocked until Session 1 submits or rolls back the data.
Session 1：update employees set salary = 612 where employee_id = 100
Step 3: Perform Step 1 again
Session 1：SELECT employee_id, salary FROM employees WHERE employee_id IN (100, 101);EMPLOYEE_IDSALARY-------------------------100612101600Session 2：SELECT employee_id, salary FROM employees WHERE employee_id IN (100, 101);EMPLOYEE_IDSALARY-------------------------100512101600Session 3：SELECT employee_id, salary FROM employees WHERE employee_id IN (100, 101);EMPLOYEE_IDSALARY-------------------------100512101600
The result of Session 1 is the updated data, while the other two sessions are still old data.
Step 4: Session 2 updates the salary of 101 employees without submitting data, so Session 2 obtains the row lock of 101 employees.
UPDATE hr.employees SET salary = salary + 100 WHERE employee_id = 101;
Step 5: Execute the query in step 1 again
Session 1：SELECT employee_id, salary FROM employees WHERE employee_id IN (100, 101);EMPLOYEE_IDSALARY-------------------------100612101600Session 2：SELECT employee_id, salary FROM employees WHERE employee_id IN (100, 101);EMPLOYEE_IDSALARY-------------------------100512101700Session 3：SELECT employee_id, salary FROM employees WHERE employee_id IN (100, 101);EMPLOYEE_IDSALARY-------------------------100512101600
Row lock Storage
Oracle stores the lock information in the data block. The database uses a queue mechanism to process row lock requests. If a transaction requests an unlocked row, the transaction will put a lock into the data block, each row modified by the transaction points to a copy of the transaction ID stored in the block header.
When a transaction ends, the transaction ID is retained in the block header. If another transaction wants to modify a row of data, it uses the transaction ID to determine whether the lock is activated. If the lock is activated, the session of the transaction will be notified when the lock is released. Otherwise, the transaction will obtain the lock.
Table Locks (TM)
A table lock, also known as a tmlock, is requested when the following operations are performed on the TABLE: INSERT, UPDATE, DELETE, MERGE, SELECT... for update, and lock table. The DML operation that requests the table lock will prevent other conflicting DDL operations.
Table locks have the following modes:
Row Share (RS)
This lock is also called subshare table lock (SS). It indicates that the transaction holds the rows in the locked table and intends to update it. The Row share lock is a table lock with the minimum limit. It supports High-concurrency modification of table Row data.
Row Exclusive Table Lock (RX)
This is also called subexclusive table lock (SX). It usually indicates that the transaction holding the lock updates the table row or executes SELECT... for update. The SX lock allows other transactions to query, insert, update, delete, or lock Multiple rows of data on the same table. Therefore, the SX lock allows multiple transactions to obtain both the SX and RS locks on the same table.
Share Table Lock (S)
A transaction holds the S lock of the table, and any other transaction is allowed to query the table (except that the SELECT... for update), but only transactions that hold the S lock are allowed to UPDATE the table. Because multiple transactions can hold the S lock at the same time, acquiring the S lock does not guarantee that the transaction can modify the table.
Share Row Exclusive Table Lock (SRX)
The lock is also called share-subexclusive table lock (SSX), which is more restrictive than the S lock. At a time point on a table, only one transaction can obtain the SSX lock. The SSX lock allows other transactions to query tables (except with SELECT... for update), but cannot UPDATE Tables.
Exclusive Table Lock (X)
This lock has the strongest limit. It prohibits other transactions from performing any type of DML operations or preventing any lock on the table.
