[Oracle] Lock

The lock in Oracle is usually a problem at the business layer. The lock aims to ensure Database Consistency in the case of concurrency, so there is no lock without concurrency. Oracle locks include the following:

The lock in Oracle is usually a problem at the business layer. The lock aims to ensure Database Consistency in the case of concurrency, so there is no lock without concurrency. Oracle locks include the following:

1. Lock Principle

The lock in Oracle is usually a problem at the business layer. The lock aims to ensure Database Consistency in the case of concurrency, so there is no lock without concurrency. Oracle locks have the following principles:

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 tmlock and TX lock objects 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)

Row Share (RS) -- 2

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, which is abstract. The following table is easier to understand:

3. demonstrate several lock examples

First, create the 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

Sessions 2: 202

1) Insert

Insert a piece of data in session 1 without submitting:

SQL> insert into t values (1 );

One 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 3 0 0

200 TX 524317 1048 6 0 1

202 TM 74584 0 3 0 0

202 TX 524317 1048 0 4 0

202 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

Update the row with id = 1 in session 1 and do not submit it:

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 3 0 0

200 TX 131092 822 6 0 1

202 TM 74584 0 3 0 0

202 TX 131092 822 0 6 0

3) Delete

Delete a row in sesssion 1 and do not submit it:

SQL> delete from t where id = 1;

One row has been deleted.

Deleting the same row in session 2 causes blocking:

SQL> delete from t where id = 1;

View v $ lock:

4. RI lock-Locking Based on the reference relationship

When you perform DML operations on a table with a primary/foreign key relationship, the lock does not only occur on the operation table, but may also be locked on the referenced table. 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 );

One 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 3 0 0

200 TM 74589 0 3 0 0

200 TX 524299 1051 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 deadlocks, for example:

Insert data id = 1 in session 1:

SQL> insert into t values (1 );

One row has been created.

Insert data id = 2 in session 2:

SQL> insert into t values (2 );

One 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 0

200 TX 65550 687 0 4 0

200 TX 262176 648 6 0 0

202 TM 74584 0 3 0 0

202 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 3 has an error:

ORA-00060: deadlock detected while waiting for resources

For the differences between Lock and Latch, see: