Oracle blocking Mechanism

 

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.
[B: 8f4f63b9bb] blocking type [/B: 8f4f63b9bb]
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. It is implemented inside the system and cannot be accessed by users. Therefore, we do not need to know much about it.

2. DDL-level 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. This avoids 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) SHARE a shared block)
2) EXCLUSIVE blocking)
3) share update blocking share update)

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:
[Quote: 04b72348bd] LOCK TABLE <表名> [, <表名> ]...
In share mode [NOWAIT]
[/Quote: 04b72348bd]
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 cannot apply any blocking to 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 <表名> [, <表名> ]...
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 from the database)
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.

DML locks can be blocked in the following three ways:
1) SHARE a shared block)
2) EXCLUSIVE blocking)
3) share update blocking share update)
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:
[Quote: 04b72348bd] LOCK TABLE <表名> [, <表名> ]...
In share mode [NOWAIT]
[/Quote: 04b72348bd]
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 cannot apply any blocking to 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 <表名> [, <表名> ]...
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 from the database)
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 <表名> [, <表名> ]...
In share update mode [NOWAIT]
2) use the following SELECT... for update statement to obtain:
SELECT <列名> [, <列名> ]...
FROM <表名>
WHERE <条件>
FOR UPDATE <列名> [, <列名> ] ...... [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 from the database)
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.
A deadlock can be automatically detected in the Oracle System, and the minimum cost is selected, that is, the transactions with the least workload are canceled, all the locks owned by the firm are released, and other transactions are recorded to continue working.
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 table, index, cluster, and view.