Detailed explanation of MySQL database isolation level and MVCC

MySQL is one of the most commonly used databases in our daily production and learning. Today, let’s talk about the isolation levels in MySQL (or other similar databases) and the multi-version concurrency control (MVCC) used to improve efficiency.

1. Isolation Level

First we need to mention a concept: transaction. What is a transaction? A transaction is a collection of a series of operation statements that complete a basic operation. For example, if I want to transfer 200 yuan from account A to account B, I might do the following:
a. Verify that the balance in account A is greater than 200 yuan.
b. Reduce the balance in account A by RMB 200.
c. Add RMB 200 to the balance in account B.
We will combine the above three operations abc into one transaction.
At this point, we will notice that a transaction we are talking about may be composed of multiple statements, and transactions are atomic, that is, the execution of a transaction cannot be interrupted. This brings up a question: if another statement is inserted into the execution of these three steps, will it affect the result, because the atomicity of the transaction is destroyed at this time. This kind of insertion is very common in a concurrent environment. Therefore, we (or the database engine) need to "protect" a transaction during its execution, that is, to ensure that statements from other external transactions cannot be arbitrarily inserted into the transaction statements being executed, to ensure the normal execution of the transaction. At this time, it is easy for us to think of the "locking" method. This is actually a very general statement, because although locking can ensure the normal execution of transactions, it will bring a lot of additional overhead. Therefore, choosing the right locking method at the right time will have a great impact on the search efficiency. The strictness of the "lock" determines the different isolation levels.

READ UNCOMMITED

In this isolation level, data reading is not affected in any way. That is, you can even read data that is being modified by other transactions, and you can read and modify it whenever you want. This certainly has little overhead, but it can cause many problems, such as "dirty reads". That is, data that is being modified but not yet submitted is read, which will cause data reading errors. In terms of performance, READ UNCOMMITED is not much better than other levels, but it brings a lot of troublesome problems, so it is rarely used in practice.

READ COMMITED (read committed/non-repeatable read)

This level adds some provisions based on READ UNCOMMITED and is the default isolation level for some databases. The difference between it and READ UNCOMMITED is that it stipulates that the data read during reading can only be the committed data. For example, the value of data a after the last submission is 1. At this time, a thread comes in to modify a and changes a to 2, but the transaction (COMMIT) is not committed at this time. In this case, the value of a read at the READ UNCOMMITED level is the current value 2, but the value read at the READ COMMITED level is still the value after the last submission, that is, a is 1. The value of a must be read after the modification thread changes the value of a to 2 and the transaction is committed for it to be 2. The problem brought about by this level is non-repeatable reading. That is, the value of a read last time was 1, but as the modification thread committed the transaction, the value of a changed to 2, and the value read at this time was 2, that is, the values ​​obtained by executing the same read operation twice are different.
The difference between non-repeatable read and dirty read is that dirty read is a transaction reading data during the execution of another unfinished transaction, while non-repeatable read is when another transaction commits and modifies the data being read by the current transaction during the execution of one transaction.

REPEATED READ

REPEATED READ adds some restrictive rules based on READ COMMITED, and it is also the default isolation level of the MySQL database. Simply put, other transactions are prohibited from modifying the corresponding data during the execution of a transaction. This ensures that the data queried during the execution of a transaction is consistent, solving the problems of dirty reads and non-repeatable reads. However, it brings a new problem, namely "phantom reads".
"Phantom read" means that although the modification of corresponding data is prohibited during the execution of a transaction, other transactions can still insert data. At this time, the first transaction will find that some extra data will appear "inexplicably", as if an illusion has occurred. Both phantom reads and non-repeatable reads read another committed transaction (which is different from dirty reads). The difference is that non-repeatable reads query the same data item, while phantom reads query a batch of data as a whole (such as the number of data).

SERIALIZABLE

This is the most stringent isolation level. It avoids the problem of phantom reads by forcing transactions to be executed serially. However, this isolation level is very expensive and is not often used.

The relationship between various isolation levels and possible problems is as follows:

MVCC

Imagine that if each SQL operation needs to add a row-level lock to ensure data consistency and accuracy, it is very reliable, but the resulting system overhead and reduced search efficiency are also very obvious. Therefore, MVCC was created to resolve this contradiction.
First, MVCC saves two hidden columns behind each row in the table, one to save the creation time of the row, and the other to save the expiration (deletion) time of the row. This time value is not real time, but a system version number. The system version number at the start of the transaction is used as the transaction version number, which is compared with the version number of each row of records queried.

• INSERT: Save the current system version number as the row version number for each newly inserted row.
• DELETE: Save the current system version number as the row deletion version number for each deleted row.
• UPDATE: Update should actually be understood as the process of inserting a new data and deleting the original data, that is, saving the current system version number as the row version number for the newly inserted data, and saving the current system version number as the deletion version number for the deleted data.
• SELECT: Query only rows that satisfy the following conditions:

a. The row version number is less than or equal to the transaction version number
b. The deletion version number is undefined or greater than the transaction version number

After saving these two version numbers, most operations can be performed correctly without locking, ensuring performance and efficiency.
It is worth noting that MVCC only works at two isolation levels: READ COMMITED and REPEATABLE READ.

The above is a detailed explanation of MySQL database isolation level and MVCC. For more information about MySQL database isolation level and MVCC, please pay attention to other related articles on 123WORDPRESS.COM!