1. Lock principle Oracle locks are usually a problem at the business layer. locks are designed to ensure Database Consistency in the case of concurrency, so there is no Lock without concurrency. Oracle locks have the following principles:
- The row is locked only when it is modified. Because of the UNDO relationship, the read operation will not produce a row lock.
- When a statement modifies a record, only the record is locked and there is no lock upgrade in the Oracle database.
- When a row is modified, it will block others' modifications to it.
- When a transaction modifies a row, the uplink lock (TX) will be applied to this row to prevent other transactions from modifying the same row.
- Read will never stop writing, but the only exception is select... for update.
- Writing will never block reading.
- After a row is modified, Oracle provides consistent read for data through the rollback segment.
- In Oracle, locking is not a scarce resource, but an attribute of data blocks.
2. tmlock and TX lock
- The TM table lock occurs when the insert, update, delete, and select for update operations are performed to ensure normal operation and prevent others from performing DDL operations on the table.
- The TX lock transaction lock (row lock) prevents other sessions from modifying the data being modified.
The objects of the tmlock and TX lock are different. The tmlock targets the table and the TX lock targets the row, as shown in. modify a row and add two locks, one is to add the TX lock to the modified row to prevent other sessions from modifying the row; the other is to add the tmlock to the table to prevent the table DDL from being modified. 3. tmlock Model)
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.
- Row Exclusive Table Lock (RX) --- 3
This lock, also called a subexclusive table lock (SX), generally indicates that the transaction holding the lock has updated table rows or issued
SELECT... 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.
- Share Table Lock (S) -- 4
A share table lock held by a transaction allows other transactions to query the table (without using SELECT... 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.
- Share Row Exclusive Table Lock (SRX) --- 5
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 (Tables t for SELECT...
UPDATE) but not to update the table.
- Exclusive Table Lock (X) --- 6
This lock is the most restrictive, prohibiting other transactions from locking any type of DML statement or placing any type of lock on
Table. The above is excerpted from the official document and abstract. The following table is easier to understand: 3. demonstrate several locking examples. First, create a test table t:
| SQL> create table t (id int primary key); the table has been created. |
The IDS of the two sessions are: session 1: 200 session 2: 202 1) Insert a piece of data in session 1 and do not submit:
| SQL> insert into t values (1); 1 row has been created. |
Insert the same data record in session 2. Blocking occurs:
| SQL> insert into t values (1 ); |
View v $ lock:
| SQL> select sid, type, id1, id2, lmode, request, block from v $ lock where type in ('TT', 'tx ') order by sid, type; sid ty ID1 ID2 lmode request block ---------- -- ---------- 200 TM 74584 0 0 3 0 0200 TX 524317 6 0 1048 TM 1202 0 3 0 74584 TX 0202 0 4 0 524317 TX 65539 677 6 0 0 |
Session 1 holds the TX lock of mode = 6 on this row, and session 2 requests the TX lock on this row, causing it to be blocked. Note: There is one more lock in 11g (look at the last row). This is a TX lock, and ID1 and ID2 are the same as the previous one. I cannot understand what the lock is. 2) update the row with id = 1 in session 1, not submitted:
| SQL> update t set id = 2 where id = 1; 1 row updated. |
When the same row of data is updated in session 2, blocking occurs:
| SQL> update t set id = 3 where id = 1; |
View v $ lock:
| SQL> select sid, type, id1, id2, lmode, request, block from v $ lock where type in ('TT', 'tx ') order by sid, type; sid ty ID1 ID2 lmode request block ---------- -- ---------- 200 TM 74584 0 0 3 0 0200 TX 131092 6 0 822 TM 1202 0 3 0 74584 TX 0202 0 6 0 0 |
3) Delete deletes a row in sesssion 1 and does not submit it:
| SQL> delete from t where id = 1; delete 1 row. |
Deleting the same row in session 2 causes blocking:
| SQL> delete from t where id = 1; |
View v $ lock:
4. RI lock-based on the reference relationship. When you perform DML operations on tables with a primary foreign key relationship, the lock does not only occur on the operation table, the corresponding referenced table may also be locked. The following describes the scenarios where RI locking causes blocking: Create a master table and a slave table respectively:
| SQL> create table p (id int primary key); the table has been created. SQL> create table c (id int references p (id); the table has been created. |
Insert a row of data into the main table:
| SQL> insert into p values (1); 1 row has been created. |
View v $ lock:
| SQL> select sid, type, id1, id2, lmode, request, block from v $ lock where type in ('TT', 'tx ') order by sid, type; sid ty ID1 ID2 lmode request block ---------- -- ---------- 200 TM 74587 0 0 0 200 TM 74589 3 0 0200 TX 524299 6 0 0 |
The tmlock is applied to both the master table and slave table. 5. deadlock two sessions hold each other's resources, resulting in a deadlock. The emergence of deadlocks indicates that there is a problem with the business design, and the business logic needs to be re-designed. Oracle can automatically monitor deadlocks and force one of the sessions to release resources to solve the deadlock problem. For example, insert data id = 1 in session 1:
| SQL> insert into t values (1); 1 row has been created. |
Insert data id = 2 in session 2:
| SQL> insert into t values (2); 1 row has been created. |
Then input data id = 2 in session 1, blocking occurs:
| SQL> insert into t values (2 ); |
View the lock status from v $ lock:
| SQL> select sid, type, id1, id2, lmode, request, block from v $ lock where type in ('TT', 'tx ') order by sid, type; sid ty ID1 ID2 lmode request block ---------- -- ---------- 200 TM 74584 0 3 0 0200 TX 65550 0 4 687 TX 0200 6 0 262176 TM 648 0 3 0 0202 TX 65550 687 6 0 1 |
Insert the data id = 1 in session 2. The deadlock occurs:
| SQL> insert into t values (1 ); |
When a deadlock occurs, Oracle Automatically releases the resources of one of the sessions to solve the deadlock problem:
| SQL> insert into t values (2); insert into t values (2) * row 1st error: ORA-00060: deadlock detected while waiting for resources |
For the differences between Lock and Latch, see:
