Orale lock mechanism Summary

A lock is a mechanism to prevent data interruption when two transactions operate on the same data source (table or row. Oracle uses block technology to ensure the serializability of concurrent operations. Oracle locks fall into two categories: Data locks (also known as DML locks) and dictionary locks. Dictionary locks are used internally by Oracle DBMS to block dictionary tables. Dictionary locks include syntax analysis locks and DDL locks. When necessary, DBMS automatically locks and releases locks, which are not controlled by users.

Oracle provides five data locks: Share table lock (s lock), exclusive table lock (x lock), and row-Level Lock (row share table lock, (RS lock), row exclusive table lock (RX lock), and share row exclusive table lock (SRX lock ). The blocking granularity includes row-level and table-level.

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. Allow multiple transactions to apply a shared lock to the same table. In this case, the transaction update table that is locked on this table is not allowed (even if a transaction controls a table like select row... For update is not allowed ). Therefore, only one shared lock of one transaction can update the table if other transactions share the same transaction on 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. See the compatibility matrix of Data locks.

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: in the Data lock compatibility matrix, it is easy to see that the row-level exclusive lock is owned by a transaction and allows other transactions to execute queries, modify, insert, delete, or lock rows on the same table at the same time. 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 in share mode;

Lock table in share exclusive mode;

Lock 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;

DML automatic lock obtained by DML statements (Oracle implicit lock)

Compatibility Matrix of DML automatic lock:

X: exclusive locks, RS: Row-level shared locks, RX: Row-level exclusive locks, S, shared locks, rsx: Row-level shared exclusive locks

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 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 the 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 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 #';