Database isolation levels and locks

Database isolation level and transaction lock

1. 1. questions raised

a) update lost

Two transactions update a row of data at the same time, and an update to the data by one transaction overwrites the update of the data by another transaction. This is because the system does not perform any lock operations, so concurrent transactions are not isolated.

b) dirty Read

One transaction reads the result of a data operation that is not committed by another transaction. This is quite dangerous because it is possible that all operations are rolled back.

c) non-repeatable reading

Non-repeatable read (non-repeatable Reads): One transaction repeats two reads of the same row of data, but it gets different results.

The following conditions are included:

I. Virtual read: After a transaction T1 reads a data, the transaction T2 modifies it, and when the transaction T1 reads the data again, it gets a different value than the previous one.

II. Phantom Read (Phantom Reads): The transaction makes two queries during the operation, the result of the second query contains data that is not present in the first query, or the data that appears in the first query is missing (the same SQL statement that does not require two queries). This is due to the fact that another transaction was inserted into the data during the two queries.

1. 2. Database things isolation level

To avoid the above scenarios, in the standard SQL specification, 4 transaction Isolation levels are defined, with different isolation levels dealing differently with transactions.

a) unauthorized read

Also referred to as READ UNCOMMITTED (READ UNCOMMITTED): Dirty reads are allowed, but updates are not allowed to be lost. If one transaction has already started writing data, the other transaction does not allow simultaneous writes, but allows other transactions to read the row data. This isolation level can be achieved through an "exclusive write lock".

b) authorized Read

Also known as read Committed: Allows non-repeatable reads, but dirty reads are not allowed. This can be achieved through "instantaneous shared read lock" and "exclusive write lock". Transactions that read data allow other transactions to continue to access the row's data, but uncommitted write transactions will prevent other transactions from accessing the row.

c) Repeatable READ (repeatableread)

REPEATABLE READ (REPEATABLE READ): Disables non-repeatable reads and dirty reads, but sometimes phantom data can occur. This can be achieved through "shared read lock" and "exclusive write lock". Transactions that read data prohibit write transactions (but allow read transactions), and write transactions prohibit any other transactions.

d) serialization (Serializable)

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

The higher the isolation level, the greater the integrity and consistency of the data, but also 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. It avoids dirty reads and has better concurrency performance. Although it causes concurrency problems such as non-repeatable reads, Phantom reads, and second-class loss updates, the application can be controlled by pessimistic or optimistic locks on individual occasions where such problems may occur.

1. 3. type of lock

A) shared (S) lock : Multiple transactions can block a shared page; The page cannot be modified by any transaction; Usually the page is read and the S Lock is immediately released.

b) Exclusive (x) lock : Only one transaction is allowed to block this page; any other transaction must wait until the X lock is released to access the page; The x lock is released until the end of the transaction.

c) update (U) lock : Used to book an X lock on this page, it allows other transactions to read, but does not allow the use of U-lock or X-lock; When the page being read is to be updated, it is promoted to an X lock, and the U lock is not released until the end of the transaction.

d) Intent (I) lock : is a lock placed at one level of the resource hierarchy to protect shared or exclusive locks on lower-level resources

is lock

If you add an IS lock to a data object, it indicates its descendant node quasi (intent) plus s lock. For example, to add an S lock to a tuple, you first add the is lock to the relationship and database.

IX Lock

If an IX lock is added to a data object, it indicates its descendant node quasi (intent) plus x lock. For example, to add an X lock to a tuple, you would first add an IX lock to the relationship and database.

Six locks

If a data object is added with a SIX lock, it is added S lock, plus IX lock, that is, Six=s+ix. For example, if you add six locks to a table, it means that the transaction reads the entire table (so you want to add an S lock to the table), and the individual tuples are updated (so you want to add an IX lock to the table).

1. 4. Level three lockdown protocol

A) level closure Agreement
The first-level blocking protocol is that transaction T must be X-locked before the data R is modified until the end of the transaction is released. The end of the transaction consists of a normal end (COMMIT) and an abnormal end (ROLLBACK).
　
The first-level blocking protocol prevents loss of modification and guarantees that the transaction T is recoverable. In the first-level blocking protocol, if only the read data does not modify it, it does not need to be locked, it does not guarantee repeatable read and do not read "dirty" data.

b) level two Lockdown protocol
Secondary blocking protocol is: the first level of the blockade protocol plus the transaction T before reading the data R must first add s lock, read the rear can release S lock.
　　
The secondary blocking protocol, in addition to preventing loss of modification, can further prevent the reading of "dirty" data. However, in the level two blocking protocol, the S lock can be released after reading the data, so it cannot guarantee repeatable reads.

c) level three lockdown protocol
　　
Level three blocking protocol is: the first level of the blockade protocol plus transaction T before reading the data r must be added to the S lock, until the end of the transaction is released.
　　
The Level three lockdown protocol prevents non-repeatable reads, in addition to preventing loss of modification and non-reading of "dirty" data.

1. 5. Two- segment Lock Protocol

Two-phase locking protocol refers to the execution of each transaction can be divided into two stages: the growth phase (lock-up phase) and the recession phase (unlocking phase).

Lock phase: The lock operation can be performed at this stage. To request and obtain an S lock before any data is read, apply for and obtain an X lock before the write operation. The lock does not succeed, the transaction goes into a wait state until locking succeeds to continue execution.

Unlocking phase: When a transaction releases a block, the transaction enters the unlock phase, and the lock operation is no longer allowed at this stage.

Note: the similarities and differences between the two-segment lock protocol and the blocking method that prevent deadlocks. A blocking law requires that every transaction must be locked at once for all data to be used, otherwise it cannot continue, so a blocking method adheres to the two-phase lock protocol, but the two-phase 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-phase lock protocol may deadlock.

Database isolation levels and locks