Oracle lock types and Parameters

Several lock types and Related Parameters

 

The blocking mechanism is mainly used to control concurrent operations, block interference, and ensure data consistency and accuracy. There are three ways to block Oracle databases: Shared blocking, exclusive blocking, and shared update blocking.
Blocking type
Oracle RDBMS blocking can be divided into the following three types:
1. Internal Blocking
Internal blocking is used to protect the internal structure of ORACLE, which is implemented inside the system and cannot be accessed by users.
2. DDL-level blocking (Dictionary/syntax analysis blocking)
DDL-level blocking is also controlled by oracle rdbms, which is used to protect consistency and integrity when data dictionaries and Definitions change. It is automatically locked when the system performs syntax analysis on SQL definition statements. Dictionary/syntax analysis blocking is divided into three types:
(1) dictionary operation lock: used to lock the data dictionary when operating on the dictionary. This lock is exclusive and thus can only operate on one dictionary at any time.
(2) dictionary definition lock: Used to prevent syntax analysis during dictionary operations, so as to avoid modifying the structure of a table while querying the dictionary.
(3) table definition lock: used when an SQL statement accesses a table, it prevents the items related to the table in the dictionary from being modified.
3. DML-level blocking
DML-level blocking is used to control data manipulation in concurrent transactions to ensure data consistency and integrity. The blocking objects can be tables or rows.
Oracle can automatically block user data for data manipulation. However, if authorization is available, a blocking is implemented to meet the needs of concurrent manipulation. DML blocking can be implemented by a user process in explicit locking or by implicit SQL statements.
DML locks can be blocked in the following three ways:
(1) shared blocking (SHARE)
(2) EXCLUSIVE Blocking)
(3) share update Blocking)
Here, SHARE and EXCLUSIVE are used for table blocking, while share update is used for row blocking.
1. Shared table Blocking
Shared table blocking is used to block all data in the table. This lock is used to protect the consistency of the queried data and prevent other users from updating the blocked table. Other users can only apply the share lock to the table, but not the exclusive lock to the table. The share update lock can be applied again, however, processes with shared update blocking are not allowed to be updated. All users who share the table can only query the data in the table but cannot update it. Shared table blocking can only be set using SQL statements. The basic statement format is as follows:
Lock table <TABLE Name> [, <TABLE Name>]...
In share mode [NOWAIT]
Execute this statement to block tables that are shared with one or more tables. If the NOWAIT option is specified and the block cannot be applied successfully for the time being, the system returns and determines whether to wait or execute other statements first.
When a transaction holding a shared lock has one of the following conditions, it is released:
A. Execute the COMMIT or ROLLBACK statement.
B. LOG OFF ).
C. The program stops running.
Shared table blocking is often used in the consistent query process, that is, the table data does not change during data query.
2. Exclusive table Blocking
Exclusive table blocking is used to block all data in a table. Users with this exclusive table blocking can query the table and update the table, other users can no longer block the table (including share, exclusive, or share update blocking ). Other users cannot update the table, but can query the table.
Exclusive table blocking can be obtained through the following SQL statement:
Lock table <TABLE Name> [, <TABLE Name>]...
In exclusive mode [NOWAIT]
Exclusive table blocking can also be obtained implicitly when you execute DML statements INSERT, UPDATE, and DELETE.
A transaction with an exclusive table lock is released when the following conditions occur:
(1) execute the COMMIT or ROLLBACK statement.
(2) LOG OFF)
(3) The program stops running.
An exclusive lock is usually used to update data. When an update transaction involves multiple tables, the deadlock can be reduced.
3. Shared update blocking method
Shared update blocking is used to block one or more rows in a table. It is also called row-level blocking. Although table-level blocking ensures data consistency, it degrades the concurrency of operation data. Row-level blocking ensures that the updated row is not modified by other users during this period. Therefore, row-level locks can ensure data consistency and improve data operation bursts.
You can obtain row-level blocking in the following two ways:
(1) execute the following SQL blocking statement to display the statement:
Lock table <TABLE Name> [, <TABLE Name>]...
In share update mode [NOWAIT]
(2) Use the following SELECT... for update statement:
SELECT <column Name> [, <column Name>]...
FROM <Table Name>
WHERE <condition>
For update of <column Name> [, <column Name>]... [NOWAIT]
Once a row is blocked, the user can query or update the blocked data row. Other users can only query but cannot update the blocked data row. if other users want to update data rows in the table, they must also apply row-level locks to the table. even if multiple users use shared updates for a table, two transactions are not allowed to update a table at the same time. When updating a table, the table is blocked exclusively, until the transaction is committed or restored. A row lock is always an exclusive lock.
When one of the following conditions appears, the share update lock is released:
(1) execute the COMMIT statement;
(2) LOG OFF)
(3) The program stops running.
The row lock cannot be released when the ROLLBACK operation is executed.
As described above, oracle rdbms locks solve the compatibility and mutex problems of concurrent transactions. Compatibility ensures the concurrency of transactions and mutual exclusion ensures data consistency. The compatibility and mutex of different user locks are given.
The last column in the last row provides other users with the share update lock configured on different rows. However, when user 1 performs an update operation on a row, user 2 can update the blocked row only after user 1 commits a transaction.
The last column in the last row is provided by other users to set the share update lock on different rows. However, when user 1 performs an update operation on a row, user 2 can update the blocked row only after user 1 commits a transaction.

