Lockdown protocol and Isolation level

The database inconsistencies brought by concurrent operations can be grouped into four categories: missing or overwritten updates, dirty reads, non-repeatable reads, and Phantom reads.

(1) Missing or overwritten update (lost update)

(2) Dirty reading

(3) Not repeatable read (Nonrepeatable Read)

A transaction reread the previously read data and finds that the data has been modified by another committed transaction. When transaction 1 reads a data, transaction 2 modifies it, and when transaction 1 reads the data again, it gets a different value from the first time.

(4) Phantom reading
This occurs if a transaction can change the result before it submits the query result. This sentence can also be interpreted as follows, transaction 1 after reading some data records from the database without submitting the query results, transaction 2 deletes some of the records, and transaction 1 reads the data again on the same condition, and finds that some of the records mysteriously disappeared. or transaction 1 after reading certain data records from the database without submitting the query results, transaction 2 inserts some records, and when transaction 1 reads the data again on the same terms, some more records are found.

The main reason for these four types of data inconsistency is that concurrent operations break the isolation of the transaction. concurrency control is to dispatch concurrent operations in the correct way, so that the execution of a user transaction is not interfered by other transactions, thus avoiding the inconsistency of data.

Two locks:

An object that is locked by an X lock exclusive lock can only be read and modified by the transaction that holds the lock, other transactions cannot be added to the object, and the object cannot be read and modified
s lock shared lock object can be read by the lock transaction, but cannot be modified, other transactions can also add S lock on the top.

一、一级 Blockade Protocol
The first-level blockade protocol is that a transaction T must have an X lock on the data R before it is modified until the end of the transaction is released. The end of a transaction includes a normal end (COMMIT) and an abnormal end (ROLLBACK).
A lockdown protocol prevents loss of modification and guarantees that the transaction T is recoverable. Use of the first level of the blockade protocol can resolve the problem of loss modification.
In the first level blockade protocol, if only read data does not modify it, it does not need to be locked, it does not guarantee repeatable read and not read "dirty" data.
二、二级 Blockade Protocol
The second-order blockade protocol is: first-level blockade protocol plus transaction T before reading the data r must be added s lock, read the rear can release S lock.
The two-level blockade protocol, in addition to preventing the loss of changes, can further prevent the reading of "dirty" data. However, in the level two blockade protocol, the S lock can be released after reading the data, so it does not guarantee repeatable reading.
三、三级 Blockade Protocol
The level three blockade protocol is that a first-level blockade protocol plus transaction T must be added s lock to the data R before it is released until the end of the transaction.

The level three blockade protocol, in addition to preventing loss of modifications and not reading "dirty" data, further prevents the unreadable.

Four. Two-stage lock protocol

The two-segment locking protocol means that the execution of each transaction can be divided into two phases: the growth phase (the lock phase) and the fading phase (the unlocking phase).

Lock stage: During this stage, the lock operation can be performed. To request and obtain an S lock before reading any data, apply for and obtain an X lock before writing. If the lock is unsuccessful, the transaction enters the wait state until lock succeeds before continuing.

Unlock phase: When a transaction releases a blockade, the transaction enters the unlock phase, which can only be unlocked at this stage and cannot be locked again.

The two-stage blockade method can be implemented in this way: After the transaction has started, it is in the lock phase, until the execution of rollback and commit are locked. Rollback and commit enable the transaction to enter the unlocking phase, in which the DBMS releases all blockades in the rollback and commit modules.

Concept

Blockade sequence

The two-segment locking protocol means that all transactions must lock and unlock data items in two stages:

1. Apply for and obtain a blockade of any data before it is read and written.

2. In each transaction, all blocking requests precede all unlock requests.

For example, the transaction T1 adheres to the two-segment locking protocol, whose blockade sequence is:

Lock A, read a, a:=a+100, Write A, Lock B, Unlock A, read B, Unlock B, Commit; [1]

It can be proved that any concurrent scheduling policy for these transactions is serializable if all transactions executed concurrently comply with the two-stage lock protocol.

Also pay attention to the similarities and differences of the two-segment locking protocol and one blockade method to prevent deadlock. A blocking method requires that each transaction must lock all data to be used at one time. Otherwise, it cannot proceed, so a blocking method adheres to the two-stage lock protocol; But the two-segment lock protocol does not require that the transaction must lock all data to be used at one time, so a transaction that adheres to the two-part lock protocol may have a deadlock.

In the standard SQL specification, 4 transaction Isolation levels are defined, and different isolation levels differ in the handling of transactions:

Unauthorized read (READ UNCOMMITTED): Dirty reads are allowed, but updates are not allowed to be lost. If a transaction has started to write data, another data does not allow write operations at the same time, but allows other transactions to read the row data. The isolation level can be implemented through exclusive write locks.

Grant Read (Read Committed): Allow non-repeatable reads, but not dirty reads. This can be achieved by "instantaneous shared read lock" and "exclusive write lock". Transactions that read data allow other transactions to continue accessing the row data, but uncommitted write transactions will prevent other transactions from accessing the row.

REPEATABLE READ (REPEATABLE Read): Prevents non-repeatable reads and dirty reads, but sometimes phantom data may occur. This can be achieved through shared read locks and exclusive write locks. Transactions that read data will prohibit write transactions (but read transactions are allowed), and write transactions prohibit any other transaction.

Serialization (Serializable): Provides strict transaction isolation. It requires transaction serialization execution, which can only be performed one after another, but not concurrently. If transaction serialization is not possible only through row-level locks, additional mechanisms must be used to ensure that the newly inserted data is not accessed by the transaction that just performed the query operation.

The higher the isolation level, the more secure the integrity and consistency of the data, but the greater the impact on concurrency performance. For most applications, it is preferable to set the isolation level of the database system to read committed, which avoids dirty reads and has better concurrency performance. Although it can result in the concurrency problems of non repeatable read, virtual read, and second class loss updates, the application can be controlled by pessimistic locks or optimistic locks on individual occasions where such problems may occur.