In-depth introduction to Oracle locks-principles

In modern multi-user multi-task systems, it is inevitable that multiple users access a shared object simultaneously. This object may be a table, row, or memory structure, to solve the data security, integrity, and consistency problems caused by multi-user concurrent access, a mechanism is required to serialize the concurrent access to these shared resources, oracle locks can provide this function. Before a transaction operates on an object, it first sends a request to the system and adds the corresponding lock to the transaction, after the lock is applied, the transaction has certain control permissions on the Data Object. Before the transaction releases the lock, other transactions cannot update the data object (select can be performed, however, 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 are also called 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 and share locks ). 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 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. There is only an X lock (exclusive lock) on the Data row. That is to say, the Tx lock can only be an 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, when the consistency of the Tx lock is checked before the transaction locks, the lock mark does not need to be checked row by row. 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.
Lock mode applied for by request, same as 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 the dialog box uses the for update substring to open a cursor, 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 lock (RX lock)
The corresponding SQL statements include insert, update, delete, lock XXX in row exclusive mode, and no commit.
Inserting the same record before will not respond, because the last 3 locks will always wait for the last 3 locks, we
You must release the previous one to continue working.

Level 4 lock: s lock
The corresponding SQL statements include: Create index, lock XXX in share mode

Level 5 lock: SRX lock
The corresponding SQL statements include lock XXX in share row exclusive mode. When the primary and foreign key constraints exist, update/delete...; may produce 4 or 5 locks.

Level 6 lock

The SQL statements corresponding to the X lock include ALTER TABLE, drop table, drop index, truncate table, and lock XXX in exclusive mode.

 

The value meanings of id1 and Id2 vary according to the value of type.
For TM locks
Id1 indicates that the object_id of the locked table can be associated with the dba_objects view to obtain specific table information. The Id2 value is 0.
For TX locks
Id1 uses a decimal value to indicate the rollback segment number occupied by the firm and the slot number in the transaction slot, in the form of a group:
0 xrrrrssss, RRRR = RBS/undo number, ssss = Slot Number
Id2 represents the number of times that wrap in decimal format, that is, the number of times that the transaction slot is reused.

V $ locked_object
Xidusn undo segment number, which can be associated with V $ transaction
Xidslot undo Slot Number
Xidsqn serial number
Object_id of the locked object, which can be associated with dba_objects
Session_id holds the session_id of the lock, which can be associated with V $ session
Oracle account whose oracle_username holds the lock
OS _user_name: operating system account holding the lock
Process ID of the process operating system, which can be associated with V $ Process
Locked_mode: The same as V $ lock. lmode.

The content of dba_locks is similar to that of V $ lock.

V $ session if a session is blocked because some rows are locked by other sessions, the following four fields in the View list the object information of these rows.
Row_wait_file # file number of the row to be waited
Row_wait_obj # object_id of the row to be waited
Row_wait_block # block to which the row to be waited belongs
Row_wait_row # position of the waiting row in blcok

Manually release the lock
Alter system kill session 'sid, serial #';

 

This article from the csdn blog, reproduced please indicate the source: http://blog.csdn.net/tangyangbuaa/archive/2009/09/07/4526146.aspx