Four isolation levels in the SQL standard

Read uncommited (UNCOMMITTED)

At the Rerad uncommited level, modifications in a transaction are visible to other transactions, even if they are not committed. The transaction can read uncommitted data, which also becomes dirty read (Dirty read). This level can cause a lot of problems, in terms of performance, read uncommited will not be much better than the other levels, but there is a lack of other levels of benefits, unless there is a very necessary reason, in practical applications generally rarely use read uncommited.

Read commited (submit reading)

The default isolation level for most database systems is read commited (but MySQL is not). READ commited satisfies the simple definition of the previously mentioned isolation: When a transaction starts, it can only see the changes made by the transactions that have been committed. In other words, any changes you make to a transaction from the beginning to the commit are not visible to other transactions. This level is sometimes called non-repeatable (nonerepeatable read), because two times the same query is executed, you may get a different result.

REPEATABLE READ (Repeatable Read)

REPEATABLE READ (Repeatable Read) solves the problem of dirty reads. This level guarantees that the result of reading the same record multiple times in the same transaction is consistent. However, in theory, it is not possible to read the isolation level repeatedly to resolve another phantom read (Phantomread) problem. The so-called Phantom reads, when a transaction reads a range of records, another transaction inserts a new record within that range, and when the previous transaction reads the record for that range again, a magic line (Phantom row) is generated. The InnoDB and XTRADB storage engines solve the Phantom read problem through multiple version concurrency control (MVCC, Multivesion Concurrency control).

Repeatable Read is the default transaction isolation level for MySQL.

SERIALIZABLE (Serializable)

The serializable is the highest isolation level. It avoids the above-mentioned phantom-reading problem by forcing the transaction serial. Simply put, serializable will add a lock on every line of data read, so it can cause a lot of timeouts and lock expropriation problems. This isolation level is rarely used in real-world applications, and can only be considered if there is a need to ensure that data is consistent and acceptable without concurrency.

Isolation level dirty read possibility non-repeatable read possibility Magic read possibility lock read

READ uncommited Yes Yes Yes NO

READ commited No Yes yes No

Repeatable READ No no YES no

SERIALIZABLE No no no YES

　　

Four isolation levels in the SQL standard