MySQL four isolation levels

What is a transaction

A transaction is a series of strict operations in an application. All operations must be completed successfully, otherwise all changes made in each operation will be undone. That is, the transaction is atomic, and a series of operations in a transaction are either all successful or none of them are performed.
There are two types of transaction endings. When all steps in the transaction are successfully executed, the transaction commits. If one of the steps fails, a rollback operation will occur, and all operations from before the undo to the beginning of the transaction are undone.

ACID of the transaction

Transactions have four characteristics: Atomicity, Consistency, Isolation and
Durability. These four features are referred to as ACID features for short.
1. Atomicity : A transaction is the logical unit of work of the database, and all operations contained in the transaction are either done or not done.
2. Consistency : The result of transaction execution must be to change the database from one consistent state to another consistent state. Therefore, when the database only contains the results of successful transaction submission, the database is said to be in a consistent state.
If a failure occurs during the operation of the database system, some transactions are forced to be interrupted before they are completed. Some of the modifications made to the database by these unfinished transactions have been written to the physical database. At this time, the database is in an incorrect state, or it is
Inconsistent state.
3. Isolation : The execution of a transaction cannot be interfered by other transactions. That is to say, the internal operations and data used by a transaction are isolated from other concurrent transactions, and each transaction executed concurrently cannot interfere with each other.
4. Persistence : Also known as permanent, it means that once a transaction is committed, its changes to the data in the database should be permanent. The following other operations or failures should not have any impact on the results of its execution.

Four isolation levels of Mysql

The SQL standard defines 4 types of isolation levels, including some specific rules to limit which changes inside and outside the transaction are visible and which are invisible.
Low-level isolation levels generally support higher concurrent processing and have lower system overhead.

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.

Read Committed (read submitted content)

This is the default isolation level for most database systems (but not the MySQL default). It satisfies the simple definition of isolation: a transaction can only see the changes made by the committed transaction.
This isolation level also supports the so-called non-repeatable read (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 MySQL's default transaction isolation level, which ensures that multiple instances of the same transaction will see the same data rows when reading data concurrently. But in theory, this leads to another tricky problem:
Phantom Read.
Simply put, phantom reading means that when the user reads a range of data rows, another transaction inserts a new row in the range. When the user reads the data rows in the range again, they will find new " Phantom"
line. The InnoDB and Falcon storage engines
solve this problem through the Multiversion Concurrency Control (MVCC ) mechanism.

Serializable

This is the highest isolation level. It solves the problem of phantom reading by forcing transaction ordering to make it impossible to conflict with each other. In short, it adds a shared lock on each row of data read. At this level, it may lead to a large number of timeouts and lock contention.
These four isolation levels are implemented using different lock types. If the same data is read, problems are prone to occur. E.g:
  • Dirty Read (DrityRead): A transaction has updated a piece of data, and another transaction reads the same piece of data at this time. For some reason, the previous RollBack operation will cause the data read by the next transaction Is incorrect.
  • Non-repeatable read: The data is inconsistent in the two queries of a transaction. This may be the original data updated by a transaction inserted between the two queries.
  • PhantomRead: The number of data in the two queries of a transaction is inconsistent. For example, one transaction queries several rows of data, while another transaction inserts new columns of data at this time. The previous In the next query of the transaction, there are several columns of data that are not queried. If you insert the data at this time and the data inserted by another transaction, an error will be reported.
In MySQL, these four isolation levels are implemented, which may cause problems as follows:
Insert picture description here

Test the isolation level of Mysql

Next, we will use the MySQL client program to test these isolation levels respectively.

The test database is demo and the table is test; the table structure:
Insert picture description here


The two command line clients are A and B respectively; the isolation level of A is constantly changed, and the data is modified on the B side.

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

Insert picture description here
A: Start the transaction, the data is in the initial state at this time
Insert picture description here
B: Start the transaction, update the data, but do not submit
Insert picture description here
A: Read the data again and find that the data has been modified. This is the so-called "dirty read"
Insert picture description here
B: Roll back the transaction
Insert picture description here
A: Read the data again and find that the data has changed back to the initial state
Insert picture description here
After the above experiment, it can be concluded that transaction B updated a record but did not submit it. At this time, transaction A can query the uncommitted record. Cause dirty read phenomenon. Uncommitted read is the lowest isolation level.

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

A: Start the transaction, the data is in the initial state at this time
Insert picture description here
B: Start the transaction, update the data, but do not submit
Insert picture description here
A: Read the data again and find that the data has not been modified
Insert picture description here
B: Commit the transaction
Insert picture description here
A: Read the data again and find that the data has changed, indicating that the modification submitted by B has been read by A in the transaction. This is the so-called "non-repeatable read"
Insert picture description here
After the above experiment, it can be concluded that the submitted read isolation level solves the problem of dirty reads, but the problem of non-repeatable reads occurs, that is, the data of transaction A in the two queries is inconsistent, because transaction B between the two queries A piece of data was updated. Read submitted is only allowed to read the submitted records, but it is not required to read repeatedly.

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

Insert picture description here
A: Start the transaction, the data is in the initial state at this time
Insert picture description here
B: Start the transaction, update the data, but do not submit
Insert picture description here
A: Read the data again and find that the data has not been modified
Insert picture description here
B: Commit the transaction
Insert picture description here
A: Read the data again and find that the data is still unchanged, which means that you can read it again this time.
Insert picture description here
B: Insert a new piece of data and submit
Insert picture description here
A: Read the data again and find that the data has not changed. Although it can be read repeatedly, but it is found that the data is not the latest data. This is the so-called "phantom reading"
Insert picture description here
A: Submit this transaction, read the data again, and find that the reading is normal
Insert picture description here
From the above experiments, it can be concluded that the repeatable read isolation level only allows reading of committed records, and during the period when a record is read twice in a transaction, other transaction departments update the record. But the transaction does not require serialization with other transactions. For example, when a transaction can find a record updated by a committed transaction, but may have a phantom reading problem (note that it is possible, because the database implementation of the isolation level is different). Like the above experiment, there is no data phantom reading problem.

Set the isolation level of A to Serializable

Insert picture description here
A: Start the transaction, the data is in the initial state at this time
Insert picture description here
B: It is found that B has entered the waiting state at this time, because A's transaction has not yet been submitted and can only wait (at this time, B may have a waiting timeout)
Insert picture description here
A: Commit the transaction
Insert picture description here
B: Found that the insertion was successful
Insert picture description here
Serializable completely locks the field. If a transaction queries the same data, it must wait until the previous transaction is completed and unlocked. Is a complete isolation level, will lock the corresponding data table, so there will be efficiency problems