ORACLE locks (oraclelockmode)

This article describes in detail a common basic knowledge about ORACLE locks. If you need it, you can check it out.

This article describes in detail a common basic knowledge about ORACLE locks. If you need it, you can check it out.

This article describes in detail a common basic knowledge about ORACLE locks. If you need it, you can check it out.

ORACLE locks have the following modes:
0: none
1: null
2: Row-S Row sharing (RS): Shared table lock, sub share
3: Row-X exclusive (RX): used for Row modification, sub exclusive
4: Share share lock (S): block other DML operations, Share
5: S/Row-X shared Row exclusive (SRX): block other transaction operations, share/sub exclusive
6: exclusive (X): used for independent access, exclusive

1. The type provided by oracle can be queried Based on the type in v $ lock_type. We usually have the most contact with two types.

The Code is as follows:
Select * from v $ lock_type where type in ('TT', 'tx ')

View the description. We can probably know the information about the two locks. TM is used to access objects synchronously, and TX is related to transactions.

3. You need to know two concepts:

(1). Lock data, that is, ROW-Level Lock. There is only one type: exclusive (ROW) Lock)
(2). Lock the table lock, that is, the metadata lock metadata (table). There are five types:

2 RS: row share
3 RX: row exclusive
4 S: share
5 SRX: share row exclusive
6 X: exclusive4.

We can find at least two items from this table ,. the first is what kind of locks are corresponding to each database operation (refer to the column in the middle), and the second is between each kind of locks. If there is any conflict in the future, the so-called conflict is whether the current database operations are stuck. Y * indicates that if the two operations are locked for the same row, there will be a conflict, and the operation will wait until the previous operation is completed, otherwise it will remain there; if it is not the same row, it will not conflict, and subsequent operations will not wait. for example, assume that operation A performs update on the record with id = 1, and Operation B deletes the record with id = 2. According to the table description, during operation on A, the TM-Level Lock will be RX, and only one TX-Level Lock will be X. On B, there will be A TM-Level Lock that will be RX, and only one TX-Level Lock will be X, according to the table, when RX encounters RX, if two operations are not the same record, there will be no conflict. Therefore, both operations of AB will add a tmlock first, add a TX lock, and then perform the corresponding operations smoothly. If you change the record id of Operation B to 1, the two record operations are the same record, and conflict occurs on the tmlock, therefore, the B operation will wait for the operation to be submitted (that is, after the TX lock of A is released), B will generate A TX lock and A tmlock to complete the operation accordingly, otherwise, it will remain stuck and wait for A to release the TX lock.

5. Common dynamic performance views:

Select * from v $ lock_type where type in ('TT', 'tx ');

The Code is as follows:
Select * from v $ lock; Select * from v $ transaction;

Focus on the v $ lock View:

Set A scenario first: update the records of A table in session A, and do not submit the update. Delete the same record of the modified table in session B.

The Code is as follows:
Session: SQL> create table ttt as select * from dba_objects where rownum <= 10;

The table has been created.

The Code is as follows:
SQL> update ttt set object_name = 'test' where object_id = 20;

1 row updated.

The Code is as follows:
SQL> Session B: SQL> delete from ttt where object_id = 20;

At this time, because A has not been submitted, B will remain stuck and open A session C to query the related v $ lock view.

The Code is as follows:
SQL> select * from v $ lock where type in ('TT', 'tx '); Addr kaddr sid ty ID1 ID2 LMODE REQUEST CTIME BLOCK ---------------------------------------------------------------------------------------- CA4244F4 ca450508 147 TX 393242 563 0 6 270 0 C8E7F704 C8E7F71C 140 TM 55156 0 3 0 301 0 C8E7F7C8 C8E7F7E0 147 TM 55156 0 3 0 270 0 C8ED3C38 C8ED3D54 140 TX 393242 563 6 0 301 1

We can clearly see that two sid locks are generated. for sessions with sid 140, a tm lock and a TX lock are generated. The TM lock mode (LMODE) is 3, (3 is RX: row exclusive, which is consistent with the table comparison, the RX lock is generated when the operation is update. For sessions with sid 147, a tm and a TX lock are also generated, and the TM lock mode (LMODE) is used) = 3 (3 is RX: row exclusive, which is consistent with the table comparison. when the operation is delete, the RX lock is generated), while the TX lock mode (LMODE) is 0, waiting for a lock. from the definition of v $ lock_type, we can also see that the type is a tm lock, and ID1 indicates object_id. to query dba_objects, we can easily find that the locked object is the obj of TTT. from the BLOCK in the last column (this block does not represent a BLOCK, but a block) = 1, we can also see that after the session with sid = 140 generates the TX lock, the subsequent operation also modifies this record. Therefore, BLOCK + 1 indicates blocking other operations and simultaneously operating this record.

In addition, query the select * from v $ transaction; view to obtain the associated information.

From the description of the TX lock in v $ lock_type, we can know that TX is related to transactions. therefore, you can see that the ADDR value is the same as the value of v $ transaction when viewing the TX lock information on v $ lock. it can even be calculated based on the ID1 value. Which segment is locked? Remove and obtain the 16 power of the remainder 2 according to the ID1 of TX:

The Code is as follows:
SQL> select 393242/65536, mod (393242,655 36) from dual; 393242/65536 MOD (393242,65536) ----------------------------- 6.00039673 26

It's amazing to find that XIDUSN and XIDSLOT in v $ transaction correspond!

6. Add an operation. The lock operation will be generated when the index is created:

Insert a lot of data into the ttt table

The Code is as follows:
SQL> insert into ttt select * from dba_objects; SQL> commit;

Submitted.

The Code is as follows:
SQL> select count (*) from ttt; COUNT (*) ---------- 1739045

Then create an index on the modified table.

The Code is as follows:
SQL> create index idx_ttt on ttt (object_id );

When creating an index, query the v $ lock table

We can find that two tmlocks are generated during index creation. The lock categories are 4 and 3, respectively. We can query the objects locked by the two TM:

According to the query results, it is found that the object_id of lmode = 4 corresponds to the TTT table, and LMODE = 4 corresponds to the S lock of TM.

Summary

The larger the number, the higher the lock level, the more operations affected.

Level 1 locks include Select, which sometimes appears in v $ locked_object.
Level 2 locks include Select for update, Lock For Update, and Lock Row Share.
Select for update when the dialog uses the for update substring to open a cursor, all data rows in the returned set will be locked exclusively at the Row level (Row-X, other objects can only query these data rows and cannot perform update, delete, or select for update operations.
Level 3 locks include Insert, Update, Delete, and Lock Row Exclusive.
Inserting the same record before the commit operation does not respond, because the last 3 lock will always wait for the last 3 lock, and we must release the previous one to continue working.
Level 4 locks include: Create Index, Lock Share
Locked_mode is 2, 3, 4 does not affect DML (insert, delete, update, select) operations, but DDL (alter, drop, etc.) operations will prompt a ora-00054 error.
00054,000 00, "resource busy and acquire with NOWAIT specified"
// * Cause: Resource interested is busy.
// * Action: Retry if necessary.
Level 5 locks: Lock Share Row Exclusive
Specifically, when there is a primary foreign key constraint, update/delete...; may produce 4 or 5 locks.
Level 6 locks include Alter table, Drop table, Drop Index, Truncate table, and Lock Exclusive.