Shulou(Shulou.com)05/31 Report--

This article is to share with you about how to achieve the cross-line transaction model in MySQL. The editor thinks it is very practical, so I share it with you. I hope you can get something after reading this article.

Atomicity guarantee of MySQL transaction

Transaction atomicity requires that a series of operations in the transaction are either completed or not done at all, not half of them. Atomicity is easy to implement for atomic operations, just as atomicity for row-level transactions in HBase is relatively simple. However, for a transaction composed of multiple statements, if an exception occurs during the execution of the transaction, the atomicity can only be rolled back to the state before the start of the transaction, as if the transaction never happened at all. How can it be realized?

The rollback operation of MySQL depends entirely on undo log, and by the way, undo log is used to implement MVCC in MySQL in addition to atomicity assurance, which will be covered below. Atomicity using undo before manipulating any data, the pre-modified data is first recorded in undo log, and then the actual modification is made. If an exception occurs and needs to be rolled back, the system can use the backup in undo to restore the data to the state it was before the transaction began. The following figure shows the basic data structure of the transaction in MySQL, where the fields related to undo are insert_undo and update_undo, pointing to the undo log generated by this transaction, respectively.

The transaction rollback finds the corresponding undo log according to update_undo (or insert_undo) and does the reverse operation. For the data cleanup deletion tag that has been marked for deletion, roll back the update directly for the updated data; the insert operation is slightly more complex, not only the data needs to be deleted, but also the related clustered index and secondary index records need to be deleted.

Undo log is a very important part of the MySQL kernel, which involves a lot of knowledge and complexity, such as:

1. Undo log must be persisted before the data is modified. Does undo log persistence need to record redo to prevent outages? If necessary, it will involve downtime recovery.

two。 How to implement MVCC through undo log?

3. In what scenarios can those undo log be recycled and cleaned up? How to clean it up?

MySQL transaction consistency guarantee: strong consistency transaction guarantee

MySQL transaction isolation level

Read Uncommitted (interpretation of RU technology: using X locks to achieve write concurrency)

Read Uncommitted only implements write and write concurrency control, and there is no effective read-write concurrency control, so that the current transaction may read the uncommitted modified data in other transactions, and the accuracy of these data is not reliable (it may be rolled back), so all the assumptions made on this basis are unreliable. In real-world scenarios, few businesses choose this isolation level.

The write concurrency implementation mechanism is no different from HBase, which uses two-phase locking protocol to lock the corresponding records. However, the row locking mechanism in MySQL is more complex, which can be divided into several locking cases according to whether the row record is a primary key index, a unique index, a non-unique index or no index.

1. If the id column is a primary key index, MySQL only locks the clustered index record.

two。 If the id column is the only secondary index, MySQL locks the secondary index leaf nodes and clustered index records.

3. If the id column is a non-unique index, MySQL locks all secondary index leaf nodes that meet the condition (id = 15) and the corresponding clustered index records.

4. If the id column is indexed, SQL will scan the whole table with a clustered index, and load the scan results into the SQL Server layer for filtering, so InnoDB will lock all scanned records first, and if the SQL Server layer filtering does not meet the criteria, InnoDB will release the lock. So InnoDB will lock all the scanned records, isn't it scary?

Next, whether it's RC, RR, or Serialization, write concurrency control uses the above mechanism, so I won't repeat it. Next, we will focus on the read-write concurrency control mechanism in the RC and RR isolation levels.

Before introducing RC and RR in detail, it is necessary to introduce the MVCC mechanism in MySQL, because both RC and RR use the MVCC mechanism to achieve read-write concurrency between transactions. It's just that there are some differences between the two in the implementation details, and the specific differences will be discussed later.

MVCC in MySQL

The MVCC mechanism in MySQL is much more complex than HBase, and the data structure involved is also more complex. In order to explain more clearly, take a chestnut as the template to explain. For example, there is a row of records as shown in the following figure:

The first four columns are the actual column values of the row record, and the two hidden columns DB_TRX_ID and DB_ROLL_PTR (invisible to the user) need to be focused on. Where DB_TRX_ID represents the transaction ID that modifies the transaction of the row, and DB_ROLL_PTR represents the pointer to the rollback segment of the row, where all version data on the record is organized as a linked list in undo, which actually points to the history linked list of the row in undo.

Now suppose a transaction trx2 modifies the data of the row, and the row record will be in the form of the following figure. DB_TRX_ID is the transaction ID (trx2) that recently modified the transaction of the row, and DB_ROLL_PTR points to the linked list of undo history records:

Now that you understand the MySQL row records, let's look at the basic structure of the transaction. The following figure shows the transaction data structure of MySQL, which we mentioned above. After a transaction is opened, a data structure is created to store transaction-related information, lock information, undo log, and very important read_view information.

Read_view keeps a list of all active transactions in the entire MySQL when the current transaction is opened. As shown in the following figure, when the current transaction is started, the active transactions in the system are trx4, trx6, trx7 and trx10. In addition, up_trx_id indicates that when the current transaction starts, the smallest transaction ID;low_trx_id in the current transaction linked list represents the largest transaction ID in the current transaction linked list when the current transaction starts.