Deadlock
Although blocking can effectively solve concurrent operations, any resource exclusive operation may cause a deadlock. For example, two transactions T1, T2, and T1 apply an exclusive blocking to Data A, while T2 applies an exclusive blocking to data B. Assume that T1 needs to lock data B, because B has been exclusively blocked by T2, so T1 is placed in the waiting state and waiting for B to be released. Now if T2 also needs to block, because A has been exclusively blocked by T1, T2. therefore, T2. In this way, two transactions wait for each other and cannot end. This situation is called a deadlock.
Hybriddb for MySQL automatically discovers deadlocks in the Oracle system, and selects the transactions with the lowest cost, that is, the transactions with the least workload are undo, and all the locks owned by the firm are released so that other transactions can continue to work.
In terms of system performance, we should minimize resource competition and increase throughput. Therefore, when locking concurrent operations, pay attention to the following points:
1. For UPDATE and DELETE operations, only the rows to be modified should be blocked and submitted immediately after the modification is completed.
2. When multiple transactions are updating using the shared update method, do not use the shared block. Instead, use the shared update block so that other users can use the row-Level Lock, to increase concurrency.
3. Try to apply a shared update lock to the concurrent transactions that operate on a table to improve the concurrency.
4. When the application load is high, it is not appropriate to modify the basic data structure (tables, indexes, clusters, and views ).
10 View table locks
Select
S. sid,
S. username,
S. machine,
L. type,
O. object_name,
DECODE (l. lmode,
0, 'none ',
1, 'null ',
2, 'row Share ',
3, 'row exists ',
4, 'share ',
5, 'sh/Row Exlusive ',
6, 'clusive ') lmode,
DECODE (l. request,
0, 'none ',
1, 'null ',
2, 'row Share ',
3, 'row exists',
4, 'share ',
5, 'sh/Row Exlusive ',
6, 'clusive ') request,
L. block
From
V $ lock l,
V $ session s,
Dba_objects o
Where
L. sid = s. sid
And
Username! = 'System'
And
O. object_id (+) = l. id1;
========================================================== ======================================
Table 1 Oracle tmlock types
Lock mode lock description
0 none
1 null SQL operation: Select
2 SS (Row-S) Row-level shared locks. Other objects can only query these data rows.
SQL operations: Select for update, Lock for update, and Lock row share

3 SX (Row-X) Row-level exclusive lock. DML operations are not allowed before submission.
SQL operations: Insert, Update, Delete, Lock row share

4 S (Share) shared lock
SQL operations: Create index, Lock share
5 SSX (S/Row-X) shared Row-level exclusive locks
SQL operation: Lock share row exclusive
6 X (Exclusive) Exclusive lock
SQL operations: Alter table, Drop able, Drop index, Truncate table, Lock exclusive

Table 2 data dictionary view description
Description of main fields in view Name Description
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 #: The waiting object. Corresponds to object_id in dba_objects.

V $ session_wait: query the waiting session information.
Sid: indicates the session information holding the lock.
Seconds_in_wait: indicates the wait 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 information that the session waits. 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: corresponds to the lmode and request in v $ lock.
V $ locked_object only contains DML lock information, including rollback segments and session information.
Xidusn, xidslot, and xidsqn: indicates the rollback segment information. Associated with v $ transaction.
Object_id: The identifier of the locked object.
Session_id: indicates the session information holding the lock.
Locked_mode: indicates the lock mode information that the session waits for, consistent with the lmode in v $ lock.