Let's talk to you about transaction isolation in MySQL

This article will take you to understand transaction isolation in MySQL, introduce the characteristics of transactions, isolation levels, transaction startup methods, etc. I hope it will be helpful to everyone!

#A transaction is to ensure that a set of database operations either all succeed or all fail. In MySQL, transaction support is implemented at the engine layer, but not all engines support transactions. For example, MySQL's native MyISAM engine does not support transactions. [Related recommendations: mysql tutorial (video)]

1. Characteristics of transactions

• Atomicity: all transactions in a transaction Operations are either completely completed or not completed at all, and will not end somewhere in the middle. If an error occurs during the execution of the transaction, it will be rolled back to the state before the transaction started, as if the transaction had never been executed.
• Consistency: Before the transaction starts and after the transaction ends, the integrity of the database Not destroyed
• Isolation: The database allows multiple concurrent transactions to read, write and modify data at the same time. Isolation can prevent data inconsistencies caused by cross-execution when multiple transactions are executed concurrently
• Persistence: After the transaction is completed, the modification to the data is permanent and will not be lost even if the system fails

2. Isolation level

1. When multiple transactions are executed simultaneously on the database, problems such as dirty reads, non-repeatable reads, and phantom reads may occur.

• Dirty reads: Transaction B has read that transaction A has not yet been committed. Data
• Non-repeatable read: A transaction reads the update data submitted in another transaction
• Phantom read/virtual read: A transaction reads the insert submitted in another transaction Data

2. The isolation levels of transactions include: read uncommitted, read committed, repeatable read and serialization

