Isolation level for database transactions

The main problem that the database software needs to solve is the contradiction between "data consistency" and "Access Concurrency", in theory, we summarize some kinds of uncertainties that need to be solved to ensure the consistency of data, and put forward several isolation levels of database transaction based on this.

First, take a look at some of the uncertainties that can occur in the process of resolving data consistency.

Dirty read:

Dirty read also known as invalid data readout, refers to the process of database access, transaction T1 a value has been modified, before committing, the transaction T2 can read to the transaction T1 modification, because the T1 transaction has not been committed, so T2 read T1 transaction data is invalid, that is, the data is dirty data.

Non-repeatable READ:

in aTransactions, read the same data multiple times. In thisTransactionsthe same data is also accessed by another transaction when it is not finished. Well, in the firstTransactionsbetween two reads of data. As a result of the secondTransactionschanges, the first transaction may read the data differently, so that the data that is read two times within a transaction is not the same, so it is called non-repeatable read, that is, the original read is not repeatable

By setting the isolation level of a transaction to repeatable read to prevent non-repeatable reads, we can achieve the same effect with select for Update in Oracle.

Phantom read:

Scenario 1:

The first transaction modifies the data in a table, such as "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.

Scenario 2:

The sum of the total number of records in the Transaction T1 query table T1 (5) and T2 (10) (15), after the transaction T1 queries the table T1 and before the query T2, the transaction T2 inserts two records into T1 and T2 respectively, commits, and then the transaction T1 the total number of records in the T2 and results t1=5 T2 =12, the total number of records 17, the actual total record should be 15 or 19, there is no 17, as if there was an illusion

Resolving Phantom reads requires that the isolation level of the transaction be set to serialization, which is serialization

To avoid these scenarios, 4 transaction isolation levels are defined in the standard SQL specification.

Read uncommited

to avoid dirty reads are allowed, but updates are not allowed to be lost. If a transaction has already started writing data, another transaction does not allow simultaneous writes, but allows other transactions to read this row of data

Read commited

Allow non-repeatable read , but dirty reads are not allowed.

REPEATABLE READprohibitednon-repeatable readand dirty reads, but sometimes phantom data may appearSerializableprovides strict transaction isolation. It requires a transactionSerialization ofexecution, a transaction can be executed one after the other, but not concurrently.

√: May appear x: does not appear

	Dirty Read	Non-REPEATABLE READ	Phantom reading
Read UNCOMMITTED	√	√	√
Read committed	X	√	√
REPEATABLE READ	X	X	√
Serializable	X	X	X

In the Oracle database, there is a read commited (default), serializable,readonly three isolation levels. Different database software implementation of the isolation level is different, the degree of implementation is different, the need for specific analysis of the situation.