Understanding locks and latches (2) Overview of lock mechanisms

 

A lock is a mechanism that prevents multiple transactions from accessing the same resource from affecting each other. Generally, high-concurrency databases need to use the lock mechanism to solve the problem of Concurrent Data Access, consistency and integrity.
The resources mentioned above can be roughly divided into two types:
● User objects: such as tables and data rows
● Transparent system objects to users, such as shared data structures in memory and information in data dictionaries

Oracle implicitly manages the locks required by SQL when any SQL statement is executed. Therefore, you do not need to explicitly lock resources. By default, Oracle uses the lock mechanism to minimize data access restrictions and achieve high data concurrency while ensuring data consistency. Oracle also supports manual locking.

Lock type
There are many division methods, which are listed in column 2:
By Data Type
① Protect metadata ---> tmlock (Table-Level Lock)
There are 5 Table-level locks and 2 important ones: lmode type (V $ lock query)
3 RX
6 x
② Protect data ---> TX lock (transaction lock)
A transaction corresponds to a transaction lock (TX), which is generated by row-level locks (X. Transaction locks are available when row-level locks exist. Therefore, a transaction must start with at least three locks: Tx, X, and Rx.
By action Type
① Protect chain ---> lock
② Protect the buffer ---> Latch

Lock mode
In highly concurrent environments, Oracle adopts two locks:
(I) x mode (exclusive lock mode): the lock in this mode must be obtained when data is modified. The first exclusive lock is the only thing that can be modified for the resource until the exclusive lock is released.
(Ii) s mode (shared lock mode): multiple users who read data can share the data. These users can share the lock on resources, prevent other users from modifying this resource concurrently (the user who modifies the data needs exclusive locks ). Multiple transactions can apply a shared lock to the same resource.

Lock duration
The locks obtained by each statement in the transaction are valid during the transaction execution period to prevent destructive mutual interference between transactions. If the SQL statement in a transaction modifies the data, only the transaction that starts after the transaction is committed can see the Modification result of the former. When a transaction is committed or rolled back, Oracle releases the lock obtained by each SQL statement in the transaction. When a user rolls back to a savepoint in a transaction, Oracle also releases the lock obtained after this point. The wait transaction does not lock available resources, but continues to wait until the transaction with the resources it is waiting for is committed or rolled back.

 

Lock conversion and lock upgrade
For data rows, Rx is already the most restrictive lock, so no lock conversion is required ).
For data tables, Oracle can automatically convert locks with lower limitations to locks with higher limitations. For example, a transaction uses the Select... for update statement to lock data rows. In this case, the transaction obtains the row-Level Lock X on the relevant data row and the RS on the relevant data table. If this transaction updates some data rows, the previously obtained RS will be automatically converted to Rx.

Lock escalation refers to a lock that is upgraded from a database to a higher granularity level (for example, a table) by a lock at a low granularity level (for example, a data row level. For example, if a user has locked multiple rows of data in a table, some database management systems upgrade these row-level locks to a single table-Level Lock. At this time, the number of locks in the system is reduced, and the limit on the locked resources is increased. The Oracle database does not have a lock upgrade. The lock upgrade significantly increases the possibility of a deadlock. For example, the database tries to upgrade the Lock of a resource in transaction T1, and transaction T2 also has the Lock of this resource. If transaction T2 also needs to upgrade the lock at this time, a deadlock will occur.