What are the four levels of database isolation?

The four levels of database isolation are: 1. Read uncommitted content; 2. Read submitted content; 3. Rereadable; 4. Serializable. Isolation levels are implemented using different lock types. If the same data is read, problems such as dirty reads and phantom reads may occur.

The operating environment of this article: Windows 7 system, Dell G3 computer, mysql8.

The four levels of database isolation are:

Read Uncommitted (read uncommitted content)

In this Isolation level, all transactions can see the execution results of other uncommitted transactions. This isolation level is rarely used in practical applications because its performance is not much better than other levels. Reading uncommitted data is also called dirty read.

Read Committed (read commit content)

This is the default isolation level for most database systems (but not the MySQL default). It meets the simple definition of isolation: a transaction can only see changes made by committed transactions. This isolation level also supports so-called nonrepeatable read, because other instances of the same transaction may have new commits during the processing of the instance, so the same select may return different results.

Repeatable Read

This is the default transaction isolation level of MySQL. It ensures that multiple instances of the same transaction will read data concurrently. to the same data row. However, in theory, this will lead to another thorny problem: phantom read (Phantom Read). Simply put, phantom reading means that when the user reads a certain range of data rows, another transaction inserts a new row in the range. When the user reads the data rows in the range again, he will find that there are new " Phantom” OK. InnoDB and Falcon storage engines solve this problem through the multiversion concurrency control (MVCC, Multiversion Concurrency Control) mechanism.

Serializable

This is the highest isolation level. It solves the phantom read problem by forcing transactions to be ordered so that they cannot conflict with each other. . In short, it adds a shared lock on each data row read. At this level, a lot of timeouts and lock contention can result.

Problems caused by isolation levels

These four isolation levels are implemented using different lock types. If the same data is read, it is easy to happen. question. For example:

Dirty Read:

A transaction has updated a piece of data, and another transaction has read the same piece of data at this time, because For some reason, if the previous RollBack operation is performed, the data read by the subsequent transaction will be incorrect.

Non-repeatable read:

The data is inconsistent between two queries of a transaction. This may be due to insertion between the two queries. A transaction updates the original data.

Phantom Read:

The number of data items in two queries of a transaction is inconsistent. For example, one transaction queries several columns (Row) of data. , but another transaction inserted several new columns of data at this time. In the subsequent query of the previous transaction, it will be found that there are several columns of data that it did not have before.

In MySQL, these four isolation levels are implemented, and the problems that may occur are as follows:

[Recommended course: MySQL Tutorial]

The above is the detailed content of What are the four levels of database isolation?. For more information, please follow other related articles on the PHP Chinese website!