Description of the operating mechanism of Oracle locks

The following article describes the operating mechanism of Oracle locks. We all know that in modern multi-user and multi-task systems, when multiple users access a shared object at the same time, the probability is very high. This object may be a table, row, or memory structure.

To solve the data security, integrity, and consistency problems caused by concurrent access by multiple users, a mechanism is required.

To serialize the concurrent access to these shared resources, Oracle locks can provide such a function. Before a transaction operates on an object, it first sends a request to the system, apply a lock to the lock. After the lock is applied, the transaction has certain control permissions on the Data Object. Before the transaction releases the lock, other transactions cannot perform the select action to update the data object, but the select statement uses the original image data in the undo statement ).

Oracle lock Classification

Oracle locks can basically be classified into two categories

A: share locks) also known as read locks and s locks

B: exclusive locks are also called write locks and x locks.

There are two basic lock types in the database: Exclusive Locks (X lock) and shared lock Share Locks (S lock ). When an exclusive lock is applied to a data object, other transactions cannot read or modify it. Data Objects with a shared lock can be read by other transactions, but cannot be modified. The database uses these two basic lock types to control the concurrency of database transactions.

Lock-protected content classification

Oracle provides a multi-granularity blocking mechanism, which can be divided into protected objects.

A: dml lock and data locks are used to protect data integrity and consistency.

B: ddl locks and dictionary locks are used to protect the structure of data objects, such as table and index definitions.

C: internal locks and latchs are used to protect the internal structure of the database, such as the sga memory structure.

Dml lock

DML locks mainly include tmlocks and TX locks. tmlocks are called table-level locks. tmlocks are classified into five types: S, X, SR, SX, and SRX. TX locks are called transaction locks or row-level locks. When Oracle executes the delete, update, insert, select for update DML statements, Oracle first automatically applies for a TM lock on the table to be operated.

After the tmlock is obtained, automatically apply for the TX lock and set the lock flag (lb) of the actually locked data row. After the record is locked by a session, other sessions that need to access the locked object will wait for the lock to be released in the first-in-first-out mode. For select operations, no Oracle lock is required, so even if the record is locked, the select statement can still be executed. In fact, in this case, Oracle uses the undo content for consistent reading.

In the Oracle database, a TX lock is obtained when a transaction initiates a DML statement for the first time. The lock is kept until the transaction is committed or rolled back. Only the X lock exclusive lock on the Data row), that is to say, the TX lock can only be exclusive lock, and it makes no sense to set a shared lock on the record row. When two or more sessions execute DML statements on the same record of the table, the first session locks the record, and other sessions are in the waiting state. After the first session is submitted, the TX lock is released before other sessions can be locked.

In a data table, Oracle uses a shared lock by default. When executing a dml statement, Oracle applies for a shared lock on the object to prevent other sessions from executing ddl statements on the object, after the shared lock on the application form is successfully applied, the affected records are added to the row to prevent other sessions from modifying the lock.

In this way, you do not need to check the Oracle lock mark row by row when checking the compatibility of the TX lock before the transaction locks. Instead, you only need to check the compatibility of the tmlock mode, which greatly improves the system efficiency. The tmlock includes multiple modes, such as SS, SX, S, and X, which are represented by 0-6 in the database. Different SQL operations generate different types of tmlocks. See table 1.

Introduction to lock-related performance View

V $ lock

SID session sid, which can be associated with v $ session

TYPE distinguishes the TYPE of the lock protection object, such as tm, tx, rt, mr, etc.

The ID1 lock represents 1. For details, see the description below.

The ID2 lock represents 2. For details, see the description below.

The LMODE lock mode is described below.

The Oracle lock mode applied for by the REQUEST, which is the same as the lmode

The time when the CTIME has been held or is waiting for the lock.

BLOCK whether to BLOCK other session lock apply 1: BLOCK 0: not blocked

The value of LMODE is 0, 1, 2, 3, 4, 5, and 6. The larger the number, the higher the lock level, the more operations affected.

Level 1 lock:

Select, which sometimes appears in v $ locked_object.

Level 2 lock: RS lock

The corresponding SQL statements include Select for update, Lock xxx in Row Share mode, and select for update.

When you use the for update substring to open a cursor, all data rows in the returned set will be in Row-X

Oracle locking. Other objects can only query these data rows, but cannot perform update, delete, or select for update.

Operation.