Oracle automatically acquires row and table locks for DML operations, and the type of operation determines the behavior of the lock, which summarizes the situation of the DML operation Lock:
| SQL Statement |
Row Locks |
Table Lock Mode |
RS |
RX |
S |
SRX |
X |
SELECT... FROMtable... |
—— |
None |
Y |
Y |
Y |
Y |
Y |
INSERTINTO table ... |
Yes |
Sx |
Y |
Y |
N |
N |
N |
UPDATEtable ... |
Yes |
Sx |
Y (note) |
Y (note) |
N |
N |
N |
MERGEINTO table ... |
Yes |
Sx |
Y |
Y |
N |
N |
N |
DELETEFROM table ... |
Yes |
Sx |
Y (note) |
Y (note) |
N |
N |
N |
SELECT... FROM tableFORUPDATEOF ... |
Yes |
Sx |
Y (note) |
Y (note) |
N |
N |
N |
LOCKTABLE tableIN ... |
—— |
|
|
|
|
|
|
ROWSHAREMODE |
|
Ss |
Y |
Y |
Y |
Y |
N |
ROWEXCLUSIVEMODE |
|
Sx |
Y |
Y |
N |
N |
N |
SHAREMODE |
|
S |
Y |
N |
Y |
N |
N |
SHAREROW EXCLUSIVEMODE |
|
SSX |
Y |
N |
N |
N |
N |
EXCLUSIVEMODE |
|
X |
N |
N |
N |
N |
N |
Note: If there is a row conflict between another transaction and the current transaction, you will need to wait for
The following describes the locks that are involved when rows are queried and modified.
Locks when rows are queried
A query can query data directly through SELECT, or other statements indirectly query data, such as INSERT, MERGE, update, and delete, where only the insert operation is not necessarily related to the query. Because the query is read-only, the likelihood of their being interfered by other DML statements is minimal.
If the query does not have a FOR UPDATE clause, the query is:
1) The query does not require a data lock, so other transactions can query and update the data being queried;
2) The query does not have to wait for any data locks to be released, so the query always executes. One exception is that the query must wait for some specific data locks on the distributed transaction.
Locks when rows are modified
Some databases use a list of in-memory to maintain locks, but the Oracle database stores the lock information in the data block, the information contains the locked rows, and each row lock affects only one row of data.
The Oracle database acquires a queue mechanism for row locks, and if a transaction requests a row lock and the row is not locked, then the transaction acquires a lock on the data block of the row, and the transaction itself places an entry in the interested Transaction list (ITL) area of the data header. Each row modified by the transaction points to a copy of the transaction ID stored in ITL, so 100 rows of data modified by a single transaction in the same block require 100 row locks, but all 100 rows refer to the same transaction ID.
When the transaction ends, the transaction ID remains in the ITL area of the data block. If a new object wants to modify a row, it uses the transaction ID to determine if the lock is active, and if the lock is active, then the session of the new transaction is requested to be notified when the lock is released, otherwise the new transaction acquires the lock.
INSERT, UPDATE, delete, and select ... For update will satisfy:
1) Transactions that use these DML operations will request row locks on the modified line, so that other transactions cannot update or delete the locked rows until the transaction commits or roll back;
2) In addition to row locks, transactions that use these DML operations need to request at least one child row table lock (subexclusive table LOCK,SX). If the transaction already has an S, SRX, or X table lock (more restrictive than the SX lock), then the SX lock is not required; If the transaction already has a SS lock, then the Oracle database automatically converts the SS lock to the SX lock;
3) Unless the row involved is modified, the transaction does not add a row lock to any subquery or to the rows involved in the implied subquery, such as the following update operation, using that one subquery (part of the parentheses) and the implicit subquery (WHERE a > 5):
UPDATE t SET x = (SELECT y from t2 where t2.z = t.z) where a > 5;
The transaction will not lock the rows involved in the subquery (SELECT y from t2 WHERE t2.z = t.z).
4) in the same transaction, one query can see the rows modified by the previous DML statement, but cannot see any changes that have not been committed by other transactions.
Oracle Lock 2:DML Operations and locks
