A DML lock, also called a data lock, is used to ensure that data is complete when multiuser data is manipulated. DML locks prevent conflicting DML and DDL operations from occurring at the same time.
DML locks have row locks,tx and table locks (table Locks,tm), and the corresponding locks are automatically requested by different DML operations.
Row lock (Row locks,tx)
A row lock is also called a TX lock, which is used to lock a row of data in a table. When a transaction makes an INSERT, UPDATE, DELETE, Merge, or select on a row of data ... For update operation, the data will add a row lock for the row, and the row lock is not released until the transaction executes a commit or roll-back operation.
Row locks prevent two transactions from modifying the same row of data, and 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, and the database releases the corresponding lock only after the transaction has performed a commit or roll-back operation. Row locks are small-grained locks that provide the application with the maximum ability to modify data in parallel.
When a transaction acquires a row lock, the transaction also needs to obtain a table lock on the table in which the row data resides, and the table lock blocks conflicting DDL operations, that is, the database automatically adds an exclusive lock for the updated row, and adds a child row lock to the table where the row resides.
Row Locks and concurrency
Here's 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, salary number), insert into employees (Employee_id,salary) VALUES ( 100,512); INSERT into employees (Employee_id,salary) values (101,600);
Step one: Three sessions query for employees with IDs 100 and 101, with consistent query results
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 Two: Session 1 to perform the update operation, update the employee ID 100, in this update, the writer will request a row lock, prevent other authors to update the row of data, if the other writer update the row data will be blocked until Session 1 commit or rollback data
Session 1:update Employees Set salary = 612 where employee_id = 100
Step three: Perform step one 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 its updated data, while the other two sessions are still old data.
Step four: Session 2 updates the salary of 101 employees and does not submit data so that session 2 acquires a row lock on employee 101
UPDATE hr.employees SET Salary = salary + WHERE employee_id = 101;
Step five: Perform the query for 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
Storage of row Locks
Oracle stores the lock information in the data block. The database processes a row lock request with a queue mechanism, and if a transaction requests an unlocked row, the transaction places a lock on the data block, and 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 remains in the block header, and if another transaction wants to modify a row of data, it uses the transaction ID to determine whether the lock is active. If the lock is active, then the session of the transaction is notified when the lock is released, otherwise the transaction acquires the lock.
Table lock (Tables Locks,tm)
Table locks, also called TM Locks, are requested when the following actions are performed on the table: INSERT, UPDATE, DELETE, MERGE, SELECT ... For update and lock TABLE. A DML operation that requests a table lock will block other conflicting DDL operations.
Table locks have the following modes:
Row Share (RS)
The lock is also called Subshare table lock (SS), which indicates that the transaction holds a lock on the table row in the locked table and intends to update it. The row share lock is the least restrictive table lock that provides support for high concurrency modifications to the table's row data.
Row Exclusive Table Lock (RX)
The institute is also called Subexclusive table lock (SX), which usually means that the transaction holding the lock updates the row of the table or executes a SELECT ... For UPDATE. SX locks allow other transactions to query, insert, UPDATE, delete, or lock multiple rows of data on the same table, so SX locks allow multiple transactions to acquire both SX and RS locks on the same table.
Share Table Lock (S)
A transaction holds the S lock on the table, allowing other transactions to query the table (except with the Select ... For update), but only transactions that hold the S lock are allowed to update the table. Because multiple transactions can hold s locks at the same time, acquiring S locks 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 stronger than the S lock limit. Only one transaction can acquire a SSX lock on a table at a point in time. SSX Lock allows other transaction query tables (except with select ... For update), but the table cannot be updated.
Exclusive Table Lock (X)
This lock is strongest and prohibits other transactions from performing any type of DML operation or preventing any locks from being placed on the table.
Oracle Lock 1:DML Lock
