Detailed introduction to transaction isolation and MVCC of MySQL database (picture and text)

This article brings you a detailed introduction (pictures and texts) about transaction isolation and MVCC of MySQL database. It has certain reference value. Friends in need can refer to it. I hope it will be helpful to you.

Foreword: A transaction is a sequence of operations to access the database. The database application system completes access to the database through transaction sets.

1. What is a transaction?

Transaction Must comply with the ACID principles established by ISO/IEC. ACID is the abbreviation of atomicity, consistency, isolation, and durability. The meaning of these four states is:
1. Atomicity
Atomic Properties means that all operations included in a transaction either succeed or fail and are rolled back. This is the same concept as the function of transactions introduced in the previous two blogs. Therefore, if the operation of the transaction is successful, it must be fully applied to the database. If the operation fails, It cannot have any impact on the database.
2. Consistency
Consistency means that a transaction must transform the database from one consistency state to another consistency state.
3. Isolation
When the transaction is correct Before committing, the transaction's changes to the data are not allowed to be provided to any other transaction, that is, its possible results should not be displayed to other transactions before the transaction is correctly committed.
4. Durability (Durability)
Durability Persistence means that once a transaction is committed, the changes to the data in the database are permanent, and the operation of committing the transaction will not be lost even if the database system encounters a failure.

2. The role of transactions

When multiple threads start transactions to operate data in the database, the database system must be able to perform isolation operations to ensure the accuracy of data obtained by each thread.

3. Concurrency problems encountered

1. The first type of lost update: when transaction A is revoked, the updated data of transaction B that has been submitted is overwritten.
2. The second type Class loss update: Transaction A overwrites the data submitted by transaction B, causing the operation of transaction B to be lost.
3. Dirty read: Transaction A reads uncommitted data in transaction B.
4. Non-repeatable Read: The value read multiple times by transaction A is different because the value was modified and committed by transaction B.
5. Phantom read: Between the two reads of transaction A, transaction B inserted data.

4. How to solve the above problem?

In order to solve the above problem, developers have designed the following four transaction isolation levels for the MySQL database:
1. Read Uncommitted (uncommitted read): allowed Dirty reading means that it is possible to read data modified by uncommitted transactions in other sessions.

2. Read Committed: Only committed data can be read. Most databases such as Oracle default to this level (no repeated reads).

3.Repeated Read: repeatable read. Queries within the same transaction are consistent at the start of the transaction, InnoDB default level. In the SQL standard, this isolation level eliminates non-repeatable reads, but phantom reads still exist, but innoDB solves phantom reads.

4. Serializable (serial read): Completely serialized read, every Each read needs to obtain a table-level shared lock, and reads and writes will block each other.

Isolation level	Dirty read	Non-repeatability	No phantom reading
Read Uncommitted(Uncommitted read)	Possibly	Possibly	Possible
Read Committed	Impossible	Possible	Possible
Repeated Read	Impossible	Impossible	Possible
Serializable (Serial reading)	Impossible	Impossible	Impossible

5. Small attempt

1. Check the global or session transaction isolation level

SELECT @@global.tx_isolation, @@tx_isolation;

2. Modify the global or session transaction isolation level

SET [SESSION|GLOBAL] TRANSACTION ISOLATION LEVEL [READ UNCOMMITTED|READ COMMITTED|REPEATABLE READ|SERIALIZABLE]

6. MySQL defaults to the Repeated Read isolation level. Logically speaking, it cannot solve the phantom read problem?

The following will first introduce the locks involved in the database.

7. Locks Basic description

1. Introduction to locks
The lock in the database refers to a software mechanism used to control and prevent a user (process session) from occupying a certain data resource. Make means that affect the user's data operations or cause data non-integrity and non-consistency problems.
2. Lock level
According to the lock level, locks can be divided into shared locks and exclusive locks.

• Shared lock (read lock)

For the same piece of data, multiple read operations can be performed at the same time without affecting each other. Shared locks are only locked during UPDATE. Before the UPDATE operation is submitted, other transactions can only obtain the latest records but cannot perform UPDATE operations.

• Exclusive lock (write lock)

