Oracle lock Classification

After reading some information on the Internet, I found thatOracleThere are multiple lock classification methods in, and the lock is analyzed from different angles.

Prerequisites:

• DDL(Database definition language): Database definition language, such as create table,Drop table .....
• DML(Database modification language ):Modify the database language, as shown in figureInsert, Delete,Update ......

OracleThe lock body is divided into the following types:

1. Automatic locks and display locks are divided by users and systems.

A) Automatic lock:
When a database operation is performed, the system automatically acquires all necessary locks for the database operation by default.

B) Show lock:
In some cases, you need to display the data required to lock the database operation to make the database operation better. The display lock is set for the database object.

2. Locks can be divided into shared locks and exclusive locks.

A) Shared lock:
A shared lock allows a transaction to share access to a specific database resource. Another transaction can also access this resource or obtain the same shared lock. Shared locks provide high concurrency for transactions, but are like poor transaction Design+Shared locks are prone to deadlocks or data update loss.

B) Exclusive lock:
After an exclusive lock is set for a transaction, the transaction obtains the resource separately, and the other transaction cannot obtain the share lock or exclusive lock for the same object before the transaction is committed.

3. Divided by operation, can be dividedDMLLocks and DDL locks

A) DMLLocks can be divided into row locks, table locks, and deadlocks.

I. Row lock:
When a transaction performs database insert, update, or delete operations, the transaction automatically obtains the exclusive lock of the Operation rows in the operation table.

II. table-Level Lock :< br> after a transaction acquires a row lock, this transaction will also automatically obtain the table lock for this row ( shared lock ), to prevent other transactions from updating record rows from being affected by the DDL statement. A transaction can also obtain a shared or exclusive lock during the process. Only when the transaction displays the definition of an exclusive lock displayed using the lock TABLE statement can the transaction obtain the exclusive lock on the table , You can also use the lock table display to define a table-level shared lock (for the specific usage of lock table , refer to the relevant documentation ).

III. Deadlock:
A deadlock occurs when two transactions need a set of conflicting locks and cannot continue the transaction.
For example, if transaction 1 is in a tableARow record #3 contains an exclusive lock and waits for the transaction2Record in Table#4The release of the exclusive lock in, while transaction 2 is in the tableARecord row #4 contains an exclusive lock and waits for the transaction1Record in Table#3Release of the exclusive lock, transaction 1 and transaction2Wait for each other, resulting in a deadlock. Deadlock is generally caused by poor transaction design. Only SQL statements can be used for deadlocks.: Alter system kill session 'sid, serial #'Or use the command of the kill process of the relevant operating system, as shown in figureUNIXKill-9 Sid, or use other tools to kill the deadlock process.

B) DDLLocks can also be divided into: exclusive DDL locks, sharingDDLLock, analysis lock

I. Arrange itDDLLock:
Create, modify, and delete DDL statements of a database object to obtain the exclusive lock of the operation object.
For example, when using the alter table statement, in order to maintain data integrity, consistency, and legitimacy, the transaction obtains a rowDDLLock.

II. ShareDDLLock:
DDL statements that need to establish dependency between database objects usually need to be shared to obtainDDLLock.
If you create a package, the process in the package is different from that in the function reference database table. When you compile this package, the transaction will be shared with the reference table.DDLLock.

III. Analysis lock:
Oracle uses shared pool storage for analysis and optimizedSQLStatement and PL/SQLProgramTo make applications that run the same statement faster. An object cached in the Shared Pool obtains the analysis lock of the database object it references. The analysis lock is unique.DDLLock type. Oracle uses it to track the dependency between the Shared Pool object and its referenced database object. When a transaction modifies or deletes a database object held by the shared pool with an analysis lock,OracleInvalidate the objects in the shared pool. The next time you reference this SQL/PLSQL sentence,OracleRe-analyze and compile this statement.

