This paper introduces the types and research of Oracle database locks.

This paper, through the research of Oracle Database lock mechanism, this paper introduces the type of Oracle database lock, and describes the abnormal situation of lock related in the actual application, especially to the problem that the transaction is suspended because of waiting for the lock, and the deadlock is a serious phenomenon. The corresponding solution method and the concrete analysis process are put forward.

A database is a shared resource that is used by multiple users. When multiple users have concurrent access to the data, multiple transactions are generated in the database while the same data is being accessed. Without control of concurrent operations, incorrect data can be read and stored, which destroys the consistency of the database.

Lock is a very important technology to realize concurrency control of database. When a transaction is operating on a data object, the system is asked to lock it before it is made. After the lock transaction has some control over the data object, the other transaction cannot update the data object until the transaction releases the lock.

There are two basic types of locks in the database: exclusive locks (Exclusive Locks, i.e., x locks) and shared locks (Share Locks, or S locks). When a data object is added to the exclusive lock, no other transaction can read and modify it. Data objects with shared locks can be read by other transactions, but cannot be modified. The database uses these two basic lock types to concurrently control the transactions of the database.

Lock-related anomalies that are often encountered in practical applications such as waiting for the lock transaction is suspended, deadlock, and so on, if not resolved in time, will seriously affect the normal implementation of the application, and currently for the solution of such problems lack of systematic research and guidance, this article in the summary of practical experience, based on The corresponding solution method and the concrete analysis process are put forward.

Lock type for Oracle database

Depending on the object being protected, Oracle database locks can be grouped into the following categories: DML locks (data locks, database locks) to protect the integrity of data, DDL locks (Dictionary locks, Dictionary locks), which protect the structure of database objects, such as tables, Structure definitions such as indexes, internal locks and latches (internal locks and latches) to protect the internal structure of the database.

The purpose of DML lock is to guarantee the data integrity in concurrency, this article mainly discusses the DML lock. In Oracle databases, DML locks consist primarily of TM and TX locks, where TM locks are called table-level locks, and TX locks are called transaction locks or row-level locks.

When Oracle executes a DML statement, the system automatically requests a TM type of lock on the table to be manipulated. When the TM lock is obtained, the system automatically requests the TX type of lock and resets the lock flag bit of the actual locked data row. In this way, check the TX lock compatibility before the transaction is locked without further checking the lock flag, and only check the compatibility of TM lock mode, which can greatly improve the efficiency of the system. TM locks include SS, SX, S, X, and many other modes, represented in the database in 0-6. Different SQL operations produce different types of TM locks. As shown in table 1.

There is only an X lock (exclusive lock) on the data line. In an Oracle database, a TX lock is obtained when a transaction initiates a DML statement for the first time, and the lock remains until the transaction is committed or rolled back. When two or more sessions execute a DML statement on the same record on the table, the first session is locked on the record, and the other session is in the waiting state. When the first session is committed, the TX lock is freed and the other sessions can be locked.

When a TX lock wait occurs in an Oracle database, failure to process it often causes the Oracle database to hang, or cause deadlocks to occur, resulting in ORA-60 errors. These phenomena will cause great harm to the actual application, such as long time not responding, a large number of transaction failures.

Analysis of TX Lock wait

After describing the types of Oracle database locks, the following discusses how to effectively monitor and resolve lock-waiting phenomena, and how to locate deadlocks when a deadlock occurs.

The related view data dictionary of the Monitor lock is an important part of the Oracle database, the user can query the data dictionary view to obtain the information of the database. The data dictionary view associated with the lock is shown in table 2.

TX Lock wait monitoring and resolution in daily work, if the database is found to be unresponsive for a long time while executing some SQL, it is likely that a TX lock wait is generated. To solve this problem, you should first identify the transaction that holds the lock, and then do the related processing, such as committing the transaction or breaking the transaction forcibly.

Deadlock monitoring and resolution in a database, a deadlock occurs when two or more sessions request the same resource. Common types of deadlocks are row-level and page-level locks, which are typically used in an Oracle database using row-level locks. The following discusses the deadlock behavior of row-level locks.

When Oracle detects deadlock generation, interrupts and rolls back the execution of deadlock-related statements, reports ORA-00060 errors and records them in the database log file AlertSID.log. At the same time, a trace file is generated under User_dump_dest to describe the deadlock information in detail.

In daily work, if you find that ora-00060 error messages are recorded in the log file, a deadlock is generated. At this point, we need to find the corresponding trace file, according to the tracking file information to locate the reason.

If the query results indicate that the deadlock is caused by the bitmap index, the deadlock problem can be resolved by changing the ind_t_product_his_state index to the normal index.

Table 1 TM Lock types for Oracle
Lock mode	Lock description	Explain	SQL operations
0	None
1	Null	Empty	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 lock, DML operation not allowed before committing	Insert, Update, Delete, Lock Row share
4	S (Share)	Shared locks	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

Table 2 Data dictionary View description
View Name	Describe	Primary field Description
V$session	Information about the query session and the lock.	Sid,serial#: Represents session information. Program: Represents application information for a session. Row_wait_obj#: Represents the Waiting object. Corresponds to the object_id in the dba_objects.
V$session_wait	The query waits for session information.	SID: The session information that holds the lock. Seconds_in_wait: Indicates the waiting time information Event: Events that represent the waiting of a session.
V$lock	Lists all the locks in the system.	Sid: The session information that holds the lock. Type: Represents the types of locks. Values include TM and TX, and so on. ID1: The identity of the object that represents the lock. Lmode,request: A letter that represents the lock mode of a session waiting Interest. With the number 0-6, and table 1 corresponds.
Dba_locks	A formatted view of the V$lock.	SESSION_ID: Corresponds to the SID in V$lock. Lock_type: Corresponds to type in V$lock. LOCK_ID1: Corresponds to the ID1 in V$lock. Mode_held,mode_requested: And V$lock The lmode,request corresponds.
V$locked_object	Contains only the lock information for DML, including rollback segments and session information.	XIDUSN,XIDSLOT,XIDSQN: Indicates rollback segment information. And V$transaction associated. OBJECT_ID: Indicates the identity of the locked object. SESSION_ID: Represents the session information that holds the lock. Locked_mode: A letter that represents the lock mode of a session waiting , and V$lock in the lmode of the same.