Read_view is a key point in implementing MVCC, which is used to determine which version of the record is visible to the current transaction. If the current transaction wants to read a row of records, and the version number (transaction ID) of the row record is trxid, then:

1. If trxid

< up_trx_id，说明该行记录所在的事务已经在当前事务创建之前就提交了，所以该行记录对当前事务可见。 2. 如果trxid >

Low_trx_id, indicating that the transaction of the row is not opened until after the current transaction is created, so the row record is not visible to the current transaction.

3. If up_trx_id

< trxid < low_trx_id， 那么表明该行记录所在事务在本次新事务创建的时候处于活动状态。从up_trx_id到low_trx_id进行遍历，如果trxid等于他们之中的某个事务id的话，那么不可见，否则可见。 以下面行记录为例，该行记录存在多个版本(trx2、trx5、trx7以及trx12)，其中trx12是最新版本。看看该行记录中哪个版本对当前事务可见。 1. 该行记录的最新版本为trx12，与当前事务read_view进行对比发现，trx12大于当前活跃事务列表中的最大事务trx10，表示trx12是在当前事务创建之后才开启的，因此不可见。 2. 再查看该行记录的第二个最新版本为trx7，与当前事务read_view对比发现，trx7介于当前活跃事务列表最小事务ID和最大事务ID之间，表明该行记录所在事务在当前事务创建的时候处于活动状态，在活跃列表中遍历发现trx7确实存在，说明该事务还没有提交，所以对当前事务不可见。 3. 继续查看该记录的第三个最新版本trx5，也介于当前活跃事务列表最小事务ID和最大事务ID之间，表明该行记录所在事务在当前事务创建的时候处于活动状态，但遍历发现该版本并不在活跃事务列表中，说明trx5对应事务已经提交(注：事务提交时间与事务编号没有任何关联，有可能事务编号大的事务先提交，事务编号小的事务后提交)，因此trx5版本行记录对当前事务可见，直接返回。 Read Committed(技术解读：写写并发使用X锁，读写并发使用MVCC避免脏读) 上文介绍了MySQL中MVCC技术实现机制，但要明白RC隔离级别下事务可见性，还需要get一个核心点：RC隔离级别下的事务在每次执行select时都会生成一个最新的read_view代替原有的read_view。 如上图所示，左侧为1号事务，在不同时间点对id=1的记录分别查询了三次。右侧为2号事务，对id=1的记录进行了更新。更新前该记录只有一个版本，更新好变成了两个版本。 1号事务在RC隔离级别下每次执行select请求都会生成一个最新的read_view，前两次查询生成的全局事务活跃列表中包含trx2，因此根据MVCC规定查到的记录为老版本;最后一次查询的时间点位于2号事务提交之后，因此生成的全局活跃事务列表中不包含trx2，此时在根据MVCC规定查到的记录就是最新版本记录。 Repeatable Read(技术解读：写写并发使用X锁，读写并发使用MVCC避免不可重复读;当前读使用Gap锁避免幻读) 和RC模式不同，RR模式下事务不会再每次执行select的时候生成最新的read_view，而是在事务第一次select时就生成read_view，后续不会再变更，直至当前事务结束。这样可以有效避免不可重复读，使得当前事务在整个事务过程中读到的数据都保持一致。示意图如下所示： 这个就很容易理解，三次查询所使用的全局活跃事务列表都一样，且都是第一次生成的read_view，那之后查到的记录必然和第一次查到的记录一致。 RR隔离级别能够避免幻读吗? 如果对幻读还不了解的话，可以参考该系列的第一篇文章。如下图所示，1号事务对针对id>

The filter condition of 1 executes three queries, transaction 2 performs one insert, and the inserted record exactly meets the condition of id > 1. As you can see, the data obtained from the three queries is consistent, which is guaranteed by the MVCC mechanism of the RR isolation level. In this way, it avoids phantom reading, but in the last transaction 1, inserting a record at id=2, MySQL will return the error of Duplicate entry, which shows that avoiding phantom reading is an illusion.

Strictly avoid illusion (technical interpretation: the current read uses Gap locks to avoid illusion)

All the RR-level select statements mentioned earlier are called snapshot reads, which ensure that they cannot be read repeatedly, but they do not avoid phantom reading. So MySQL puts forward the concept of "current reading". The common current reading sentences are:

1. Select for update

2. Select lock in share mode

3. Update / delete

It is also stipulated that the current read statement under the RR level will add a special lock-Gap lock to the record. The Gap lock does not lock a specific record, but locks the interval between the record and the record to ensure that no other new records will be inserted in this interval. The following figure is a schematic diagram:

Transaction 1 in the figure above first executes a currently read select statement, which adds a Gap lock to all intervals where id > 0, and then transaction 2 returns an exception of Lock wait timeout execcded when it performs an insert at id = 3. Of course, other transactions can be found in id

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.