Block other write locks and read locks before the current write operation is completed.
3. Lock granularity
According to the lock granularity, locks can be divided into table-level locks, row-level locks, and page-level locks.

• Row-level lock

The overhead is large, locking is slow, deadlock will occur, the locking strength is the smallest, the probability of lock conflict is the lowest, and the concurrency is high.

• Table-level lock

The overhead is small, locking is fast, no deadlock will occur, the locking force is strong, the probability of conflict is high, and the concurrency is low.

• Page lock

The overhead and locking time are between table locks and row locks, deadlocks will occur, and the locking intensity is between table and row-level locks. In between, the concurrency is average.

8. Pessimistic locking and optimistic locking

8.1 Pessimistic locking

1. Basic idea: always assume the worst case scenario, and think that every time you get the data Others will modify it, so it will be locked every time it gets the data. In this way, if others want to get the data, it will be blocked until it gets the lock (the shared resource is only used by one thread at a time, and other threads are blocked. After use, it will be blocked. Resources are transferred to other threads). Many such lock mechanisms are used in traditional relational databases, such as row locks, table locks, read locks, write locks, etc., which are all locked before operations. Therefore, no matter whether a conflict actually occurs, they will be used lock mechanism.
2. Pessimistic lock function:

• Lock the read records to prevent other transactions from reading and updating these records. Other transactions will be blocked until this transaction ends.
• Pessimistic locking is based on the use of the database's transaction isolation function and exclusive use of occupied resources to ensure the consistency of read data and avoid modification loss.
• Pessimistic locks can use Repeatable Read transactions, which fully meet the requirements of pessimistic locks.

8.2 Optimistic locking

1. Basic idea: Always assume the best situation. Every time you go to get the data, you think that others will not modify it, so you will not lock it. , but when updating, it will be judged whether others have updated this data during this period, which can be implemented using version number mechanism and CAS algorithm. Optimistic locking is suitable for multi-read application types, which can improve throughput.
2. Explanation: Optimistic locking is an idea. Optimistic locking does not lock anything, that is, it does not rely on the transaction mechanism of the database. , Optimistic locking is completely at the application system level. So it is not a locking mechanism. If optimistic locking is used, the database must add a version field, otherwise all fields can only be compared, but because floating point types cannot be compared, it is actually not feasible without a version field

8.3 Version number mechanism

Generally, a data version number version field is added to the data table to indicate the number of times the data has been modified. When the data is modified, the version value will be incremented by one. When thread A wants to update the data value, it will also read the version value while reading the data. When submitting the update, update it only if the version value just read is equal to the version value in the current database, otherwise try again. Update operation until the update is successful.

8.4 CAS algorithm

1. Core idea: Compare and Swap, that is, compare and then exchange.
2. Process: Assume that thread A is going to modify the value of the variable named name in the memory, so thread A will compare the value of the name variable it read before with the value of name at this moment. If they are the same, it means that in The variable value has not been modified, so it can be updated and modified, otherwise the update fails.

9. Return to MySQL's Repeated Read transaction isolation level

As mentioned before, MySQL implements the repeatable read transaction isolation level by default, but it cannot solve the problem of phantom reads. However, under the transaction isolation conditions of the MySQL database using repeatable reads, phantom reads did not occur. MySQL uses MVCC (Multi-version Concurrency) Control) was controlled.

9.1名词简析:

1.MVCC:是multiversion concurrency control的简称，也就是多版本并发控制，是个很基本的概念。MVCC的作用是让事务在并行发生时，在一定隔离级别前提下，可以保证在某个事务中能实现一致性读，也就是该事务启动时根据某个条件读取到的数据，直到事务结束时，再次执行相同条件，还是读到同一份数据，不会发生变化（不会看到被其他并行事务修改的数据）。
2.read view:InnoDB MVCC使用的内部快照的意思。在不同的隔离级别下，事务启动时（有些情况下，可能是SQL语句开始时）看到的数据快照版本可能也不同。在上面介绍的几个隔离级别下会用到 read view。
3.快照读: 就是所谓的根据read view去获取信息和数据，不会加任何的锁。
4.当前读:前读会获取得到所有已经提交数据，按照逻辑上来讲的话，在一个事务中第一次当前读和第二次当前读的中间有新的事务进行DML操作，这个时候俩次当前读的结果应该是不一致的，但是实际的情况却是在当前读的这个事务还没提交之前，所有针对当前读的数据修改和插入都会被阻塞，主要是因为next-key lock解决了当前读可能会发生幻读的情况。
next-key lock当使用主键索引进行当前读的时候，会降级为record lock(行锁)

