Oracle Lock (enqueues)

A database is a shared resource used by multiple users. When multiple users access data concurrently, the database generates multiple transactions with simultaneous access to the data. If the concurrency operation is not controlled, it is possible to read and store incorrect data and compromise the consistency of the database.

There are two basic types of locks in the database: Exclusive (Exclusive Locks, X-Lock) and shared (Share Locks, S-lock). When a data object is added to an exclusive lock, other transactions cannot read and modify it; A data object with a shared lock can be read by another transaction, but cannot be modified.

Depending on the protection object, the Oracle database lock can be divided into the following categories:

(1). DML Lock (data locks): Used to protect the integrity of data;

(2). DDL Lock (dictionary locks, Dictionary Lock): The structure used to protect database objects (such as the structure definition of tables, views, indexes);

(3). Internal Locks and latches (internal lock and latch): Protect the internal database structure;

(4). Distributed locks (distributed lock): For OPS (parallel server);

(5). PCM Locks (parallel cache management Lock): For OPS (parallel server).

The most important lock in Oracle is DML (also known as data locks, lock). The purpose of a DML lock is to ensure data integrity in the case of concurrency. In Oracle databases, DML locks mainly include TM and TX locks, where TM locks are called table-level locks, and TX locks are called transaction or row-level locks.

Types of Oracle TM locks

Lock mode	Description of the Lock	Meaning	SQL for locking tables
0	None
1	Null	Empty, this mode is Oracle reserved mode
2	Row Share (RS) is also called (SS)	Row-level shared locks, which are the least restrictive TM locks, provide the highest degree of concurrency, and other sessions can perform any type of DML operation on locked tables, and can coexist with other session locks.	Lock table T in row share mode;
3	Row exlusive Table Lock (RX) also called (SX)	Row-level exclusive locks, which usually have a transaction modified or select...for update to modify the result set. Allow other transactions to lock a table while a SELECT, insert, UPDATE, delete, or lock table is locked	Lock table T in exclusive mode;
4	Share Table Lock (S)	Shared locks, other transactions can query a locked table but cannot modify, allowing only the current transaction to be modified, but multiple transactions can hold it.	Lock table T in share mode;
5	Share Row Exclusive Table Lock (SRX) also known as SSX	Shared row-level exclusive locks, allowing only one transaction to hold and modify locked tables at the same time, and other transactions to query but not modify.	Lock table T in share row exclusive mode;
6	Exclusive Table Lock (X)	Exclusive locks, which are the highest-limit TM locks, prohibit other transactions from executing any type of DML statement or lock table, and a table generally can only be a 6th-number lock.	Lock table T in exclusive mode;

1. Row-level shared lock (Lmode=2,row Share Table lock,rs)

Lock syntax: Lock Table TableName in Row Share Mode;

A row-level lock (sometimes called a subshare table lock, abbreviation, child-shared lock) requires that the transaction be locked in update form on the table of the locked row. A row-level lock is automatically added to the action table when the following statement is executed.

Select...from tablename...for update of ...;

Allowed actions: Row-level shared locks are controlled by one transaction, allowing other transactions to query, insert, UPDATE, delete, or lock rows on the same table at the same time. So other transactions can get row-level locks, shared row-level exclusive locks, row-level exclusive locks, and exclusive locks on the same table at the same time.

Forbidden Operation: A transaction with row-level locks does not allow other transactions to perform an exclusive lock, that is:

Lock Table TableName in Exclusive Mode;

2. Row-level exclusive lock (Lmode=3,row Exclusive Table lock,rx)

lock Syntax: Lock TABLE TableName in ROW EXCLUSIVE MODE;

Row-level exclusive locks (also known as subexclusive table lock, or SX, child exclusive lock) typically require a transaction-owned lock to be updated one or more rows on the table. A row-level exclusive lock is added to the operating table when the following statement is executed.

INSERT into TableName ...;

