In-depth understanding of the 4 types of isolation levels in 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.

Read Uncommitted (read uncommitted content)

## In this isolation level, all transactions can be seen 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

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, which ensures that multiple instances of the same transaction can read data concurrently. , you will see the same data rows. 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 problem by forcing transactions to be ordered so that they cannot conflict with each other. Phantom reading problem. In short, it adds a shared lock on each data row read. At this level, a lot of timeouts and lock contention can result.

                 

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

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

               

Non-repeatable read (Non-repeatable read): The data is inconsistent between two queries of a transaction. This may be due to the original data updated by a transaction inserted between the two query processes. The data.

## 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 transaction queries At this time, several new columns of data are inserted. In the subsequent query of the previous transaction, you will find 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:

Next, we will use the MySQL client program to test several isolation levels. The test database is test and the table is tx; table structure:

The two command line clients are A and B; constantly change the isolation level of A and modify the data on the B side.

(1) Set the isolation level of A to read uncommitted

In B Before data is updated:

Client A:

B updated data:

Client B:

##Client A:

After the above experiment, it can be concluded that transaction B updated a record, but it was not submitted. At this time, transaction A can query the uncommitted record . Cause dirty reads. Read uncommitted is the lowest isolation level.

(2) Set the transaction isolation level of client A to read committed

Before B has updated the data:

##Client A:

B update data:

Client B:

# #Client A:

## After the above experiment, it can be concluded that the committed read isolation level solves the problem of dirty reads. However, the problem of non-repeatable reading arises, that is, the data queried by transaction A between the two queries is inconsistent because transaction B updated a piece of data between the two queries. Committed read only allows reading of committed records, but does not require repeatable reads.

(3),

Set the isolation level of A to repeatable read(repeatable read)

In B Before data is updated:

Client A:

B update data:

Client B:

##Client A:

B Insert data:

Client B:

Client A:

## Based on the above experiment It can be concluded that the repeatable read isolation level only allows reading of committed records, and during the period when a transaction reads a record twice, other transactions update the record. But the transaction is not required to be serializable with other transactions. For example, when a transaction can find records updated by a committed transaction, phantom read problems may occur (note that this is possible because the database implements isolation levels differently). In experiments like the above, there is no problem of phantom reading of data.

(

四)、

Set the isolation level of A to Serializable

A-side opens the transaction, and B-side inserts a record

Transaction A-side:

Transaction B-side:

## Because the isolation level of transaction A is set to serializable at this time, after starting the transaction, it is not committed, so transaction B can only wait.

##Transaction A submits the transaction:

Transaction A side

Transaction B-side

##        

Serializable completely locks the field. If a transaction queries the same data, it must wait until the previous transaction completes and unlocks it. is a complete isolation level, which will lock the corresponding data table, so there will be efficiency issues.

id	##                              int
##num ## int

The above is the detailed content of In-depth understanding of the 4 types of isolation levels in mysql. For more information, please follow other related articles on the PHP Chinese website!