What you need to know about msyql transaction isolation

What is a transaction?

A transaction is a logical unit in the execution process of a database management system, consisting of a finite sequence of database operations. A database transaction usually consists of a sequence of read/write operations to the database. It has the following two purposes:

1. It provides a method for the database operation sequence to recover from failure to a normal state, and also provides a method for the database to maintain consistency even in an abnormal state.
2. When multiple applications access the database concurrently, an isolation method can be provided between these applications to prevent their operations from interfering with each other.

Isolation and Isolation Levels

When it comes to transactions, you will definitely think of ACID (Atomicity, Consistency, Isolation, Durability). Today we will 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. The stronger the isolation level, the worse the performance, so a balance must be struck between performance and isolation level. The SQL standard transaction isolation levels include:

• Read uncommitted: After a transaction is committed, the changes it makes will be seen by other transactions. A dirty read will occur.
• Read committed: After a transaction is committed, the changes it makes will be seen by other transactions. This will result in non-repeatable reads.
• Repeatable read: 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 also invisible to other transactions. Phantom reads will occur.
• Serializable: As the name implies, 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.

The main reason is that it is difficult to distinguish between read committed and repeatable read, so let's look at a small example. First create a table and insert data 1

create database test;
use test;
create table test(id int primary key);
insert into test(id) values(1);

Let's take a look at the different return results of transaction A under different isolation levels, that is, the return values ​​of V1, V2, and V3 in the figure.

• If the isolation level is "read uncommitted", the value of V1 is 2. Although transaction B has not been committed at this time, the result has been seen by A. Therefore, V2 and V3 are also 2.
• If the isolation level is "read committed", V1 is 1 and V2 is 2. The updates of transaction B can only be seen by A after they are committed. Therefore, the value of V3 is also 2.
• If the isolation level is "repeatable read", V1 and V2 are 1, and V3 is 2. The reason why V2 is still 1 is that it follows this requirement: the data seen by the transaction during execution must be consistent before and after.
• If the isolation level is "serializable", transaction B will be locked when it executes "change 1 to 2". Transaction B can continue to execute only after transaction A is committed. So from A's perspective, the values ​​of V1 and V2 are 1, and the value of V3 is 2.

A view will be created in the database, and the logical result of the view will be used when accessing it. 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.

So when is the scenario of **"repeatable read"** needed?

Suppose you are managing a table of personal bank accounts. One table stores the balance at the end of each month, and the other 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.

Implementation of transaction isolation

In MySQL, in fact, a rollback operation is recorded for each record 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. Suppose a value is changed from 1 to 2, 3, and 4 in sequence, there will be a record similar to the following in the rollback log.

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. Even if there is another transaction that is changing 4 to 5, this transaction will not conflict with the transactions corresponding to read-views A, B, and C.

The rollback log cannot be kept forever, when should it be deleted?

Delete it when it is 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.

When is it no longer necessary?

That is when there is no read-view in the system that is earlier than this rollback log.

Why should you avoid using transactions?

Transactions mean that there will be very old transaction views in the system. Before the transaction is committed, the rollback records must be retained, which will cause a large amount of storage space to be occupied. In addition, transactions occupy lock resources and may bring down the database.

The above is the details you need to know about msyql transaction isolation. For more information about mysql transaction isolation, please pay attention to other related articles on 123WORDPRESS.COM!