UPDATE TableName ...;

DELETE from TableName ...;

LOCK TABLE TableName in ROW EXCLUSIVE MODE;

Row-level exclusive locks are slightly more restrictive than row-level locks.

Allowed actions: Row-level exclusive locks are owned by a transaction that allows other transactions to execute queries, modify, insert, delete, or lock rows on the same table at the same time. Only transactions with row-level exclusive locks allow other transactions to receive both shared and row-level exclusive locks on the same table.

Prohibited operations: Row-level exclusive locks are owned by one transaction that prevents other transactions from manually locking the table to exclude read and write access to other transactions. Therefore, other transactions do not allow row lock transactions to be performed on the same table using the following statement.

LOCK table table in SHARE MODE;

LOCK table table in SHARE EXCLUSIVE MODE;

LOCK table table in EXCLUSIVE MODE;

3. Shared Lock (Lmode=4,share Table lock,s)

Lock syntax: Lock Table TableName in Share Mode;

Allowed actions: A shared lock is controlled by one transaction, allowing only other transactions to query the table that is locked. A valid shared lock explicitly has a select. The For update form locks the row, or executes the lock table TableName in Share mode syntax to lock the entire table and does not allow updates by other transactions.

Forbidden Action: A shared lock is controlled by a transaction to prevent other transactions from updating the table or executing the following statement:

Lock Table Tablename in Share Row Exclusive Mode;

Lock Table Tablename in row Exclusive Mode;

4. Shared row-level exclusive lock (lmode=5,share row Exclusive Table lock,srx)

shared row-level exclusive locks, sometimes called shared child exclusive locks (Share subexclusive Table lock,ssx), have more restrictions than shared locks. The syntax for defining a shared row-level exclusive lock is:

LOCK TABLE TableName in SHARE ROW EXCLUSIVE MODE;

Allowed actions: Allows only row-level exclusive locks that a transaction obtains at a certain point in time. Owning a row-level exclusive lock transaction allows other transactions to execute a query on a locked table or use Select ... From TableName for Update ... To accurately lock the row and not update the row.

Forbidden operations: Transactions that have row-level exclusive locks do not allow other transactions to have other forms of locks except shared locks on the same table or update the table. That is, the following statement is not allowed:

LOCK TABLE TableName in SHARE MODE;

LOCK TABLE TableName in SHARE ROW EXCLUSIVE MODE;

LOCK TABLE TableName in ROW EXCLUSIVE MODE;

LOCK TABLE TableName in EXCLUSIVE MODE;

5. Exclusive Lock (lmode=6)

An exclusive lock is one of the most restrictive types in the lock mechanism, allowing transactions with exclusive locks to control write permissions on the table alone.

Lock syntax: Lock Table Tablename in Exclusive Mode;

Allowed actions: Only one transaction in a table can have an exclusive lock on the table, and an exclusive lock allows only other transactions to query the table.

Forbidden Operation: A transaction with an exclusive lock prohibits other transactions from executing statements of other DML types or adding any other type of lock on the table.

Some experiments on locks:

1. Simulate insert,update and delete examples of blocking, and explain the corresponding information in V$lock, give a SQL demo.

1.1. The two sessions that will be used in the experiment:

1 sql> Select Sid from V$mystat where rownum=1;2 3     SID4----------5      1

1 sql> Select Sid from V$mystat where rownum=1;2 3     SID4----------5     48

A table to use:

1 sql> CREATE TABLE txt (ID int primary key,name VARCHAR2) 2 3 table Created.4 5 sql> desc txt;6  name                 Nu ll?         Type7  --------------------------------------------------
8  ID not                 NULL number      (9)  NAME                            VARCHAR2 (10)

1.2. Blocking caused by analog insert

Insert a statement in Session 1:

1 sql> INSERT INTO TXT values (1, ' AA '); 2 3 1 row created.

At this point session 1 is not yet commit, but session 48 inserts the following statement:

