Types and Research of Oracle Database locks, oracle Database locks

Types and Research of Oracle Database locks, 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.

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.

Oracle Database locks can be divided into the following categories based on different protected objects:

(1) DML lock (data locks): used to protect data integrity;

(2) DDL lock (dictionary locks): used to protect the structure of database objects (such as the schema definitions of tables, views, and indexes );

(3) Internal locks and latches (Internal locks and latches): protects the Internal database structure;

(4) Distributed locks: Used in OPS (Parallel Server;

(5) PCM locks: Used in OPS (Parallel Server.

The most important lock in Oracle is the DML (also known as data locks) Lock. The purpose of the DML lock is to ensure data integrity in the case of concurrency. 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.

The intention lock means that if an intention lock is applied to a node, the lower node of the node is being locked, the intention lock must be applied to its upper-Layer Nodes first. For example, when locking any row in the table, you must first apply an intention lock to the table where the row is located, and then apply a lock to the row. In this way, when a transaction locks a table, the lock flag of each record in the table is no longer required, and the system efficiency is greatly improved.

There are five types of tmlocks (Table-level locks), which are called shared locks, exclusive locks (X locks), row-level shared locks (RS locks), and row-level exclusive locks (RX locks), shared row-level exclusive lock (SRX lock)

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 types

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

1. Share Table Lock (S ):

Lock Syntax: Lock Table TableName In Share Mode;

Permitted operations: A Shared lock is controlled by one transaction and only allows other transactions to query locked tables. An effective shared lock explicitly uses Select... Lock the row In the For update format, or execute the Lock Table TableName In Share Mode syntax to Lock the entire Table. Other transactions are not allowed to update the Table.

Prohibited operations: A Shared lock is controlled by a transaction to prevent other transactions from updating the table or executing the following statement:

Lock table TableName in share row exclusive mode;

Lock table TableName in row exclusive mode;

2. Exclusive Table Lock, X ):

Exclusive locks are the most restrictive lock types in the lock mechanism. Transactions with exclusive locks are allowed to independently control the write permissions on the table.

Lock Syntax: Lock Table TableName In Exclusive Mode;

Allowed operations: Only one transaction in a table can implement an exclusive lock on the table. The exclusive lock only allows other transactions to query the table.

Operation prohibited: transactions with exclusive locks prohibit other transactions from executing any other DML statements or adding any other types of locks to the table.

Syntax for defining the exclusive lock:

Lock table TableName in exclusive mode;

3. Row-Level Lock (Row Share Table Lock, RS ):

A row-Level Lock (sometimes referred to as Subshare Table Lock (SS for short) requires the transaction to Lock the Table of the locked row in the form of update. When the following statement is executed, the row-Level Lock is automatically added to the operation table.

SELECT... FROM TableName... for update ...;

Lock table TableName in row share mode;

Row-Level Lock (Row Share Table Lock) has the least limit in the Lock type, and is also used to the highest degree of concurrency in the Table.

Permitted operations: Row-level shared locks are controlled by one transaction, allowing other transactions to query, insert, update, delete, or lock rows on the same table at the same time. Therefore, other transactions can obtain row-level locks, shared row-level exclusive locks, row-level exclusive locks, and exclusive locks on the same table at the same time.

Prohibited operations: transactions with row-level locks do not allow other transactions to execute the exclusive locks, I .e:

Lock Table TableName In Exclusive Mode;

4. Row-level Exclusive Table Lock (RX ):

A row-level exclusive Table Lock (SX for short) usually requires the transaction Lock to be updated by one or more rows on the Table. When the following statement is executed, the row-level exclusive lock is added to the operation table.

Insert into TableName ...;

UPDATE TableName ...;

Delete from TableName ...;

Lock table TableName in row exclusive mode;

Row-level exclusive locks are slightly more restrictive than Row-level locks.

Permitted operations: a row-level exclusive lock is owned by a transaction that allows other transactions to perform queries, modifications, inserts, deletes, or simultaneously lock rows on the same table. Transactions with row-level exclusive locks allow other transactions to obtain both the shared locks and row-level exclusive locks on the same table.

Prohibited operations: the row-level exclusive lock is owned by a transaction to prevent other transactions from manually locking the table to exclude the read and write permissions of other transactions. Therefore, other transactions cannot use the following statements to execute lock transactions on the same table.

Lock table table in share mode;

Lock table table in share exclusive mode;

Lock table table IN EXCLUSIVE MODE

5. Share Row Exclusive Table Lock, SRX ):

A shared row-level exclusive Table Lock (SSX) is also known as a shared row-level exclusive Table Lock. It has more limits than a shared Lock. Syntax for defining a shared row-level exclusive lock:

Lock Table TableName In Share Row Exclusive Mode;

Permitted operations: Only one transaction is allowed to obtain a row-level exclusive lock at a specific time point. A row-level exclusive lock transaction allows other transactions to perform queries on the locked table or use Select... From TableName For update... To accurately lock rows but not update rows.

Prohibited operations: transactions with row-level exclusive locks do not allow other transactions to add locks in other forms except the shared locks to the same table or update the table. The following statement is not allowed:

Lock table TableName in share mode;

Lock table TableName in share row exclusive mode;

Lock table TableName in row exclusive mode;

Lock table TableName in exclusive mode;

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.

Analysis of TX lock wait

Row-level locks are generally used in Oracle databases.

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.

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 #: indicates the waiting object, which corresponds to the 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 waiting 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 of the session waiting

. 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: and 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. And

V $ transaction is associated.

Object_id: The identifier of the locked object.

Session_id: indicates the session information holding the lock.

Locked_mode: a message indicating the lock mode in which the session is waiting

Is consistent with the lmode in v $ lock.

Unlock and Kill Session:

Use the following syntax to locate the lock and kill the Session.

Select a. SID, A. SERIAL #, A. USERNAME, B. type from v $ session a, V $ lock B where a. SID = B. SID;

Alter system kill session 'sid, SERIAL #';