What are the isolation levels of mysql?

There are 4 mysql isolation levels, namely: 1. Read Uncommitted (read uncommitted content); 2. Read Committed (read submitted content); 3. Repeatable Read (rereadable); 4. Serializable.

(Recommended tutorial: mysql tutorial)

Four isolation levels of Mysql

The SQL standard defines four types of isolation levels, including some specific rules to limit which changes inside and outside the transaction are visible and which are invisible. Lower isolation levels generally support higher concurrency and have lower system overhead.

1. Read Uncommitted (read uncommitted content)

At 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.

2. Read Committed (read submission content)

This is the default isolation level of 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.

3. Repeatable Read

This is the default transaction isolation level of MySQL. It ensures that when multiple instances of the same transaction read data concurrently, You will see the same rows of data.

But in theory, this will lead to another thorny problem: 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.

4. Serializable (serializable)

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

Problem

These four isolation levels are implemented using different lock types. If the same data is read, problems may easily occur. For example:

1. Dirty Read:

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

2. Non-repeatable read:

The data is inconsistent between two queries of a transaction. This may be because a transaction update was inserted between the two query processes. original data.

3. Phantom Read:

The number of data items in two queries of a transaction is inconsistent. For example, one transaction queries several rows of data, while another query However, the transaction inserted several new columns of data at this time. In the subsequent query of the previous transaction, you will find that there are several columns of data that it did not have before.

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