Detailed analysis of three ORACLE lock mechanisms (1)

The ORACLE lock mechanism is mainly used to control concurrent operations to ensure data consistency and accuracy. Next we will learn about the ORACLE lock mechanism.

Oracle RDBMS blocking can be divided into the following three types:

1. Internal Blocking
Internal blocking is used to protect the internal structure of ORACLE. It is implemented inside the system and cannot be accessed by users. Therefore, we do not need to know much about it.

2. DDL-level dictionary/syntax analysis blocking)
DDL-level blocking is also controlled by oracle rdbms, which is used to protect consistency and integrity when data dictionaries and Definitions change. It is automatically locked when the system performs syntax analysis on SQL definition statements. Dictionary/syntax analysis blocking is divided into three types:
1) dictionary operation lock: used to lock the data dictionary when operating on the dictionary. This lock is exclusive and thus can only operate on one dictionary at any time.
2) dictionary definition lock: Used to prevent syntax analysis during dictionary operations. This avoids modifying the structure of a table while querying the dictionary.
3) table definition lock: used when an SQL statement accesses a table, it prevents the items related to the table in the dictionary from being modified.

3. DML-level blocking

DML-level blocking is used to control data manipulation in concurrent transactions to ensure data consistency and integrity. The blocking objects can be tables or rows.
For user data control, the ORACLE lock mechanism can automatically block the manipulated data. However, if there is an authorization, another blocking is implemented to meet the needs of concurrent operations. DML blocking can be implemented by a user process in explicit locking or by implicit SQL statements.

DML locks can be blocked in the following three ways:
1) SHARE a shared block)
2) EXCLUSIVE blocking)
3) share update blocking share update)

Here, SHARE and EXCLUSIVE are used for table blocking, while share update is used for row blocking.

1. Shared table Blocking
Shared table blocking is used to block all data in the table. This lock is used to protect the consistency of the queried data and prevent other users from updating the blocked table. Other users can only apply the share lock to the table, but not the exclusive lock to the table. The share update lock can be applied again, however, processes with shared update blocking are not allowed to be updated. All users who share the table can only query the data in the table but cannot update it. Shared table blocking can only be set using SQL statements. The basic statement format is as follows:

 
 

  
  
[Quote: 04b72348bd] LOCK TABLE<Table Name>[,<Table Name>]...
  
  
In share mode [NOWAIT]
  
  
[/Quote: 04b72348bd]
 
 

Execute this statement to block tables that are shared with one or more tables. If the NOWAIT option is specified and the block cannot be applied successfully for the time being, the system returns and determines whether to wait or execute other statements first.
When a transaction holding a shared lock has one of the following conditions, it is released:
A. Execute the COMMIT or ROLLBACK statement.
B. LOG OFF ).
C. The program stops running.
Shared table blocking is often used in the consistent query process, that is, the table data does not change during data query.

2. Exclusive table Blocking

Exclusive table blocking is used to block all data in a table. Users with this exclusive table blocking can query the table and update the table, other users cannot apply any blocking to the table, including share, exclusive, or share update blocking ). Other users cannot update the table, but can query the table.
Exclusive table blocking can be obtained through the following SQL statement:

 
 

  
  
LOCK TABLE<Table Name>[,<Table Name>]...
  
  
In exclusive mode [NOWAIT]
 
 

Exclusive table blocking can also be obtained implicitly when you execute DML statements INSERT, UPDATE, and DELETE.
A transaction with an exclusive table lock is released when the following conditions occur:
1) execute the COMMIT or ROLLBACK statement.
2) log off from the database)
3) The program stops running.

An exclusive lock is usually used to update data. When an update transaction involves multiple tables, the deadlock can be reduced.