Isolation level of a transaction with lock request and release

Dirty Reads (Dirty read)

Dirty reads mean that one transaction reads uncommitted data from another transaction, and this data is likely to be rolled back

Non-repeatable read (unrepeatable read)

Non-repeatable reading means that in database access, two identical queries within a transaction range return different data. This is caused by the commit of the other transaction modifications in the system during the query.

For example: Transaction B executes two times for a query, and when the first execution finishes, transaction a modifies its data. Data changes when querying again in transaction B

Phantom Read (Phantom Read)

Phantom reading is a phenomenon that occurs when a transaction is not executed independently, such as when the first transaction modifies data in a table, which involves all rows of data in the table. At the same time, the second transaction modifies the data in the table by inserting a new row of data into the table. Then the user who will be working on the first transaction in the future finds that there are no modified rows of data in the table, as if the illusion had occurred.

Concurrency problems can be summed up in the following categories:

A. Missing updates: When undoing a transaction, overwrite the updated data that was committed by other transactions (A and B transactions are executed concurrently, a transaction executes the update, commits; B transaction after the A transaction is updated, the B transaction completes the update operation on the row's data before the end, and then rolls back, the two update operations are lost).
B. Dirty reads: One transaction reads uncommitted update data from another transaction (A and B transactions execute concurrently, b transaction performs an update, a transaction query B transaction does not commit data, and B transaction is rolled back, the data obtained by a transaction is not real data in the database. That is, dirty data, which is inconsistent with the data in the database).
C. Non-repeatable reads: One transaction reads the updated data that has been committed by another transaction (A and B transactions are executed concurrently, a transaction queries the data, and then B transactions update the data, and a is found when the data is queried again).
D. Overwrite updates: This is a special case in non-repeatable reads, where one transaction overwrites the updated data that another transaction has committed (that is, the a transaction updates the data, and then the B transaction updates the data, and a transaction query discovers that its updated data has changed).

E. Virtual read ( Phantom read ): One transaction reads the newly inserted data that has been committed by another transaction (A and B transactions are executed concurrently, a transaction query data, b transactions insert or delete data, a transaction is queried again to find that the result set has no previous data or the previous data disappears).
The database system provides four levels of transaction isolation for users to choose from:
A.serializable (serialization): One transaction does not completely see the updates made to the database by other transactions during execution (transaction execution does not allow other transactions to execute concurrently.) Transaction serialization is performed, and transactions can be executed one after the other, not concurrently. ）。
B.repeatable Read (Repeatable Read): One transaction can see newly inserted records that other transactions have committed during execution, but cannot see updates to existing records from other transactions.
C.read commited (Read committed data): One transaction can see the newly inserted records that other transactions have committed during execution, and can see updates to existing records that have been committed by other transactions.
D.read UNCOMMITTED (READ UNCOMMITTED data): One transaction can see the newly inserted records not submitted by other transactions during execution, and can see updates to existing records that are not committed by other transactions.

	Missing updates	Dirty Read	Non-REPEATABLE READ	Overwrite updates	Phantom reading
Non-committed read	Y	Y	Y	Y	Y
Read Committed	N	N	Y	Y	Y
REPEATABLE READ	N	N	N	N	Y
Serialization	N	N	N	N	N

Isolation level

The database system has four isolation levels (the default level for most databases is read commited). Careful analysis of what isolation levels are used for the database, because

1. Maintaining one of the highest isolation levels prevents errors in data, but results in a loss of parallelism and an increased likelihood of deadlocks.

2. However, reducing the isolation level can cause some hard-to-find bugs.

SERIALIZABLE (serialized)

Add range locks (such as table locks, page locks, and so on, I have no deep research on range lock) until transaction a ends. This prevents other transaction B operations such as insert,update in this range.

Magic Reading, dirty reading, non-repeatable reading and other problems will not occur.

REPEATABLE READ (Repeatable Read)

For a read-out record, add a shared lock until transaction a ends. Other transaction B attempts to modify this record will wait until transaction a ends.

Possible issues: Phantom reads may occur when a scope query is executed.

Read COMMITTED (submit reading)

A shared lock is added to the record when the data is read in transaction A, but the read end is released immediately. Other transaction B attempts to modify this record will wait until the end of the read process in a, rather than the end of the entire transaction a. Therefore, the read results for the same record may be different at different stages of transaction A.

Problems that may occur: non-repeatable reading.

READ UNCOMMITTED (UNCOMMITTED)

No shared locks are added. So other transaction B can modify the same record during transaction A's reading of the record, which may cause the data read by a to be a corrupted or incomplete data.

In addition, data modified in transaction B (uncommitted) can be read in transaction A. For example, transaction B has modified the R record but not submitted it. At this point, read the R record in transaction A and read the data modified by B.

Problems that may occur: dirty reads.

Problem

We see that when different isolation levels are executed, a variety of different problems can occur. They are summarized below and illustrated in an example.

Phantom reading

Phantom reads occur when two identical queries are executed, and the result set returned by the second query is not the same as the first query.

What happens: There is no range lock.

Example:

Transaction 1	Transaction 2
SELECT * Fromuserswhereage Between10and30
	Insertintousers VALUES (3, ' Bob ', 27); COMMIT;
select* fromusers whereage between10and30;

How to avoid: Implementing a serialization isolation mode can occur in any low-level isolation.

Non-REPEATABLE READ

In a lock-based parallel control approach, non-repeatable read problems occur if you do not add read locks when you execute a SELECT.

In a multi-version parallel control mechanism, a non-repeatable read problem occurs when a transaction that encounters a commit conflict needs to be rolled back but is released.

Transaction 1	Transaction 2
select* fromusers Whereid = 1;
	Updateusers setage = 21WHEREid = 1; COMMIT; /* in multiversion concurrency*/ control, or lock-based READ COMMITTED *
select* fromusers Whereid = 1;
	COMMIT; /* lock-based repeatable READ */

In the above example, transaction 2 commits successfully, and the changes it makes are already visible. However, transaction 1 has read a different value. In the serialization and repeatable read isolation level, the database management system returns the old value, which is the value before the transaction 2 was modified. Under the commit read and uncommitted read isolation levels, the updated value may be returned, which is "non-repeatable read".

There are two strategies to prevent this problem from happening:

1. Defer execution of transaction 2 until transaction 1 commits or rolls back. This policy is applied when locks are used. (pessimistic locking mechanism, such as adding an exclusive lock to a data row with select for update)

2. In multi-version parallel control, transaction 2 can be submitted first. With transaction 1, the data on the older version continues to execute. When transaction 1 finally attempts to commit, the database verifies that its results are the same as for transaction 1 and Transaction 2 order execution. If it is, then transaction 1 commits successfully. If not, transaction 1 will be rolled back. (Optimistic locking mechanism)

Dirty Read

Dirty reads occur when a transaction a reads a data that has been modified by another transaction B, but has not yet committed. If B is rolled back, transaction a reads invalid data. This is similar to non-repeatable reads, but the second transaction does not require a commit.

Transaction 1	Transaction 2
select* fromusers Whereid = 1;
	Updateusers setage = 21WHEREid = 1
Selectfromusers Whereid = 1;
	COMMIT; /* lock-based DIRTY READ */

Isolation level of a transaction with lock request and release