Let's talk about the characteristics and isolation levels of MySQL transactions

The Internet is already saturated with articles of this type, but the reason I still write this is simple - to serve as a note of my own understanding.

Preface

This article is my personal understanding of MySQL, and the engine used is InnoDb. First, let's talk about the concept of transactions. In the third edition of "High Performance MySQL", the description of transactions is as follows:

A transaction is a set of atomic SQL queries, or an independent unit of work. If the database engine can successfully apply all statements of the set of queries to the database, then the set of queries is executed. If any of the statements cannot be executed due to a crash or other reasons, then none of the statements will be executed.

In other words, a transaction is an integral unit, and the SQL statements in it will not be executed separately, just like some products, which are composed of multiple components. However, I absolutely do not sell components separately. If you want to buy, buy the whole product, otherwise I will not sell it.

After a simple understanding of transactions, you also need to know that the purpose of transactions is to ensure the correctness and consistency of data. For this purpose, its four characteristics are born (more on that later). In order to realize these four characteristics, many specific implementations are required, including four isolation levels for isolation. These four isolation levels have caused three problems (dirty reads, non-repeatable reads, and phantom reads). This is the general relationship. Next, let's take a look at what these are.

1 Four properties (ACID)

​​ Speaking of transaction characteristics, ACID is definitely the first thing that comes to mind. However, in addition to the four letters ACID, we still need to talk about something else.

Atomicity: It means that a transaction should be treated as an indivisible minimum unit. The operations of the entire transaction are either all executed successfully or not executed at all, just like atoms. (Don't mention quarks to me.) The execution here refers to successful execution. If one operation fails, then none of it will be executed. This is also the rollback we usually see.

​​ Consistency: The meaning given in the book is that transactions always jump from one consistent state to another consistent state. My understanding is that it is conserved within the data range involved, that is, the overall data remains unchanged. Take the universal example of transferring money. Account A transfers 200 yuan to account B. Then the data range composed of A and B has not changed (-200+200=0), but the composition of the data has changed, so it is from one consistent state -> another consistent state.

​​ Isolation: Generally speaking, the operations of one transaction are not visible to other transactions, which means that transactions are generally independent. But this is related to the isolation level of the database. Except for a certain isolation level (yes, it's you - read uncommitted classmate), the others are invisible, and this transaction visibility level is rarely used, so it is said 'generally speaking'.

Durability: Once a transaction is completed, the data changes caused by the transaction will take effect permanently and will not change (unless modified by another transaction). However, the book mentions that this is actually related to the implementation strategy, but this seems a bit far-fetched (yeah, I don’t understand!).

The above are the four characteristics of transactions. The implementation of isolation depends on the isolation level of the database.

2 Database isolation level

There are four isolation levels in MySQL. Each isolation level corresponds to different transactions and may cause different problems.

​​ Read uncommited: In this isolation level, operations performed in one transaction can be seen by other transactions even if they are not committed. At this level, a transaction may read dirty data that has not been committed by other transactions, that is, dirty reads may occur. As shown in the figure below, the sequence numbers indicate the order of execution.

​​ As you can see, a piece of data is inserted into the test table in the transaction on page 1. Even if it has not been submitted yet, the submitted data can be seen in another transaction on page 2.

​​ Read committed: After a transaction is committed, other transactions can see the changes made to the transaction. This isolation level may result in the same query being executed in the same transaction but reading different data, which is called nonrepeatable read. Nonrepeatable read may also occur in uncommitted reads. Examples are as follows

Repeatable read: This is the default isolation level of MySQL. At the beginning of a transaction, a snapshot will be saved at the moment (I'll be more specific here. In fact, it is a snapshot prepared when the first statement is executed after the transaction is started. The method of preparing the snapshot is to record the version number of the current transaction. No data is copied. If you don't understand the transaction version number or hidden fields, you can look at MySQL's MVCC). Then all subsequent data reads of this transaction are read from this snapshot, so there will be no non-repeatable reads, but phantom reads may still occur. This means that the snapshot table data will not change when it is read, but the number of updates during write operations such as updates may be different from the expected number. As shown

​​ It can be seen that after a record is inserted and submitted in interface 1, interface 2 still cannot read the submitted data because it is read from the snapshot table at the beginning of the transaction, so it naturally cannot read it. However, when the update operation is performed, unexpected records are updated. This is a phantom read phenomenon.

Serializable: This means that transactions must be processed one by one. If a read operation is performed in a transaction, other transactions can only perform read operations before the transaction is completed; if a write operation is performed, the operations of other transactions will wait (until the current transaction is committed). This level can prevent the current phenomena such as dirty reads, non-repeatable reads, and phantom reads. As shown

The figure above shows that when a transaction is reading, other transactions cannot write. The figure below shows that it cannot be operated when writing.

3 Three problems—dirty reads, non-repeatable reads, and phantom reads.

These are some of the problems that may arise from adopting different isolation levels of transactions. The isolation levels have been mentioned above, but they are still mentioned separately to avoid confusion.

• Dirty read: refers to reading dirty data in a transaction that has not been committed by other transactions, which occurs at the read uncommitted level.
• Non-repeatable read: The same query in a transaction may have different results, which occurs at the read uncommitted and read committed levels. (Personally, I think there is no need to understand what is called non-repeatable, it is easy to confuse)
• Phantom read: When performing a write operation in a transaction, the number of modifications is different from the expected number, for example, data that could not be queried before is modified.
  

​​ Let me explain the difference between non-repeatable reads and phantom reads: non-repeatable reads can be understood as changes in the field value of that record, for example, the two values ​​of name in the record with id 1 are different; while phantom reads are differences in quantity, for example, when I query, there are 2 records in total, but when I perform the modification operation, 3 records are updated.

The above is the details of the characteristics and isolation levels of MySQL transactions. For more information about the characteristics and isolation levels of MySQL transactions, please pay attention to other related articles on 123WORDPRESS.COM!