Deep analysis of Mysql's transaction isolation and its impact on performance _mysql

The SQL standard defines 4 classes of isolation, including specific rules that define which changes are visible inside and outside the transaction and which are not. The low-level isolation level generally supports higher concurrency processing and has lower system overhead.
Read uncommitted (reading uncommitted content)
At this isolation level, all transactions can see the execution results of other uncommitted transactions. This isolation level is rarely used for practical purposes because it does not perform much better than other levels. Reading uncommitted data is also called dirty Read (Dirty read).
Read committed (reading submissions)
This is the default isolation level for most database systems (but not MySQL default). It satisfies the simple definition of isolation: A transaction can only see changes that have been committed by the firm. This isolation level also supports so-called non-repeatable reads (nonrepeatable read) because other instances of the same transaction may have a new commit during the processing of the instance, so the same select may return different results.
repeatable Read (reread)
This is the default transaction isolation level for MySQL, which ensures that multiple instances of the same transaction see the same data row when they read the data concurrently. In theory, however, this can lead to another tricky problem: Phantom Reading (Phantom read). In short, phantom reading means that when a user reads a range of data rows, another transaction inserts a new row within that range, and a new phantom row is found when the user reads the range of data rows. The InnoDB and Falcon storage engines address this problem through the multiple versioning concurrency control (mvcc,multiversion concurrency controls) mechanism.
Serializable (Serializable)
This is the highest isolation level, which solves the problem of phantom reading by forcing transactions to be sorted so that they cannot conflict with each other. In short, it adds a shared lock to each read row of data. At this level, it can lead to a large number of timeout phenomena and lock competition.
These four isolation levels are implemented with different lock types, which can be easily problematic if the same data is read. For example:

• Dirty Read (drity Read): A transaction has updated a data, another transaction at this time read the same data, for some reason, the previous rollback the operation, then the data read by the latter will be incorrect.
• Non-repeatable reads: Data is inconsistent among two queries in a transaction, which may be the original data in which a transaction update was inserted in the middle of a two-query process non-repeatable.
• Phantom read: The number of data pens is inconsistent in a two-time query of a transaction, for example, a transaction queries several columns of data, while another transaction inserts new columns of data at this time, and the previous transaction finds several columns of data that it previously did not have in the next query.

In MySQL, these four isolation levels are implemented, which can cause problems as follows:

The effect of MySQL transaction isolation level on its performance
MySQL defaults to work at level three. We know that transaction isolation is to avoid the inconsistency between concurrent operations and data. So in order to ensure the consistency of data, the function of transaction isolation is introduced. The above four levels of data consistency protection is gradually increased. Level 4 is best at isolating transactions, but with the least performance, it is generally no longer used in production environments.
The following examples are used to verify the impact of MySQL performance at different levels. My experimental environment is: redhat5.8+mysql5.5
First we enable two sessions here:
1, verify the characteristics of level one
The actions we do on session A are:

The action on session B is the same as sessions A, where no screenshots are attached.
Then we'll look at a series of experiments to see what read-uncommitted is, what it really is and what it does to our operations. First, we can see the initial data in the table as follows:

Next we change one of the records on the Sessiona, and the results are as follows:

Note: We have transactions enabled above, but we are not doing a commit here.

Next we make a select query for the table we just changed in sessionb, and the query results are as follows:

We can see clearly that although we did not commit to session a, the result has indeed changed. So at this level, uncommitted operations can have an impact on the consistency of the data. So, if we roll back the above operation on session A at this time, we will find that the results on session B are back to the original results, which leads to the inconsistency of the data, which is also called the reading phenomenon of the data, which seems to be a very strange thing. So in some scenarios, we should avoid the emergence of this phenomenon. But this level is not without its use, for example, when we have a large number of data to write, and the reading operation is very small, it is suitable for this mode.
When session a rolls back, the data in session B becomes the original, also known as Phantom reads:

2, verification level Read Committed characteristics
first, the isolation level of session A and session B is changed to read-committed, and all the transactions are opened, as follows:

Next we look at the initial state information for the tutors table:

Then we are still updating the data and there is no commit after the update. We can see that in Sessiona, the results have changed:

At this point we looked at session B and found that the results remained unchanged:

However, if we commit in session a at this time, we will find that the query changes sessionb at this time, which also results in inconsistencies in the data and also in the Phantom reading of the data:

3, the data can be reread
the data can be reread, also known as Repeatable-read, which is the default transaction isolation level of MySQL, has its advantages, but still does not fundamentally solve the problem of data consistency. First of all, let's test how MySQL works at this level and what the features are, and how we do it.
We set up the Repeatable-read environment first, the concrete operation method is as follows:

Then we look at its initial data and the results are as follows:

We modify the data in session a and commit it, and the results are as follows:

And then we looked at session B and found that the results were still unchanged:

This is the feature of the accent, as long as this session is not submitted, although the other side of the modification, but the result is still the same, only in sessions B is also a commit, the changes will be in the sessionb in effect.

4, Seriabliable
This level is the best for transaction isolation security, but it is also the least performing because all operations at this level are sequential. One operation is not committed, and another affected operation is blocked.
To verify this effect, we set up the environment first, in session A and session B, as follows:

Any of its fields are modified in session A and no commit is made. The query operation in SESSIONB is now blocked:

This sets the effect of serialization isolation and is why serialization is poor in concurrency.