Example of all Oracle locks

The following articles mainly introduce the working mechanism of Oracle locks and the introduction of Oracle locks. The actual proportion of Oracle locks is still the majority. If you are curious about it, the following articles will unveil its mysteries. Hope you can learn from it.

0-none

1-null (NULL)

2-row-S (SS)

3-row-X (SX)

4-share (S)

5-S/Row-X (SSX)

6-exclusive (X)

0-None

1-NULL)

2-row sub-Sharing Mode SS)

3-row sharing mutex mode SX)

4-Shared Mode S)

5-row-Child shared mutex Mode

6-mutex mode X)

Type and description of Oracle locks caused by DML operations:

 
 

  
  
Operation Lock Mode LMODE Lock Description  
  
  
------------------------- ---------- ------ -----------------  
  
  
Select NULL 1 null  
  
  
Select for update SS 2 sub share  
  
  
Insert SX 3 sub exclusive  
  
  
Update SX 3 sub exclusive  
  
  
Delete SX 3 sub exclusive  
  
  
Lock For Update SS 2 sub share  
  
  
Lock Share S 4 share  
  
  
Lock Exclusive X 6 exclusive  
  
  
Lock Row Share SS 2 sub share  
  
  
Lock Row Exclusive SX 3 sub exclusive  
  
  
Lock Share Row Exclusive SSX 5 share/sub exclusive  
  
  
Alter table X 6 exclusive  
  
  
Drop table X 6 exclusive  
  
  
Create Index S 4 share  
  
  
Drop Index X 6 exclusive  
  
  
Truncate table X 6 exclusive  
  
  
-----------------------------------------------------------  
 
 

Compatibility of Oracle locks. YES indicates that locks can exist together, and NO indicates mutual exclusion.

 
 

  
  
NULL SS SX S SSX X  
  
  
------ ---- ---- ---- ----- ----  
  
  
NULL| YES YES YES YES YES YES  
  
  
SS | YES YES YES YES YES NO  
  
  
SX | YES YES YES NO NO NO  
  
  
S | YES YES NO YES NO NO  
  
  
SSX | YES YES NO NO NO NO  
  
  
X | YES NO NO NO NO NO