Detailed explanation of transaction isolation levels in MySql study notes

background

When we talk about transactions, everyone should be familiar with them. When developing with MySQL database, we usually use transactions. A classic example is money transfer. For example, you want to transfer 50 yuan to Xiao Ming, but you only have 50 yuan in your bank card at that time.

There will be a series of operations in the code program for the transfer process, such as checking the account balance, adding and subtracting the balance, updating the balance, etc. These operations must be processed together. Otherwise, after the program is finished checking, if the 50 yuan in the account is still there, and then the money is transferred to another friend, if the bank also handles it, and the data consistency of the entire process is not guaranteed, wouldn’t it be a mess? This is when "transactions" come into play.

Transaction Introduction

Simply put, a transaction is to ensure that a set of database operations are either all executed successfully or all failed. In MySQL, transaction support is implemented at the engine level (InnoDB). We know that MySQL is a system that supports multiple engines, but not all engines support transactions. For example, MySQL's native MyISAM engine does not support transactions, which is one of the important reasons why MyISAM was replaced by InnoDB.

In this article, we will take InnoDB as an example to talk about some implementations of MySQL transaction support and give corresponding practical suggestions based on the principles. Through these explanations, you can deepen your understanding of the principles of MySQL transactions.

Transaction isolation level

When it comes to transactions, you will definitely think of ACID (Atomicity, Consistency, Isolation, Durability). Let’s talk about the I, which is "isolation".

When multiple transactions are executed simultaneously on a database, dirty reads, non-repeatable reads, and phantom reads may occur. To solve these problems, the concept of "isolation level" was introduced.

Before talking about isolation levels, you must first know that the stricter your isolation, the lower the efficiency will be. Therefore, many times, we have to find a balance between the two. The SQL standard transaction isolation levels include: read uncommitted, read committed, repeatable read, and serializable. Here are the explanations for you one by one:

• Read uncommitted means that when a transaction is not committed, the changes it makes can be seen by other transactions.
• Read commit means that after a transaction is committed, the changes it makes will be seen by other transactions.
• Repeatable read means that the data seen during the execution of a transaction is always consistent with the data seen when the transaction was started. Of course, at the repeatable read isolation level, uncommitted changes are not visible to other transactions.
• Serialization, as the name implies, means that for the same row of records, "write" will add a "write lock" and "read" will add a "read lock". When a read-write lock conflict occurs, the later accessed transaction must wait until the previous transaction is completed before it can continue execution.

Among them, "Read Committed" and "Repeatable Read" are more difficult to understand, so I plan to use an example to illustrate these isolation levels. Assume that there is only one column in the data table t_student, and the value of one row is 21. The following is the behavior of executing two transactions in chronological order.

mysql> create table t_student(age int) engine=InnoDB;
mysql> insert into t_student(age) values(21);

Under different isolation levels, what are the different return results of transaction A? That is, what are the return values ​​of V1, V2, and V3 in the figure respectively? If the isolation level is "read uncommitted", the value of V1 is 22. Although transaction B has not been committed at this time, the result is visible to A. Therefore, V2 and V3 are also 22.

If the isolation level is "read committed", V1 is 21 and V2 is 22. The updates of transaction B are visible to A only after it is committed. Therefore, the value of V3 is also 22.

If the isolation level is "repeatable read", V1 and V2 are 21, and V3 is 22. The reason why V2 is still 21 is that it follows this principle: the data seen by the transaction during execution must be consistent.

If the isolation level is "serialization", when transaction B executes "change 21 to 22", it will be locked. Transaction B can continue to execute only after transaction A is committed. So from A's perspective, the values ​​of V1 and V2 are 21, and the value of V3 is 22.

In implementation, a view will be created in the database, and the logical result of the view will be used as the basis for access. Under the "repeatable read" isolation level, this view is created when the transaction starts and is used throughout the transaction.

In the "Read Committed" isolation level, this view is created at the beginning of each SQL statement execution. It should be noted here that the "read uncommitted" isolation level directly returns the latest value on the record, without the concept of view; while the "serializable" isolation level directly uses locking to avoid parallel access.

It can be seen that the database behavior is different under different isolation levels. The default isolation level of Oracle database is actually "read committed". Therefore, for some applications migrated from Oracle to MySQL, in order to ensure the consistency of database isolation level, you must remember to set the isolation level of MySQL to "read committed".

The configuration method is to set the value of the startup parameter transaction-isolation to READ-COMMITTED. You can use showvariables to view the current value.

