Oracle lock 2: DML operations and locks, oracle lock dml operations
Oracle automatically acquires row locks and table locks for DML operations. The operation type determines the lock action. The following section summarizes the DML operation locks:
| SQL Statement |
Row Locks |
Table Lock Mode |
RS |
RX |
S |
SRX |
X |
SELECT...FROM table... |
-- |
None |
Y |
Y |
Y |
Y |
Y |
INSERT INTO table... |
Yes |
SX |
Y |
Y |
N |
N |
N |
UPDATE table... |
Yes |
SX |
Y (note) |
Y (note) |
N |
N |
N |
MERGE INTO table... |
Yes |
SX |
Y |
Y |
N |
N |
N |
DELETE FROM table... |
Yes |
SX |
Y (note) |
Y (note) |
N |
N |
N |
SELECT...FROM tableFORUPDATEOF... |
Yes |
SX |
Y (note) |
Y (note) |
N |
N |
N |
LOCK TABLE tableIN... |
-- |
|
|
|
|
|
|
ROW SHARE MODE |
|
SS |
Y |
Y |
Y |
Y |
N |
ROW EXCLUSIVE MODE |
|
SX |
Y |
Y |
N |
N |
N |
SHARE MODE |
|
S |
Y |
N |
Y |
N |
N |
SHARE ROW EXCLUSIVE MODE |
|
SSX |
Y |
N |
N |
N |
N |
EXCLUSIVE MODE |
|
X |
N |
N |
N |
N |
N |
NOTE: If another transaction conflicts with the current transaction, you need to wait.
The following describes the locks involved in row query and modification.
Lock when a row is queried
A query can directly query data through SELECT, or indirectly query data using other statements, such as INSERT, MERGE, UPDATE, and DELETE. Only INSERT operations do not necessarily involve queries. Because queries only read data, they are least likely to be interfered by other DML statements.
If the query does not have a for update clause, the query is:
1) Data locks are not required for queries. Therefore, other transactions can query and update the data being queried;
2) the query does not have to wait for any data lock to be released. Therefore, the query can always be executed. One exception is that the query must wait for some specific data locks of distributed transactions.
Lock when the row is modified
Some databases use a list in the memory to maintain the lock. However, the Oracle database stores the lock information in the data block. The information contains the locked rows. Each row lock only affects one row of data.
The Oracle database uses a queue mechanism for row lock acquisition. If a transaction requests a row lock and the row is not locked, the transaction obtains a lock of the row's data block, the transaction itself will put an entry in the interested transaction list (ITL) area of the data block header, and each row modified by the transaction points to a copy of the transaction ID stored in the ITL. Therefore, 100 rows of data modified by a single transaction require 100 row locks, but all 100 rows reference the same transaction ID.
When the transaction ends, the transaction ID is retained in the ITL area of the data block header. If a new transaction wants to modify a row, it uses the transaction ID to determine whether the lock is activated. If the lock is activated, the session of the new transaction will be notified when the lock is released. Otherwise, the new transaction gets the lock.
INSERT, UPDATE, DELETE, and SELECT... for update will meet the following requirements:
1) transactions using these DML operations will request row locks on the modified rows. Therefore, other transactions cannot update or delete the locked rows until the transaction commit or roll back;
2) Apart from row locks, transactions that use these DML operations must request at least one sub-Row table lock (SX ). If the transaction already has a S, SRX, or X table lock (which is more restrictive than the SX lock), the SX lock is not required. If the transaction already has an SS lock, then the Oracle database automatically converts the SS lock to the SX lock;
3) unless the involved rows are modified, the transaction will not apply a row lock to the rows involved in any subquery or implicit subquery. For example, in the following update operation, use a subquery (the part in the brackets) and an 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, a query can see the rows modified by the previous DML statement, but cannot see the uncommitted changes of other transactions.