9.2 Read view详析

InnoDB支持MVCC多版本控制，其中READ COMMITTED和REPEATABLE READ隔离级别是利用consistent read view(一致读视图)方式支持的。所谓的consistent read view就是在某一时刻给事务系统trx_sys打snapshot(快照)，把当时的trx_sys状态(包括活跃读写事务数组)记下来，之后的所有读操作根据其事务ID(即trx_id)与snapshot中trx_sys的状态做比较，以此判断read view对事务的可见性。
REPEATABLE READ隔离级别(除了GAP锁之外)和READ COMMITTED隔离级别的差别是创建snapshot时机不同。REPEATABLE READ隔离级别是在事务开始时刻，确切的说是第一个读操作创建read view的时候,READ COMMITTED隔离级别是在语句开始时刻创建read view的。这就意味着REPEATABLE READ隔离级别下面一个事务的SELECT操作只会获取一个read view，但是READ COMMITTED隔离级别下一个事务是可以获取多个read view的。
创建／关闭read view需要持有trx_sys->mutex，会降低系统性能，5.7版本对此进行优化，在事务提交时session会cache只读事务的read view。

9.3 read view 判断当前版本数据项是否可见

在InnoDB中，创建一个新事务的时候，InnoDB会将当前系统中的活跃事务列表（trx_sys->trx_list）创建一个副本（read view），副本中保存的是系统当前不应该被本事务看到的其他事务id列表。当用户在这个事务中要读取该行记录的时候，InnoDB会将该行当前的版本号与该read view进行比较。
具体的算法如下:
设该行的当前事务id为trx_id，read view中最早的事务id为trx_id_min, 最迟的事务id为trx_id_max。
如果trx_id如果trx_id>trx_id_max的话，那么表明该行记录所在的事务在本次新事务创建之后才开启，所以该行记录的当前值不可见。
如果trx_id_min

从该行记录的DB_ROLL_PTR指针所指向的回滚段中取出最新的undo-log的版本号的数据，将该可见行的值返回。
需要注意的是，新建事务(当前事务)与正在内存中commit 的事务不在活跃事务链表中。
在具体多版本控制中我们先来看下源码：

函数：read_view_sees_trx_id。
read_view中保存了当前全局的事务的范围：
【low_limit_id， up_limit_id】

1.当行记录的事务ID小于当前系统的最小活动id，就是可见的。
    　　if (trx_id up_limit_id) {
        　　　　return(TRUE);
        　　}
2.当行记录的事务ID大于当前系统的最大活动id（也就是尚未分配的下一个事务的id），就是不可见的。
    　　if (trx_id >= view->low_limit_id) {
        　　　　return(FALSE);
        　　}
3.当行记录的事务ID在活动范围之中时，判断是否在活动链表中，如果在就不可见，如果不在就是可见的。
    　　for (i = 0; i <p><strong>Read view 图解</strong>：<br></p><p><img src="/static/imghw/default1.png" data-src="https://img.php.cn/upload/image/428/162/665/1553653110536794.jpg" class="lazy" title="1553653110536794.jpg" alt="Detailed introduction to transaction isolation and MVCC of MySQL database (picture and text)"></p><p   style="max-width:90%">结语：笔者水平有限，文中如有不妥，请大家多多指教，MySQL数据库事务机制还有很多需要深入研究的，我们仍需不断钻研。</p><p>本篇文章到这里就已经全部结束了，更多其他精彩内容可以关注PHP中文网的<a href="http://www.php.cn/course/list/51.html" target="_blank">MySQL视频教程</a>栏目！</p><p></p>

The above is the detailed content of Detailed introduction to transaction isolation and MVCC of MySQL database (picture and text). For more information, please follow other related articles on the PHP Chinese website!