The locking mechanism of Oracle

A lock is a mechanism that prevents data from being interactively corrupted when two transactions operate on the same data source (table or row). Oracle uses blocking technology to ensure the serialization of concurrent operations. Oracle's locks fall into two main categories: data locks (also called DML locks) and dictionary locks. The dictionary lock is used internally by the Oracle DBMS to block the dictionary tables. Dictionary locks include parsing and DDL locks, which are automatically locked and released by the DBMS when necessary, and user-inorganic control.

Oracle provides 5 types of data locks: Shared locks (Share table lock, abbreviated S-lock), exclusive lock (Exclusive table lock, abbreviated X-Lock), row-level lock (row Share table lock, RS Lock), row-level row lock (row Exclusive table lock, referred to as RX Lock) and shared row level exclusive lock (Share row Exclusive Table Lock, abbreviated as SRX Lock). Its blocking granularity includes row level and table level.

1. Shared Lock (Share Table lock,s):

Lock syntax: Lock Table TableName in Share Mode;

Allowed actions: A shared lock is controlled by one transaction, allowing only other transactions to query the table that is locked. A valid shared lock is explicitly used with select ... The For update form locks the row, or executes the lock table TableName in Share mode syntax to lock the entire table and does not allow updates by other transactions. Allows multiple transactions to share a lock on the same table, in which case a transaction Update table with a lock on the table is not allowed (even if there is a transaction control that is shaped like a select row ... for update such a row lock is not allowed). Therefore, only one shared lock of a transaction can update the table if other transactions also have the same transaction on the table.

Forbidden Action: 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 Lock (Exclusive Table lock,x):

An exclusive lock is a type of lock that is most restrictive in the lock mechanism, allowing a transaction with its lock to control write access to the table on its own.

Lock syntax: Lock Table TableName in Exclusive Mode;

Allowed actions: Only one transaction in a table can have an exclusive lock on the table, and exclusive locks allow only other transactions to query the table.

Forbidden Operation: A transaction with an exclusive lock prohibits other transactions from executing any other DML-type statement or adding any other type of lock on the table.

Defines the syntax for an 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, or SS, child shared Lock) requires that the transaction be locked in update form on the table of the locked row. A row-level lock is automatically added to the action table when the following statement is executed.

SELECT ... From TableName ... For UPDATE of ...;

LOCK TABLE TableName in ROW SHARE MODE;

Row-level Locks (row Share table lock) are the least restrictive in the lock type, and are the most used in the concurrency of the table.

Allowed actions: 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. So other transactions can get row-level locks on the same table at the same time, shared row-level exclusive locks, row-level exclusive locks, and exclusive locks. See Compatibility matrix diagram for data locks

Forbidden Operation: A transaction with row-level locks does not allow other transactions to perform an exclusive lock, that is:

Lock Table TableName in Exclusive Mode;

4. Row-level exclusive lock (row Exclusive Table Lock,rx):

Row-level exclusive locks (also known as subexclusive table lock, referred to as SX, sub-exclusive locks) typically require a transaction-owned lock to be updated one or more rows on the table. When the following statement is executed, row-level row locks are added to the operating 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.

Allowable actions: Compatibility matrix diagram in data locks it is easy to see the travel level exclusive lock by a transaction owns allows other transactions to execute queries, modify, insert, delete, or lock rows on the same table at the same time. Transactions with row-level locks allow other transactions to have both shared and row-level locks on the same table.

Prohibited operations: Row-level exclusive locks are owned by a transaction that prevents other transactions from manually locking the table to exclude read and write access to other transactions. Therefore, other transactions do not allow the use of the following statement on the same table to perform a lock transaction.

LOCK table table in SHARE MODE;

LOCK table table in SHARE EXCLUSIVE MODE;

LOCK table table in EXCLUSIVE MODE

5. Shared row-level exclusive lock (Share row Exclusive Table LOCK,SRX):

Shared row-level exclusive locks are sometimes called shared sub-exclusive locks (Share subexclusive Table lock,ssx), which have more restrictions than shared locks. The syntax for defining a shared row-level exclusive lock is:

Lock Table TableName in Share Row Exclusive Mode;

Allowed actions: Only one transaction is allowed to get row-level exclusive locks at a time. Owning a row-level exclusive lock transaction allows other transactions to execute a query on a locked table or use Select ... From TableName for update ... To accurately lock a row without updating the row.

Forbidden Operation: A transaction with row-level exclusive locks does not allow other transactions to have other forms of locks except shared locks on the same table or update the table. That is, 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;

DML Automatic Lock (Oracle implicit Lock) obtained by DML statement

Compatible matrix graphs for DML automatic locks:

X: Exclusive lock, RS: Row level shared lock, RX: Row level exclusive lock, S, shared lock, RSX: Row level shared exclusive lock

DML statements have row-level lock types

SELECT ... From table

INSERT into Table ... X RX

UPDATE table ... X RX

DELETE from Table ... X RX

SELECT ... From table ... For UPDATE of ... X RS

LOCK Table Table in ...

ROW SHARE MODE RS

ROW EXCLUSIVE MODE RX

SHARE MODE S

SHARE EXCLUSIVE MODE SRX

EXCLUSIVE MODE X

Lock-related tables or views in a data dictionary

V_$locked_object

V_$locks_with_collisions

V_$lock_activity

V_$lock_element

V_$_lock

Dbms_lock

V$dlm_all_locks

V$dlm_locks

V$enqueue_lock

V$global_blocked_locks

V$lock

V$locked_object

V$locks_with_collisions

V$lock_activity

V$lock_element

V$_lock

Unlock and kill Session:

Use the following syntax to isolate 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# ';

The locking mechanism of Oracle