IV. Internal locks:
This isOracleA special lock used for sequential access to the internal system structure. When writing information to the buffer zone, in order to use this block of memory area, Oracle must first obtain the latch of this block of memory area before writing data to this block of memory.

The blocking mechanism is mainly used to control concurrent operations, block interference, and ensure data consistency and accuracy.OracleThere are three database blocking methods: Shared blocking, exclusive blocking, and shared update blocking.

OracleRDBMSCan be divided into the following types:

1. Internal Blocking
Internal blocking is used for protectionOracleThe internal structure is implemented internally by the system and cannot be accessed by users. Therefore, we do not need to know much about it.

2. DDLLevel blocking (Dictionary/Syntax analysis blocking)
DDLLevel blocking is also causedOracleRDBMSIt is used to protect the consistency and integrity of data dictionary and data definition changes. It isSQLThe Definition Statement is automatically locked for syntax analysis. Dictionary/There are three types of syntax analysis blocking:

A) Dictionary operation lock:
The lock is exclusive for data dictionary operations, so that only one dictionary can be operated at any time.

B) Dictionary definition lock:
It is used to prevent syntax analysis during dictionary operations. This avoids modifying the structure of a table while querying the dictionary.

C) Table definition lock:
ForSQLWhen a table is accessed by a statement, the items related to the table in the dictionary are modified.

3. DML level blocking
DML -level blocking is used to control data manipulation in concurrent transactions, to ensure data consistency and integrity, the blocked objects can be tables or rows.
Oracle can automatically block the manipulated data, the block is implemented to meet the needs of concurrent operations. DML blocking can be performed by a user process in an explicit way or by implicit SQL statements.
DML locks can be blocked in three ways:
( 1 ) shared block ( share )
( 2 ) exclusive blocking ( exclusive )
( 3 ) shared update blocking ( share update )
where share , exclusive is used for table blocking, and share update is used for row blocking.

A) shared table blocking
shared table Blocking is to block all data in the table,
the lock is used to protect the consistency of queried data and prevent other users from updating blocked tables. 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 the SQL statement. The basic statement format is as follows: lock table table name [, table name ]... in share mode [Nowait] run 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, the system returns the result 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 , run the commit or rollback statement.
B and exit the database ( log off ).
C . The program stops running.
shared tables are usually used in the consistent query process. That is, the table data does not change during data query.

B) exclusive table blocking
exclusive table blocking is used block all data in the 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 performed by the user in the DML statement insert , Update and Delete are implicitly obtained.
if a transaction with an exclusive table block exists, the transaction is released when the following conditions are met: ( 1 ), run commit , or rollback statements.
( 2 ). log out of the database ( 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.

C) 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 BLOCK statement, which is displayed as follows:
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) Submit the execution ( Commit ) Statement;
(2) log out of the database ( Log Off )
(3) The program stops running.
Run Rollback The operation cannot release the row lock.
As shown above, Oracle RDBMS The locking mechanism solves the compatibility and mutex problems of concurrent transactions. Compatibility ensures the concurrency of transactions and mutual exclusion ensures data consistency. 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.

Although blocking can effectively solve concurrent operations, any resource exclusive operation may cause a deadlock. For example, there are two transactions.T1,T2,T1For dataAApply an exclusive lock,T2For dataBExclusive blocking is imposed. Further hypothesisT1To pair dataBLock, becauseBAlreadyT2Exclusive blocking, soT1Placed in the waiting status, waitingBReleased; ifT2You must alsoABecauseAAlreadyT1Exclusive blocking, soT2It is also placed in the waiting state. In this way, two transactions wait for each other and cannot end. This situation is called a deadlock.

InOracleThe system can automatically detect deadlocks and select the transactions with the lowest cost, that is, the transactions with the least workload will be canceled, all locks owned by the firm will be released, and other transactions will 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.UpdateAndDeleteOnly the rows to be modified should be blocked and submitted immediately after modification.

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 ).