ORACLE locks (oraclelockmode) instance details,

ORACLE locks (oraclelockmode) instance details,

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.

select * from v$lock_type where type in ('TM','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: exclusive

4. According to the concepts

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 ('TM','TX');

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.

Session A:SQL> create table ttt as select * from dba_objects where rownum<=10;

The table has been created.

SQL> update ttt set object_name='TEST' where object_id=20;

1 row updated.

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.

SQL> select * from v$lock where type in ('TM','TX');ADDR     KADDR           SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK-------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------CA4244F4 CA424508        147 TX     393242        563          0          6        270          0C8E7F704 C8E7F71C        140 TM      55156          0          3          0        301          0C8E7F7C8 C8E7F7E0        147 TM      55156          0          3          0        270          0C8ED3C38 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:

SQL> select 393242 / 65536, mod(393242, 65536) 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

SQL> insert into ttt select * from dba_objects;SQL> commit;

Submitted.

SQL> select count(*) from ttt;  COUNT(*)----------   1739045

Then create an index on the modified table.

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 a cursor is opened using the for update substring, all data rows in the return 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, Lock Row Exclusive, and Insert the same record before commit, because the last three locks will always wait for the last three locks, we must release the previous one to continue working.

Level 4 locks include: Create Index, Lock Share locked_mode is 2, 3, and 4, which does not affect DML (insert, delete, update, select) operations, but DDL (alter, drop, etc) an ora-00054 error is prompted for the operation.

00054, 00000, "resource busy and acquire with NOWAIT specified" // *Cause: Resource interested is busy. // *Action: Retry if necessary. 

Level 5 locks include: 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.