The following statement is used to query the resulting:

1 sql> Select Sid,type,id1,id2,lmode,request,block from V$lock where type in (' TX ', ' TM ') Order by 1,2;2 3     Sid    T Y     ID1       ID2        lmode     REQUEST    BLOCK4------------------------------------------------------------ --5      1     TM    73545       0          3          0      1     TX    589837     745         6          0    0 TM 73545          3          0     TX    524309     964         6          0    589837 TX     745         0          4          0

From the block=1 that can be seen sid=1 that the session is blocking other sessions, lmode=6 indicates that the level of the lock is 6, and request=4 in sid=48 indicates that the current session is waiting for a lmode=4 lock, meaning that the session is being blocked.

1.3, simulation of the congestion caused by the update

Let's look at the contents of the table txt first:

1 sql> SELECT * from txt;2 3     ID        NAME4--------------------5      1         AA

Change this line in Session 1:

1 sql> Update txt set name= ' zz ' where id=1;2 3 1 row updated.

At this point session 1 does not execute commit, in session 48 also to change the line:

Use the following statement to query:

1 sql> Select Sid,type,id1,id2,lmode,request,block from V$lock where type in (' TX ', ' TM ') Order by 1,2;2 3     Sid    T Y     ID1       ID2       lmode      REQUEST    BLOCK4------------------------------------------------------------ --5      1     TM    73545       0          3          0      1     TX    589841     745         6          0    0 TM 73545          3          0     TX    589841     745         0          6          0

This is not a description, and the situation is broadly similar to insert.

1.4. Simulate the blocking caused by delete

Execute the DELETE statement in Session 1:

1 sql> Delete txt;2 3 1 row deleted.

Session 48 also executes the DELETE statement at this point:

Use the following statement to query:

1 sql>  Select Sid,type,id1,id2,lmode,request,block from V$lock where type in (' TX ', ' TM ') Order by 1,2;2 3     sid
   ty      ID1      ID2       lmode      REQUEST     BLOCK4-------------------------------------------------------- ------5      1     TM    73545       0          3           0         1     TX    458783     613         6           0     TM    73545       0          3           0     TX    458783     613         0           6         0

1.5, the information in the V$lock description:

1 sql>  Select Sid,type,id1,id2,lmode,request,block from V$lock where type in (' TX ', ' TM ') Order by 1,2;2 3     sid
   ty      ID1      ID2       lmode      REQUEST     BLOCK4-------------------------------------------------------- ------5      1     TM    73545       0          3           0         1     TX    458783     613         6           0     TM    73545       0          3           0     TX    458783     613         0           6         0

which

SID: The session information that holds the lock

Type: Represents the types of locks, values include TM and TX, and so on.

ID1: Represents the identity of the object

ID2:ID1+ID2 locating an address on the rollback segment (that is, before the data mirroring address is modified)

Lmode: Lock mode

Request: Request a lock mode

Block:a value of either 0 or 1, depending on whether or not the "lock in question" is the blocker

2. Simulate an RI lock causing the blocking scenario, and analyze the v$lock corresponding locking information to give a SQL demo.

The experiment will create two new tables:

