Database transaction isolation level

Database transaction isolation level

The main problems to be solved by database software are the conflict between "data consistency" and "Access concurrency". In theory, we have summarized several uncertainties that need to be solved to ensure data consistency, based on this, several isolation levels of database transactions are proposed.

First, let's take a look at several uncertainties that may occur in the solution to data consistency.

Dirty read:

Dirty reading, also known as reading invalid data, refers to modifying a value in transaction T1 during database access. Before committing, transaction T2 can read the modification of transaction T1. Because transaction T1 has not been committed, the transaction data of transaction T1 read by T2. that is, the transaction data is dirty data.

Repeatable read:

Read the same data multiple times in a transaction. When the transaction is not completed, another transaction also accesses the same data. So, between the two reads of the first transaction. Because of the modification of the second transaction, the data read by the first transaction may be different, so that the data read twice in a transaction is different, so it is called non-repeated read, that is, the original reads cannot be repeated.

Setting the transaction isolation level to repeatable read can prevent repeated reads. in oracle, we can achieve the same effect through select for update.

Phantom read:

Case 1:

The first transaction modifies the data in a table. For example, this modification involves "all data rows" in the table ". At the same time, the second transaction also modifies the data in this table. This modification inserts a "new row of Data" into the table ". In the future, the user who operates the first transaction will find that there are still unmodified data rows in the table, just like an illusion.

Case 2:

The sum of the total number of records in the T1 (5) and t2 (10) query tables of transaction t1 (15). After transaction T1 queries table t1 and before query of T2, transaction T2 inserts two records into t1 and t2 respectively, and submits the records. Then transaction T1 queries the total number of records in T2. the result t1 = 5 t2 = 12, the total number of records 17, the actual total number of records should be 15 or 19, not 17, just like an illusion

To solve phantom read, you need to set the isolation level of the transaction to serialization, that is, serialization.

To avoid the preceding situations, four transaction isolation levels are defined in the standard SQL specification.

Read Uncommited

To avoid dirty reads, but do not allow loss of updates. If a transaction has started to write data, the write operation is not allowed for another transaction, but other transactions are allowed to read this row of data.

Read Commited

Repeated reads are allowed, but dirty reads are not allowed.

Repeatable read prohibits repeated reads and dirty reads. However, phantom data Serializable may sometimes provide strict transaction isolation. It requires the transaction to be serialized. The transaction can only be executed one by one, but cannot be executed concurrently.

√: Possible occurrence ×: No

	Dirty read	Non-repeated read	Phantom read
Read uncommitted	√	√	√
Read committed	×	√	√
Repeatable read	×	×	√
Serializable	×	×	×

 

In oracle databases, there are three isolation levels: read commited (default), Serializable, and readonly. Different database software has different implementation methods for isolation levels, and the Implementation degree is different. Specific analysis is required.