In-depth analysis of MySQL transaction isolation and its impact on performance _ MySQL

This article mainly introduces MySQL transaction isolation and its impact on performance. it has some reference significance in MySQL optimization, you can refer to the SQL standard to define four types of isolation levels, including specific rules to limit which changes inside and outside the transaction are visible and which are invisible. Low-level isolation generally supports higher concurrent processing and 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 no better than other levels. Read UNCOMMITTED data, also known as Dirty Read ).
Read Committed (Read submitted content)
This is the default isolation level for most database systems (but not for MySQL ). It satisfies the simple definition of isolation: a transaction can only see changes made by committed transactions. This isolation level also supports the so-called Nonrepeatable Read, because other instances of the same transaction may have a new commit during the processing of this instance, so the same select may return different results.
Repeatable Read (repeable)
This is the default transaction isolation level of MySQL. it ensures that multiple instances of the same transaction will see the same data rows when reading data concurrently. However, theoretically, this will lead to another tricky problem: Phantom Read ). In short, Phantom read refers to when a user reads data in a certain range, another transaction inserts a new row in this range. when the user reads data in this range, there will be a new Phantom line. The InnoDB and Falcon storage engines solve this problem through the multi-version Concurrency Control (MVCC, Multiversion Concurrency Control) mechanism.
Serializable (Serializable)
This is the highest isolation level. it forces transaction sorting to make it impossible to conflict with each other, thus solving the Phantom read problem. In short, it adds a shared lock to each read data row. At this level, there may be a lot of timeout and lock competition.
The four isolation levels adopt different lock types. if the same data is read, problems may occur. For example:

• Dirty Read: a transaction has updated the data, and another transaction has Read the same data at this time. for some reason, the previous RollBack operation is performed, the data read by the other transaction is incorrect.
• Non-repeatable read: the data in the two queries of a transaction is inconsistent. This may be because the original data updated by a transaction is inserted in the two queries.
• Phantom Read: the number of data records in two queries of a transaction is inconsistent. for example, a transaction queries several rows of data, another transaction inserts several new columns of data at this time. in the subsequent query, the previous transaction will find that several columns of data are not at the beginning.

In MySQL, these four isolation levels are implemented, which may cause the following problems:

Effect of MySQL transaction isolation level on its performance
MySQL works under Level 3 by default. We know that transaction isolation aims to avoid inconsistent data import because concurrent operations affect each other. Therefore, transaction isolation is introduced to ensure data consistency. The above four levels of data consistency protection are gradually improved. Level 4 has the best effect on transaction isolation, but has the worst performance. it is generally not used in the production environment.
The following examples are used to check the impact on MySQL performance at different levels. My experiment environment is: Redhat5.8 + MySQL5.5
First, we enable two sessions:
1. verification level 1 features
The operations we perform on session A are as follows:

The operation on session B is the same as session A, which is not attached here.
Next, we will use a series of experiments to observe what READ-UNCOMMITTED is, what features it has, and what impact it has on our operations. First, we can see the initial data in the table as follows:

Next we will change one of the records on sessionA. The change result is as follows:

Note: we have enabled transactions, but we have not performed the commit operation here.

Next, we will perform the select query on the modified table in sessionB. the query results are as follows:

We can clearly see that although we have not performed commit on the result of session A, the result has indeed changed. Therefore, operations that are not submitted at this level may affect data consistency. Therefore, if we roll back the above operations on session A at this time, we will find that the results on session B return to the original results, this leads to data inconsistency. this is also called the Phantom reading phenomenon of data. it looks strange. Therefore, in some scenarios, we should avoid this phenomenon. However, this level is not useless. for example, this mode is suitable when we have a large amount of data to be written and few read operations.
We can see that after session A is rolled back, the data in session B becomes the original, which is also called Phantom read:

2. verify the read committed feature
First, change the isolation levels of session A and session B to READ-COMMITTED, and enable all transactions as follows:

Next, we can view the initial status information of the tutors table:

Then we continue to update the data, and there is still no commit after the update. We can see that in sessionA, the result has changed:

Now we can check in session B and find that the results remain unchanged:

However, if we perform the commit operation in session A at this time, we will find that sessionB queries will change at this time, which also results in inconsistent data, it is also a phantom read of data:

3. repeatable data
Data repeatable, also known as REPEATABLE-READ, which is the default transaction isolation level adopted by MySQL, has its advantages, but still does not fundamentally solve the problem of data consistency. First, let's test how MySQL works at this level, what features are there, and how we operate it.
We first set the REPEATABLE-READ environment, the specific operation method is as follows:

Then we can view the initial data. The result is as follows:

We modify the data in session A and perform the commit operation. the modified result is as follows:

Then we can check in session B and find that the result remains unchanged:

This is the repeatable feature. as long as this session is not submitted, the results remain unchanged despite the modifications made by the other party. only the commit operation is performed in session B, the modification takes effect in sessionB.

4. seriabliable
This level is the best security for transaction isolation, but it also has the worst performance, because all operations at this level are sequential. One operation is not submitted, and the other affected operation is blocked.
To verify this effect, we first set the environment as follows:

Modify any of its fields in session A without performing the commit operation. At this time, the query operation in sessionB is always blocked:

This sets up the effect of serialize isolation, which is also why the concurrency capability of serialize isolation is poor.