1 sql> CREATE TABLE TAB1 (ID int primary key,name VARCHAR2) 2 3 table Created.4 5 sql> CREATE table tab2 (ID ref Erences tab1 (ID), num varchar2 (6) 7 Table created.

2.1. Insert a record into table Tab1 in Session 1:

1 sql> INSERT into TAB1 values (1, ' AA '); 2  3 1 row created. 4  5 sql> Select Sid,type,id1,id2,lmode,request,block from V$lock where type in (' TX ', ' TM ') o Rder by 1, 2; 6  7     SID    TY     ID1        ID2       lmode     REQUEST    BLOCK 8--------------------------------------- -----------------------9      1     TM    73547        0          3         0          010      1     TM    73549        0          3         0          011      1     TX    327703      784         6         0          012 sql> Select Object _name from Dba_objects where object_id in (73547,73549); Object_name16------------------------------------------- -------------------------------------TAB118 TAB2

As can be seen, when the main table executes the INSERT statement, the main table and the table are added lmode=3 lock, then in session 48, the table tab2 insert a record:

1 sql> Select Sid,type,id1,id2,lmode,request,block from V$lock where type in (' TX ', ' TM ') Order by 1, 2; 2  3     SID    TY     ID1       ID2       lmode     REQUEST      BLOCK 4--------------------------------------- -----------------------5      1     TM    73547       0          3         0            0 6      1     TM    73549       0          3         0            0 7      1     TX    327703     784         6         0            1 8     TM    73549       0          3         0            0 9     TM    73547       0          3         0            010     TX    589851     746         6         0            011     TX    327703     784         0         4            012 7 rows Selected.

This will cause blocking, and session 48 will apply for a lmode=4 lock.

2.2, in Session 1 in table tab2 insert a record, but first look at TAB1 is the contents of the table:

1 sql> select * from Tab1; 2  3     ID NAME 4--------------------5      1 AA 6  7 sql> insert INTO TAB2 values (2, ' BB '); 8 insert INTO Tab 2 values (2, ' BB ') 9 *10 ERROR at line 1:11 ora-02291:integrity constraint (JACK. sys_c0010811) violated-parent key not12 found

Directly to insert records from the table, if the main table is not there will be an error violation referential integrity constraints, no main table basis.

2.3. In session 1, perform a delete operation on the Table tab:

1 sql> Delete from tab2; 2  3 1 row deleted. 4  5 sql> Select Sid,type,id1,id2,lmode,request,block from V$lock where type in (' TX ', ' TM ') o Rder by 1, 2; 6  7     SID    TY     ID1       ID2       lmode      REQUEST    BLOCK 8--------------------------------------- -----------------------9      1     TM    73547       0          3          0          010      1     TM    73549       0          3          0          011      1     TX    131085     737         6          0          012 sql> Select Object _name from Dba_objects where object_id in (73547,73549); Object_name16------------------------------------------- -------------------------------------TAB118 TAB2

When a delete operation is performed from a table, the main table and the lmode=3 lock are added to the table in session 48, and the record is changed in TAB1:

1 sql> update tab1 set name= ' where id=1; 2  3 1 row updated. 4  5 sql> select * from TAB1; 6  7     ID NAME 8--------------------9      1 3310 One SQL > select Sid,type,id1,id2,lmode,request,block from V$lock where type in (' TX ', ' TM ') Order by 1,2;12     Sid    Ty
   id1        ID2       lmode     REQUEST    BLOCK14------------------------------------------------------------ --15      1     TM    73549       0           3         0          016      1     TM    73547       0           3         0          017      1     TX    131085     737          6         0          018     TM    73547       0           3         0          019     TX    524313     972          6         0          0

is not blocked in the main table.

3. Give a SQL instance that causes a deadlock.

The table txt will be used in the experiment.

First look at the contents of the TXT, and then insert a record in a session:

Session 1:

1 sql> SELECT * FROM txt;2 3 no rows selected4 5 sql> inserts into TXT values (2, ' cc '); 6 7 1 row created.

To insert another record in another session:

Session 48:

1 sql> INSERT INTO TXT values (3, ' GG '); 2 3 1 row created.

At this point in Session 1, insert the session 481-like record:

Insert the first record in session 62 with the session:

At this point two sessions are blocked, and session 48 first blocked the session 1,session 1 and then blocked session 48, which produced a deadlock. Oracle automatically solves the deadlock problem by forcing the previously locked session to end and reporting the Ora 60 error.

1 sql> INSERT INTO TXT values (3, ' GG '); 2 insert to TXT values (3, ' GG ') 3             ERROR at line 1:5 Ora-00060:deadlock Detected while waiting for resource