Common types of Oracle Database locks

This article mainly studies the Oracle database lock mechanism in detail. First, we will introduce the Oracle database lock type, and also elaborate, in practical applications, we often encounter lock-related exceptions. In particular, we locate and solve the problem that transactions are suspended due to waiting for locks, the corresponding solution and specific analysis process are proposed for the serious deadlock phenomenon.

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 Oracle 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.

Tmlocks include multiple modes such as SS, SX, S, and X, which are represented by 0-6 in Oracle databases. Different SQL operations generate different types of tmlocks. See table 1.

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.

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 Oracle 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.

Table 1 Oracle tmlock types

Lock mode lock description Description SQL operations

0 none

1 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. Before submission, DML operations such as Insert, Update, Delete, and Lock Row share are not allowed.

4 S (Share) share Lock Create index, Lock Share

5 SSX (S/Row-X) share row-level exclusive Lock share Row exclusive

6 X (Exclusive) exclusive Lock Alter table, Drop able, Drop index, Truncate table, Lock Exclusive

Article by: http://soft.zdnet.com.cn/software_zone/2007/0208/377403.shtml