Summary of common ORACLE lock modes (General)

ORACLE is currently the most widely used database in the database field. Now let's quickly analyze which modes of these powerful ORACLE locks are available. ORACLE lock has the following modes: 0: none1: null 2: Row-S Row sharing (RS): Shared table lock 3: Row-X Row dedicated (RX ): used for row modification 4: Share lock (S ):

Or acl e is currently the most widely used database in the database field. Now let's quickly analyze the modes of these powerful o rac le locks. ORACLE lock has the following modes: 0: none 1: n ul l null 2: Row-S Row sharing (RS): Shared table lock 3: Row-X Row dedicated (RX ): used for row modification 4: Share lock (S ):

ORACLE is currently the most widely used database in the database field. Now let's quickly analyze which modes of these powerful ORACLE locks are available.

　　ORACLE locks have the following modes:

0: none
1: null
2: Row-S Row sharing (RS): Share table lock
3: Row-X (RX): used for Row Modification
4: Share lock (S): blocks other DML operations
5: S/Row-X dedicated shared rows (SRX): block other transaction operations
6: exclusive dedicated (X): used for independent access

　　

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

A general query statement such as select... from...; is a lock smaller than 2, and sometimes appears in v $ locked_object.

Select... from... for update; is the lock of 2.

When you use 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.

Insert/update/delete...; is the lock of 3.

Inserting the same record before the commit operation does not respond,
Because the last three locks will always wait for the last three locks, we must release the previous one to continue working.

When an index is created, 3 or 4 locks are generated.

Locked_mode is 2, 3, and 4, which does not affect DML (insert, delete, update, select) operations,
However, DDL (alter, drop, and so on) Operations prompt a ora-00054 error.

When there is a primary foreign key constraint, update/delete...; may produce 4 or 5 locks.

The DDL statement is a 6 lock.

You can use the following SQL statement to view the locks in the current database as a DBA:

 
 

  
  
select object_id,session_id,locked_mode from v$locked_object; 
  
  
select t2.username,t2.sid,t2.serial#,t2.logon_time 
  
  
from v$locked_object t1,v$session t2 
  
  
where t1.session_id=t2.sid order by t2.logon_time; 
  
  
 
 
 

If a long-standing column appears, the lock may not be released.

We can use the following SQL statement to kill abnormal locks that have not been released for a long time:

 
 

  
  
alter system kill session sid,serial#; 
 
 

If a lock problem occurs, a DML operation may wait for a long time and no response is returned.

When you use the direct connection to the database,
Do not use the OS command $ kill process_num or $ kill-9 process_num to terminate the user connection,
Because a user process may generate more than one lock, killing the OS process cannot completely clear the lock.

At the database level, you can use alter system kill session sid, serial #; To kill abnormal locks.

Lock type. Some frequently acquired lock types are as follows:
-TM table lock provides concurrent access to several tables
-TX transaction processing locks or row locks provide concurrent access rows
-The MR media recovery lock indicates that the database files are online.
-The ST space transaction processing lock indicates that the zones in the database due to poor optimization are growing or shrinking. SMON is merging adjacent idle zones.