Types and Research of Oracle Database locks

A database is a shared resource used by multiple users. When multiple users access data concurrently, multiple transactions can access the same data simultaneously in the database. If concurrent operations are not controlled, incorrect data may be read and stored, compromising Database Consistency.

Locking is a very important technology for implementing database concurrency control. Before a transaction operates on a data object, it first sends a request to the system to lock it. After the lock, the transaction has certain control over the data object. Before the transaction releases the lock, other transactions cannot update the data object.

There are two basic lock types in the database: Exclusive Locks (X lock) and 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-related exceptions often occur in actual applications, such as waiting for the lock transaction to be suspended or deadlocked. If the problem cannot be solved in time, the normal execution of the application will be seriously affected, at present, there is a lack of systematic research and guidance for solving such problems. Based on the practical experience, this article puts forward the corresponding solutions and specific analysis processes.

Oracle Database lock type

Based on different protected objects, Oracle Database locks can be divided into the following categories: DML lock data locks, data locks), used to protect data integrity; DDL lock dictionary locks, dictionary locks ), it is used to protect the structure of database objects, such as the definition of tables and indexes, internal locks and latches, and protect the internal structure of the database.

The purpose of the DML lock is to ensure data integrity in the case of concurrency. This article mainly discusses the DML lock. In Oracle databases, DML locks mainly include tmlocks and TX locks. tmlocks are called table-level locks, and TX locks are called transaction locks or row-level locks.

When Oracle executes the DML statement, the system automatically applies for a TM lock on the table to be operated. After the tmlock is obtained, the system automatically applies for the TX lock and places the lock flag of the actually locked data row. 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.

Table 1: Oracle tmlock type
Lock mode	Lock description	Explanation	SQL operations
0	None
1	NULL	Null	Select
2	SS (Row-S)	Row-level shared locks. Other objects can only query these data rows.	Select for update, Lock for update, Lock row share
3	SX (Row-X)	Row-level exclusive locks. DML operations are not allowed before submission.	Insert, Update, Delete, Lock row share
4	S (Share)	Shared lock	Create index, Lock share
5	SSX (S/Row-X)	Shared row-level exclusive locks	Lock share row exclusive
6	X (Exclusive)	Exclusive lock	Alter table, Drop able, Drop index, Truncate table, Lock exclusive

Only the X lock exclusive lock on the Data row ). 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. 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.

When the Oracle database has a TX lock wait, if not timely processing will often cause the Oracle database to suspend, or cause the occurrence of a deadlock, produce ORA-60 errors. These phenomena will cause great harm to the actual application, such as long time not responding, a large number of transactions failed, etc.

Analysis of TX lock wait

After introducing the types of local Oracle Database locks, we will discuss how to effectively monitor and solve the lock wait phenomenon and how to locate the cause of the deadlock when a deadlock occurs.

View data dictionaries related to monitoring locks are an important part of Oracle databases. You can query the data dictionary view to obtain database information. The data dictionary view related to the lock is shown in table 2.

Table 2: Data Dictionary view description
View name	Description	Description of main fields
V $ session	Queries session information and lock information.	Sid, serial #: indicates the session information. Program: indicates the application information of the session. Row_wait_obj #: The waiting object. Corresponds to object_id in dba_objects.
V $ session_wait	Queries the information of a pending session.	Sid: indicates the session information holding the lock. Seconds_in_wait: indicates the wait duration. Event: indicates the session waiting Event.
V $ lock	Lists All locks in the system.	Sid: indicates the session information holding the lock. Type: indicates the lock Type. Values include TM and TX. ID1: The identifier of the lock object. Lmode, request: indicates the lock mode information that the session waits. It is represented by numbers 0-6 and corresponds to table 1.
Dba_locks	Format the view of v $ lock.	Session_id: corresponds to the Sid in v $ lock. Lock_type: corresponds to the type in v $ lock. Lock_ID1: corresponds to ID1 in v $ lock. Mode_held, mode_requested: corresponds to the lmode and request in v $ lock.
V $ locked_object	It only contains DML lock information, including rollback segments and session information.	Xidusn, xidslot, and xidsqn: indicates the rollback segment information. Associated with v $ transaction. Object_id: The identifier of the locked object. Session_id: indicates the session information holding the lock. Locked_mode: indicates the lock mode information that the session waits for, consistent with the lmode in v $ lock.

In daily work, if you find that the database does not respond for a long time when you execute an SQL statement, the TX lock wait may occur. To solve this problem, we should first find the lock-holding transaction and then perform relevant processing, such as committing the transaction or forcibly suspending the transaction.

Deadlock monitoring and resolution in databases, when two or more sessions request the same resource, a deadlock occurs. Common types of deadlocks are row-level locks and page-level locks. Row-level locks are generally used in Oracle databases. The deadlock of Row-level locks is discussed below.

When Oracle detects a deadlock, it interrupts and rolls back the execution of statements related to the deadlock, reports an ORA-00060 error and records it in the database's log file alertSID. log. A trace file is generated under user_dump_dest to describe the deadlock information in detail.

In daily work, if an error message for the ora-00060 is found in the log file, it indicates a deadlock. In this case, you need to find the corresponding trace file and locate the cause based on the trace file information.

If the query results show that the deadlock is caused by the bitmap index, you can solve the deadlock after changing the IND_T_PRODUCT_HIS_STATE index to the normal index.

Related Articles]

• Hints adjustment mechanism used by Oracle

• Oracle remote replication and Remote Disaster Recovery classic Analysis

• Detailed description of data replication technology in Oracle Distributed System