• Read uncommitted: a transaction has not yet When committing, the changes it makes can be seen by other transactions
• Read commit: After a transaction is submitted, the changes it makes can be seen by other transactions (solve dirty reads, Oracle's default isolation level )
• Repeatable reading: The data seen during the execution of a transaction is always consistent with the data seen when the transaction is started, and uncommitted changes are also invisible to other transactions (solution Dirty read and non-repeatable read, MySQL's default isolation level)
• Serialization: For the same row of records, writing will add a write lock, and reading will add a read lock. When a read-write lock conflict occurs, then The accessed transaction must wait for the completion of the previous transaction before it can continue to execute (solve dirty reads, non-repeatable reads and phantom reads)

Security is submitted in sequence, and performance is reduced in sequence

3 .Assume that there is only one column in the data table T, and the value of one row is 1

create table T(c int) engine=InnoDB;
insert into T(c) values(1);

The following is the behavior of executing two transactions in chronological order:

• If the isolation level is read uncommitted, V1 is 2. At this time, although transaction B has not yet been submitted, the result has been seen by A. Both V2 and V3 are 2
• If the isolation level is read-commit, then V1 is 1 and V2 is 2. The updates of transaction B can only be seen by A after they are committed. V3 is also 2
• If the isolation level is repeatable read, then V1 and V2 are 1, and V3 is 2. The reason why V2 is 1 follows that the data seen before and after the transaction is executed must be consistent
• If the isolation level is serialization, the values ​​of V1 and V2 are 1, and V3 is 2

In terms of implementation, a view will be created in the database, and the logical result of the view shall prevail when accessing. Under the repeatable read isolation level, this view is created when the transaction starts and is used throughout the transaction. Under the read-commit isolation level, this view is created at the beginning of each SQL statement. Under the read uncommitted isolation level, the latest value on the record is directly returned, without the concept of a view; while under the serialized isolation level, locking is directly used to avoid parallel access

3. Implementation of transaction isolation (Take repeatable read as an example)

In MySQL, each record will record a rollback operation at the same time when it is updated. The latest value on the record, through the rollback operation, can get the value of the previous state

Suppose a value is changed from 1 to 2, 3, and 4 in order, there will be in the rollback log Similar to the following record

#The current value is 4, but when querying this record, transactions started at different times will have different read-views. As you can see 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-viewA, to get 1, you must perform all rollback operations on the current value at once to get

Even if there is another transaction changing 4 to 5, this transaction is different from read -The transactions corresponding to view A, B, and C will not conflict

系统会判断，当没有事务再需要用到这些回滚日志时，回滚日志会被删除

四、事务启动的方式

MySQL的事务启动方式有以下几种：

• 显示启动事务语句，begin或start transaction。提交语句是commit，回滚语句是rollback
• set autocommit=0，这个命令将这个线程的自动提交关掉。意味着如果只执行一个select语句，这个事务就启动了，而且不会自动提交事务。这个事务持续存在直到主动执行commit或rollback语句，或者断开连接

建议使用set autocommit=1，通过显示语句的方式来启动事务

可以在information_schema库中的innodb_trx这个表中查询长事务，如下语句查询持续时间超过60s的事务

select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60

五、事务隔离还是不隔离

下面是一个只有两行的表的初始化语句：

mysql> CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `k` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;
insert into t(id, k) values(1,1),(2,2);

事务A、B、C的执行流程如下，采用可重复读隔离级别

begin/start transaction命令：不是一个事务的起点，在执行到它们之后的第一个操作InnoDB表的语句，事务才真正启动，一致性视图是在执行第一个快照读语句时创建的

start transaction with consistent snapshot命令：马上启动一个事务，一致性视图是在执行这条命令时创建的

按照上图的流程执行，事务B查到的k的值是3，而事务A查到的k的值是1

1、快照在MVCC里是怎么工作的？

在可重复读隔离级别下，事务启动的时候拍了个快照。这个快照是基于整个库的，那么这个快照是如何实现的？

InnoDB里面每个事务有一个唯一的事务ID，叫做transaction id。它在事务开始的时候向InnoDB的事务系统申请，是按申请顺序严格递增的

每行数据也都是有多个版本的。每次事务更新数据的时候，都会生成一个新的数据版本，并且把transaction id赋值给这个数据版本的事务ID，记作row trx_id。同时，旧的数据版本要保留，并且在新的数据版本中，能够有信息可以直接拿到它。也就是说，数据表中的一行记录，其实可能有多个版本，每个版本有自己的row trx_id

下图是一个记录被多个事务连续更新后的状态：

语句更新生成的undo log（回滚日志）就是上图中的是哪个虚线箭头，而V1、V2、V3并不是物理上真实存在的，而是每次需要的时候根据当前版本和undo log计算出来的。比如，需要V2的时候，就是通过V4依次执行U3、U2算出来的

按照可重复读的定义，一个事务启动的时候，能够看到所以已经提交的事务结果。但是之后，这个事务执行期间，其他事务的更新对它不可见。在实现上，InnoDB为每个事务构造了一个数组，用来保存这个事务启动瞬间，当前在启动了但还没提交的所有事务ID。数组里面事务ID的最小值记为低水位，当前系统里面已经创建过的事务ID的最大值加1记为高水位。这个视图数组和高水位就组成了当前事务的一致性视图。而数据的可见性规则，就是基于数据的row trx_id和这个一致性视图的对比结果得到的

这个视图数组把所有的row trx_id分成了几种不同的情况

对于当前事务的启动瞬间来说，一个数据版本的row trx_id，有以下几种可能：

1）如果落在绿色部分，表示这个版本是已提交的事务或者是当前事务自己生成的，这个数据是可见的

2）如果落在红色部分，表示这个版本是由将来启动的事务生成的，肯定不可见

3）如果落在黄色部分，那就包括两种情况

• 若row trx_id在数组中，表示这个版本是由还没提交的事务生成的，不可见
• 若row trx_id不在数组中，表示这个版本是已经提交了的事务生成的，可见

InnoDB利用了所有数据都有多个版本的这个特性，实现了秒级创建快照的能力

2、为什么事务A的查询语句返回的结果是k=1？

假设：

1.事务A开始时，系统里面只有一个活跃事务ID是99

2.事务A、B、C的版本号分别是100、101、102

3.三个事务开始前，(1,1)这一行数据的row trx_id是90

In this way, the array of transaction A is [99,100], the view array of transaction B is [99,100,101], and the view array of transaction C is [99,100,101,102]

From As you can see in the above figure, the first effective update is transaction C, which changes the data from (1,1) to (1,2). At this time, the row trx_id of the latest version of this data is 102, and version 90 has become a historical version

The second effective update is transaction B, which changes the data from (1,2) to ( 1,3). At this time, the latest version of this data is 101, and 102 has become the historical version

When transaction A queries, transaction B has not yet been submitted, but the version (1,3) it generated has been It has become the current version. But this version must be invisible to transaction A, otherwise it will become a dirty read

Now transaction A wants to read data, and its view array is [99,100]. Reading data starts from the current version. Therefore, the data reading process of transaction A query statement is as follows:

• When finding (1,3), it is determined that row trx_id=101, which is larger than the high water level, is in the red area, and is invisible
• Then, find the previous historical version, and see row trx_id=102, which is larger than the high water level, in the red area, and invisible
• Look further, and finally find (1,1 ), its row trx_id=90, which is smaller than the low water level and is in the green area. It can be seen that

Although this row of data has been modified during the period, no matter when transaction A queries, it will see this row The results of the data are all consistent, which we call consistent reading.

A data version. For a transaction view, in addition to its own updates being always visible, there are three situations:

• The version is not submitted and is invisible
• The version is submitted, but it is submitted after the view is created, and the invisible
• version is submitted and it is submitted before the view is created, It can be seen that

The view array of the query statement of transaction A is generated when transaction A is started. At this time:

• (1,3) has not been submitted yet and belongs to Case 1, invisible
• (1,2) is submitted, but it is submitted after the view array is created, which belongs to case 2, invisible
• (1,1) is in the view It was submitted before the array was created. It can be seen that

3. Why does the query statement of transaction B return k=3?

When transaction B wants to update data, it can no longer update on the historical version, otherwise the update of transaction C will be lost. Therefore, the set k=k 1 of transaction B at this time is an operation based on (1,2)

The updated data is read first and then written, and this read can only Reading the current value is called current reading. In addition to the update statement, if the select statement is locked, it will also be the current read

What if transaction C is not submitted immediately, but becomes the following transaction C’?

In the above figure, transaction C was not submitted immediately after being updated. Before it was submitted, the update statement of transaction B was initiated first. Although transaction C has not yet been submitted, the version (1,2) has also been generated and is the latest version

At this time, a two-stage lock protocol is involved, and transaction C has not been submitted, that is to say ( 1,2) The write lock on this version has not been released yet. And transaction B is the current read. It must read the latest version and must be locked, so it is locked. It must wait until transaction C releases the lock before it can continue its current read

7. How is the repeatable read capability of transactions achieved?

The core of repeatable reading is consistent reading; when a transaction updates data, only the current read can be used. If the row lock of the current record is occupied by other transactions, you need to enter the lock wait

The logic of read commit is similar to the logic of repeatable read. The main difference between them is:

• Under the repeatable read isolation level, you only need to create a consistency view at the beginning of the transaction, and then other queries in the transaction will share this consistency view
• Under the read commit isolation level, each statement A new view will be calculated repeatedly before execution

For more programming-related knowledge, please visit:Programming Video! !

The above is the detailed content of Let's talk to you about transaction isolation in MySQL. For more information, please follow other related articles on the PHP Chinese website!