The blocking mechanism is mainly set up to control concurrent operations, block interference and ensure the consistency and accuracy of data. There are three types of Oracle database locking: shared locking, exclusive locking, and shared update locking.

1. Internal lockdown

The internal level blockade is used to protect the internal structure of ORACLE. It is implemented internally and cannot be accessed by users, so we don’t need to know too much about it.

Ddl-level locking is also controlled by the Oracle DBMS to protect the consistency and integrity of data dictionaries and data definition changes. It is the system to automatically lock the SQL definition statement when parsing, without the user to do. There are three types of dictionary/parsing blocks:

  1. Dictionary operation lock: Used to lock data dictionaries while operating on dictionaries. This lock is exclusive and protects only one dictionary from being operated on at any time.
  2. Dictionary definition lock: Prevents parsing at the same time as dictionary operations. This prevents changing the structure of a table while querying the dictionary.
  3. Table definition lock: Used to prevent dictionary items related to a table from being modified if an SQL statement is properly accessing the table.

DML level blocking is used to control data manipulation in concurrent transactions and ensure data consistency and integrity. The blocking object can be a table or a row. For manipulation of user data, Oracle can automatically block manipulated data, but if manipulation authorization is available, it can also block concurrent manipulation. DML locking can be done either explicitly by a user process or implicitly by some SQL statements.

DML locks can be locked in the following three ways:

  1. SHARE Blocking Mode (SHARE)
  2. EXCLUSIVE mode
  3. SHARE UPDATE

SHARE, EXCLUSIVE, and SHARE UPDATE are used to block tables and rows.

1. Table blocking in shared mode

In shared table locking mode, all data in a table is locked. The lock is used to protect the consistency of query data and prevent other users from updating the locked table. Other users can only apply a shared lock on the table, but cannot apply an exclusive lock on the table. The shared update lock can be applied again, but the process holding the shared update lock is not allowed to update the table. All users sharing the table can only query the data in the table, but cannot update it. Table blocking in shared mode can only be set by users using SQL statements. The base statement format is as follows:

LOCK TABLE< table name >[,< table name >]...IN SHARE MODE [NOWAIT]
Copy the code

Execute this statement to impose a shared table blockade on one or more tables. When NOWAIT is specified, if the block cannot be successfully applied, the user is returned and decides whether to wait or execute another statement first. A transaction holding a shared lock releases its shared lock when one of the following conditions occurs:

A. Execute COMMIT or ROLLBACK statements. B. Exit the database (LOGOFF). C. The program stops running.

Shared mode Table blocking is commonly used during consistency queries, where the data in a table does not change while the data is being queried.

2, exclusive mode table blockade

Exclusive mode table blocking is used to block all data in a table. An exclusive mode table blocking user can query and update the table. Other users cannot block the table (including sharing, exclusive, or shared update blocking). Other users can query the table although they cannot update it. Exclusive table blocking can be explicitly obtained by using the following SQL statement:

LOCK TABLE[,< table name >]....IN EXCLUSIVE MODE [NOWAIT]
Copy the code

Exclusive table blocking can also be obtained implicitly when the user executes THE DML statements INSERT, UPDATE, or DELETE. A transaction that has an exclusive mode table block releases the block when one of the following conditions occurs:

A. Execute COMMIT or ROLLBACK statements. C. The program stops running.

Exclusive blocking is typically used to update data, reducing deadlocks when multiple tables are involved in an update transaction.

3. Sharing update blocking mode

Shared update blocking is the blocking of one or more rows of a table, thus also known as row-level blocking. Table – level locking ensures data consistency but weakens the parallelism of operational data. Row-level blocking ensures that the updated row is not modified by other users between the time the user obtains the updated row and the time the row is updated. Therefore, row – level locking can ensure the consistency of data and improve the burst of data operation. Row-level blocking can be obtained in two ways:

  1. Execute the following SQL block statement to display:
LOCK TABLE[,< table name >]....IN SHARE UPDATE　MODE [NOWAIT]
Copy the code
  1. Use the following SELECT… The FOR UPDATE statement obtains:
SELECT< column name >[, column name >]...FROMThe < table >WHEREConditions of < >FOR UPDATE OF[,< column name >]..... [NOWAIT]
Copy the code

Once a user has imposed a row-level blockade on a row, the user can query and update the blocked rows, while other users can query but not update the blocked rows. If other users want to update rows in the table, row-level locks must be applied to the table as well. Even if multiple users use shared updates on a table, two transactions are not allowed to update the same table at the same time. When the table is actually updated, it is locked exclusively until the transaction is committed or restored. Row locks are always exclusive locks.

Release the update lock when one of the following conditions occurs: A. COMMIT; C. The program stops running.

The ROLLBACK operation cannot release the row lock. It can be seen from above that oracle DBMS lock mechanism solves the compatibility and mutual exclusion problem of concurrent transactions. Compatibility ensures transaction concurrency and mutual exclusion ensures data consistency. The compatibility and mutual exclusion of different user locks are shown in the following figure. The last row and last column provide other users to set the SHARE UPDATE lock on different rows. However, when user 1 updates a row, user 2 can update the blocked row only after user 1 commits a transaction. Deadlock locking can effectively solve concurrent operations, but any resource monopolization will have the danger of deadlock. For example, there are two transactions T1 and T2, where T1 imposes an exclusive blockade on data A and T2 imposes an exclusive blockade on data B. Suppose T1 wants to lock data B. Since B has been exclusively blocked by T2, T1 is placed in the waiting state, waiting for B to be released. Now if T2 also wants to block A, since A has been exclusively blocked by T1, T2 is also put in wait state. This creates a state in which two transactions wait for each other and never end, a condition called a deadlock. In Oracle, deadlocks are automatically discovered, and the transaction with the least cost, that is, the least amount of work done, is withdrawn. All locks owned by the transaction are released, and other transactions continue to work.

In terms of system performance, you should minimize resource contention and increase throughput. Therefore, when locking concurrent operations, note the following:

  1. For UPDATE and DELETE operations, only the rows to be changed should be blocked and committed as soon as the changes are made.
  2. When multiple transactions are being updated using shared updates, do not use shared update locking, but use shared update locking so that other users can use row-level locking to increase parallelism.
  3. If possible, a shared update lock is imposed on concurrent transactions of a table operation to improve parallelism.
  4. Changes to the underlying data structures (tables, indexes, clusters, and views) are not appropriate during periods of high application load.

Reprinted from: Simple Book – fold up low

Article: www.jianshu.com/p/bb0fdfa4a…