mysql> show variables like 'transaction_isolation';

In general, existence is reasonable. Each isolation level has its own usage scenario and should be determined based on your own business situation. Some people may ask when is a "repeatable read" scenario needed? Let's look at a case study of data proofreading logic.

Suppose you are managing a bank account table, one table stores the balance at the end of each month, and one table stores the bill details. At this time, you need to do data proofreading, that is, determine whether the difference between last month's balance and the current balance is consistent with this month's bill details. You must hope that during the proofreading process, even if a user has a new transaction, it will not affect your proofreading results. At this time, it is convenient to use the "repeatable read" isolation level. The view when a transaction starts can be considered static and is not affected by updates from other transactions.

Implementation of transaction isolation

Now that we understand the transaction isolation level, let's take a look at how transaction isolation is implemented. Here we explain "repeatable read" in detail. In MySQL, in fact, each record will record a rollback operation when it is updated. The latest value on the record can be used to get the value of the previous state through a rollback operation.

Assume that a value is changed from 1 to 2, 3, and 4 in sequence, and the undo log will contain a record similar to the following:

The current value is 4, but when querying this record, transactions started at different times will have different read-views. As shown in the figure, in views A, B, and C, the values ​​of this record are 1, 2, and 4 respectively. The same record can have multiple versions in the system, which is the multi-version concurrency control (MVCC) of the database. For read-view A, to get 1, you must execute all the rollback operations in the figure in sequence to get the current value.

At the same time, you will find that even if there is another transaction that is changing 4 to 5, this transaction will not conflict with the transactions corresponding to read-viewA, B, and C.
Some people may ask, the rollback log cannot be kept forever, when should it be deleted? Of course, they are deleted when they are not needed. In other words, the system will determine that when no transaction needs to use these rollback logs, the rollback logs will be deleted.

So the question is, when is it no longer needed? That is when there is no read-view in the system that is earlier than this rollback log.

Based on the above explanation, let's discuss why it is recommended that you avoid using long transactions as much as possible.

First of all, long transactions mean that there will be very old transaction views in the system. Since these transactions may access any data in the database at any time, before the transaction is committed, all rollback records that it may use in the database must be retained, which will result in a large amount of storage space being occupied.

In MySQL 5.5 and earlier versions, the rollback log is placed in the ibdata file together with the data dictionary. Even if the long transaction is finally committed and the rollback segment is cleaned up, the file will not become smaller. I have seen libraries with only 10GB of data and 100GB of rollback segments. In the end, the entire library had to be rebuilt to clean up the rollback segments.
In addition to the impact on the rollback segment, long transactions also occupy lock resources and may also bring down the entire database.

Transaction start mode

The long transactions mentioned above have these potential risks, and the recommendation is of course to avoid them as much as possible. In fact, many times business developers do not intentionally use long transactions, but usually due to misuse. There are several ways to start a MySQL transaction:

1. Explicitly start a transaction statement, begin or start transaction. The corresponding commit statement is commit, and the corresponding rollback statement is rollback.
2. set autocommit=0, this command will turn off the automatic commit of this thread. This means that if you only execute a select statement, the transaction is started and is not automatically committed. This transaction persists until you actively execute a commit or rollback statement, or disconnect.

Some client connection frameworks will execute a set autocommit=0 command by default after a successful connection. This causes all subsequent queries to be in the transaction, and if it is a long connection, it will lead to an unexpectedly long transaction.

Therefore, it is recommended to always use set autocommit=1 and start transactions through explicit statements. However, some developers may be troubled by the issue of "one more interaction". For a business that requires frequent use of transactions, the second method does not require the active execution of "begin" at the beginning of each transaction, thus reducing the number of statement interactions. If you have this concern, it is recommended to use the commit work and chain syntax.

When autocommit is 1, a transaction explicitly started with begin will be committed if commit is executed. If commit work and chain is executed, the transaction is committed and the next transaction is automatically started, which also saves the overhead of executing the begin statement again. The benefit is that from the perspective of program development, you can clearly know whether each statement is in a transaction. You can query long transactions in the innodb_trx table in the information_schema library.

Summarize

It mainly talks about the phenomenon and implementation of MySQL's transaction isolation level, analyzes the risks of long transactions based on the implementation principles, and how to avoid long transactions in the right way. Understanding the principles of these transactions can help you better use the transaction features of MySQL.

This is the end of this article about MySQL study notes and transaction isolation levels. For more information about MySQL transaction isolation levels, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!