What is the MVCC principle of MySQL InnoDB?-Mysql Tutorial-php.cn

Table of Contents

Version chain

ReadView

READ COMMITTED每次读取数据前都生成一个ReadView

REPEATABLE READ —— 在第一次读取数据时生成一个ReadView

MVCC下的幻读现象和幻读解决

MVCC Summary

Home

Database

Mysql Tutorial

What is the MVCC principle of MySQL InnoDB?

王林

May 30, 2023 pm 01:20 PM

mysql innodb mvcc

MySQL InnoDB之MVCC原理是什么

MVCC's full name is Multi-Version Concurrency Control, which is multi-version concurrency control, mainly to improve the concurrency performance of the database. When a read or write request occurs for the same row of data, it will be locked and blocked. MVCC uses a more optimized method to handle read and write requests, and can handle read and write request conflicts without locking. This refers to snapshot reading, not current reading, which is a pessimistic locking mechanism. We will learn in the following studies how to perform read and write operations without locking, and the concepts of snapshot read and current read will also be analyzed.

MySQL can largely avoid phantom read problems under the REPEATABLE READ isolation level. How does MySQL do this?

Version chain

We know that for tables using the InnoDB storage engine, its clustered index records contain two necessary hidden columns (row_id It is not necessary. The row_id column will not be included when the table we create has a primary key or a non-NULL UNIQUE key):

trx_id: Each time a transaction clusters a certain When the index record is modified, the transaction ID of the transaction will be assigned to the trx_id hidden column.
roll_pointer: Every time a clustered index record is changed, the old version will be written to the undo log, and then this hidden column is equivalent to a pointer. Use it to find the information before the record was modified.

In order to illustrate this problem, we create a demonstration table:

CREATE TABLE `teacher` (
  `number` int(11) NOT NULL,
  `name` varchar(100) DEFAULT NULL,
  `domain` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`number`)) ENGINE=InnoDB DEFAULT CHARSET=utf8

Copy after login

Then insert a piece of data into this table:

mysql> insert into teacher values(1, &#39;J&#39;, &#39;Java&#39;);Query OK, 1 row affected (0.01 sec)

Copy after login

The current data This is it:

mysql> select * from teacher;
+--------+------+--------+
| number | name | domain |
+--------+------+--------+
|      1 | J    | Java   |
+--------+------+--------+
1 row in set (0.00 sec)

Copy after login

Assume that the transaction ID of inserting the record is 60, then the diagram of the record at this moment is as follows:

MySQL InnoDB之MVCC原理是什么

Assume that the next two A transaction with transaction IDs of 80 and 120 performs an UPDATE operation on this record. The operation process is as follows:

##beginupdate teacher set name='S' where number=1;##update teacher set name='T' where number=1;##commit##update teacher set name='K' where number=1;##update teacher set name='F' where number =1;##commit

Trx80

Trx120

begin

每次对记录进行改动，都会记录一条undo日志，每条undo日志也都有一个roll_pointer属性（INSERT操作对应的undo日志没有该属性，因为该记录并没有更早的版本），可以将这些undo日志都连起来，串成一个链表，所以现在的情况就像下图一样：

MySQL InnoDB之MVCC原理是什么

对该记录每次更新后，都会将旧值放到一条undo日志中，就算是该记录的一个旧版本，随着更新次数的增多，所有的版本都会被roll_pointer属性连接成一个链表，我们把这个链表称之为版本链，版本链的头节点就是当前记录最新的值。另外，每个版本中还包含生成该版本时对应的事务id。于是可以利用这个记录的版本链来控制并发事务访问相同记录的行为，那么这种机制就被称之为多版本并发控制(Mulit-Version Concurrency Control MVCC)。

ReadView

对于使用READ UNCOMMITTED隔离级别的事务来说，由于可以读到未提交事务修改过的记录，所以直接读取记录的最新版本就好了。

对于使用SERIALIZABLE隔离级别的事务来说，InnoDB使用加锁的方式来访问记录。

对于使用READ COMMITTED和REPEATABLE READ隔离级别的事务来说，都必须保证读到已经提交了的事务修改过的记录，也就是说假如另一个事务已经修改了记录但是尚未提交，是不能直接读取最新版本的记录的，核心问题就是：READ COMMITTED和REPEATABLE READ隔离级别在不可重复读和幻读上的区别，这两种隔离级别关键是需要判断一下版本链中的哪个版本是当前事务可见的。

为此，InnoDB提出了一个ReadView的概念，这个ReadView中主要包含4个比较重要的内容：

m_ids：表示在生成ReadView时当前系统中活跃的读写事务的事务id列表。
min_trx_id：表示在生成ReadView时当前系统中活跃的读写事务中最小的事务id，也就是m_ids中的最小值。
max_trx_id: 表示系统应该分配给下一个事务的ID值，以便在生成ReadView时使用。请注意，max_trx_id不一定是m_ids中的最大值，因为事务id是递增分配的。例如，假设有三个事务分别为ID 1、2、3，在ID 3的事务提交后。那么一个新的读事务在生成ReadView时，m_ids就包括1和2，min_trx_id的值就是1，max_trx_id的值就是4。
creator_trx_id：表示生成该ReadView的事务的事务id。

有了这个ReadView，这样在访问某条记录时，只需要按照下边的步骤判断记录的某个版本是否可见：

如果被访问版本的trx_id属性值与ReadView中的creator_trx_id值相同，意味着当前事务在访问它自己修改过的记录，所以该版本可以被当前事务访问。
如果被访问版本的trx_id属性值小于ReadView中的min_trx_id值，表明生成该版本的事务在当前事务生成ReadView前已经提交，所以该版本可以被当前事务访问。
如果被访问版本的trx_id属性值大于或等于ReadView中的max_trx_id值，表明生成该版本的事务在当前事务生成ReadView后才开启，所以该版本不可以被当前事务访问。
如果被访问版本的trx_id属性值在ReadView的min_trx_id和max_trx_id之间(min_trx_id <= trx_id < max_trx_id)，那就需要判断一下trx_id属性值是不是在m_ids列表中，如果在，说明创建ReadView时生成该版本的事务还是活跃的，事务还没提交，该版本不可以被访问；如果不在，说明创建ReadView时生成该版本的事务已经被提交，该版本可以被访问。
如果某个版本的数据对当前事务不可见的话，那就顺着版本链找到下一个版本的数据，继续按照上边的步骤判断可见性，依此类推，直到版本链中的最后一个版本。如果最新的版本也无法查看，则该记录对该事务是完全不可见的，查询结果将不包含该记录。

在MySQL中，READ COMMITTED和REPEATABLE READ隔离级别的的一个非常大的区别就是它们生成ReadView的时机不同。

我们还是以表teacher为例，假设现在表teacher中只有一条由事务id为60的事务插入的一条记录，接下来看一下READ COMMITTED和REPEATABLE READ所谓的生成ReadView的时机不同到底不同在哪里。

READ COMMITTED每次读取数据前都生成一个ReadView

假设现在系统里有两个事务id分别为80、120的事务在执行：

# Transaction 80
set session transaction isolation level read committed;
begin
update teacher set name=&#39;S&#39; where number=1;
update teacher set name=&#39;T&#39; where number=1;

Copy after login

此刻，表teacher中number为1的记录得到的版本链表如下所示：

MySQL InnoDB之MVCC原理是什么

假设现在有一个使用READ COMMITTED隔离级别的事务开始执行：

set session transaction isolation level read committed;
# 使用READ COMMITTED隔离级别的事务
begin;
# SELECE1：Transaction 80、120未提交
SELECT * FROM teacher WHERE number = 1; # 得到的列name的值为&#39;J&#39;

Copy after login

这个SELECE1的执行过程如下：

在执行SELECT语句时会先生成一个ReadView，ReadView的m_ids列表的内容就是[80, 120]，min_trx_id为80，max_trx_id为121，creator_trx_id为0。
然后从版本链中挑选可见的记录，最新版本的列name的内容是’T’，该版本的trx_id值为80，在m_ids列表内，根据步骤4不符合可见性要求，根据roll_pointer跳到下一个版本。
下一个版本的列name的内容是’S’，该版本的trx_id值也为80，也在m_ids列表内，根据步骤4也不符合要求，继续跳到下一个版本。
下一个版本的列name的内容是’J’，该版本的trx_id值为60，小于ReadView 中的min_trx_id值，根据步骤2判断这个版本是符合要求的。

之后，我们把事务id为80的事务提交一下，然后再到事务id为120的事务中更新一下表teacher 中number为1的记录：

set session transaction isolation level read committed;
# Transaction 120
begin
update teacher set name=&#39;K&#39; where number=1;
update teacher set name=&#39;F&#39; where number=1;

Copy after login

此刻，表teacher 中number为1的记录的版本链就长这样：

MySQL InnoDB之MVCC原理是什么

然后再到刚才使用READ COMMITTED隔离级别的事务中继续查找这个number 为1的记录，如下：

# 使用READ COMMITTED隔离级别的事务
begin;
# SELECE1：Transaction 80、120未提交
SELECT * FROM teacher WHERE number = 1; # 得到的列name的值为&#39;J&#39;
# SELECE2：Transaction 80提交、120未提交
SELECT * FROM teacher WHERE number = 1; # 得到的列name的值为&#39;T&#39;

Copy after login

这个SELECE2 的执行过程如下：

在执行SELECT语句时会又会单独生成一个ReadView，该ReadView的m_ids列表的内容就是[120]（事务id为80的那个事务已经提交了，所以再次生成快照时就没有它了），min_trx_id为120，max_trx_id为121，creator_trx_id为0。
然后从版本链中挑选可见的记录，从图中可以看出，最新版本的列name的内容是’F’，该版本的trx_id值为120，在m_ids列表内，根据步骤4不符合可见性要求，根据roll_pointer跳到下一个版本。
下一个版本的列name 的内容是’K’，该版本的trx_id值为120，也在m_ids列表内，根据步骤4不符合可见性要求，根据roll_pointer跳到下一个版本。
下一个版本的列name的内容是’T’，该版本的trx_id值为80，小于ReadView中的min_trx_id值120，表明生成该版本的事务在当前事务生成ReadView前已经提交，所以这个版本是符合要求的，最后返回给用户的版本就是这条列name为’‘T’'的记录。

以此类推，如果之后事务id为120的记录也提交了，再次在使用READCOMMITTED隔离级别的事务中查询表teacher中number值为1的记录时，得到的结果就是’F’了，具体流程我们就不分析了。

总结一下就是：使用READCOMMITTED隔离级别的事务在每次查询开始时都会生成一个独立的ReadView。

REPEATABLE READ —— 在第一次读取数据时生成一个ReadView

对于使用REPEATABLE READ隔离级别的事务来说，只会在第一次执行查询语句时生成一个ReadView，之后的查询就不会重复生成了。我们还是用例子看一下是什么效果。

假设现在系统里有两个事务id分别为80、120的事务在执行：

# Transaction 80
begin
update teacher set name=&#39;S&#39; where number=1;
update teacher set name=&#39;T&#39; where number=1;

Copy after login

此刻，表teacher中number为1的记录得到的版本链表如下所示：

MySQL InnoDB之MVCC原理是什么

假设现在有一个使用REPEATABLE READ隔离级别的事务开始执行：

# 使用REPEATABLE READ隔离级别的事务
begin;
# SELECE1：Transaction 80、120未提交
SELECT * FROM teacher WHERE number = 1; # 得到的列name的值为&#39;J&#39;

Copy after login

这个SELECE1的执行过程如下（与READ COMMITTED的过程一致）：

在执行SELECT语句时会先生成一个ReadView，ReadView的m_ids列表的内容就是[80, 120]，min_trx_id为80，max_trx_id为121，creator_trx_id为0。
然后从版本链中挑选可见的记录，最新版本的列name的内容是’T’，该版本的trx_id值为80，在m_ids列表内，根据步骤4不符合可见性要求，根据roll_pointer跳到下一个版本。
下一个版本的列name的内容是’S’，该版本的trx_id值也为80，也在m_ids列表内，根据步骤4也不符合要求，继续跳到下一个版本。
下一个版本的列name的内容是’J’，该版本的trx_id值为60，小于ReadView 中的min_trx_id值，根据步骤2判断这个版本是符合要求的。

之后，我们把事务id为80的事务提交一下，然后再到事务id为120的事务中更新一下表teacher 中number为1的记录：

# Transaction 80
begin
update teacher set name=&#39;K&#39; where number=1;
update teacher set name=&#39;F&#39; where number=1;

Copy after login

此刻，表teacher 中number为1的记录的版本链就长这样：

MySQL InnoDB之MVCC原理是什么

然后再到刚才使用REPEATABLE READ隔离级别的事务中继续查找这个number为1的记录，如下：

# 使用REPEATABLE READ隔离级别的事务
begin;
# SELECE1：Transaction 80、120未提交
SELECT * FROM teacher WHERE number = 1; # 得到的列name的值为&#39;J&#39;
# SELECE2：Transaction 80提交、120未提交
SELECT * FROM teacher WHERE number = 1; # 得到的列name的值为'J'

Copy after login

这个SELECE2的执行过程如下：

因为当前事务的隔离级别为REPEATABLE READ，而之前在执行SELECE1时已经生成过ReadView了，所以此时直接复用之前的ReadView，之前的ReadView的m_ids列表的内容就是[80, 120]，min_trx_id为80，max_trx_id为121，creator_trx_id为0。
然后从版本链中挑选可见的记录，从图中可以看出，最新版本的列name的内容是’F’，该版本的trx_id值为120，在m_ids列表内，根据步骤4不符合可见性要求，根据roll_pointer跳到下一个版本。
下一个版本的列name的内容是’K’，该版本的trx_id值为120，也在m_ids列表内，根据步骤4不符合可见性要求，根据roll_pointer跳到下一个版本。
下一个版本的列name的内容是’T’，该版本的trx_id值为80，也在m_ids列表内，根据步骤4不符合可见性要求，根据roll_pointer跳到下一个版本。
下一个版本的列name的内容是’S’，该版本的trx_id值为80，也在m_ids列表内，根据步骤4不符合可见性要求，根据roll_pointer跳到下一个版本。
下一个版本的列name的内容是’J’，该版本的trx_id值为60，小于ReadView中的min_trx_id值80，表明生成该版本的事务在当前事务生成ReadView前已经提交，所以这个版本是符合要求的，最后返回给用户的版本就是这条列name为’‘J’'的记录。

可重复读的意思是两次SELECT查询的结果相同，记录的列值均为'J'。

如果我们之后再把事务id为120的记录提交了，然后再到刚才使用REPEATABLE READ隔离级别的事务中继续查找这个number为1的记录，得到的结果还是’J’，具体执行过程大家可以自己分析一下。

MVCC下的幻读现象和幻读解决

前面我们已经知道了，REPEATABLE READ隔离级别下MVCC可以解决不可重复读问题，那么幻读呢？MVCC是怎么解决的？幻读是一个事务按照某个相同条件多次读取记录时，后读取时读到了之前没有读到的记录，而这个记录来自另一个事务添加的新记录。

我们可以想想，在REPEATABLE READ隔离级别下的事务T1先根据某个搜索条件读取到多条记录，然后事务T2插入一条符合相应搜索条件的记录并提交，然后事务T1再根据相同搜索条件执行查询。结果会是什么？按照ReadView中的比较规则：

无论事务T2是否先于事务T1开启，事务T1都无法观察到T2的提交。请根据以上所述的版本历史、阅读视图与可视性判断规则，自行进行分析。

但是，在REPEATABLE READ隔离级别下InnoDB中的MVCC可以很大程度地避免幻读现象，而不是完全禁止幻读。怎么回事呢？我们来看下面的情况：

T1	T2
begin;
select * from teacher where number=30; 无数据	begin;
	insert into teacher values(30, ‘X’, ‘Java’);
	commit;
update teacher set domain=‘MQ’ where number=30;
select * from teacher where number = 30; 有数据

Well, what's going on? Transaction T1 obviously has a phantom read phenomenon. Under the REPEATABLE READ isolation level, T1 generates a ReadView when executing a normal SELECT statement for the first time, and then T2 inserts a new record into the teacher table and submits it. ReadView cannot prevent T1 from executing the UPDATE or DELETE statement to modify the newly inserted record (since T2 has already submitted, changing the record will not cause blocking), but in this way, the value of the trx_id hidden column of this new record will be It becomes the transaction id of T1. After that, T1 can see this record when it uses an ordinary SELECT statement to query this record, and can return this record to the client. MVCC cannot completely eliminate phantom reads because of the existence of this special phenomenon.

MVCC Summary

We can see from the above description that the so-called MVCC (Multi-Version ConcurrencyControl, multi-version concurrency control) refers to the use of READ COMMITTD and REPEATABLE READ. This isolation level transaction accesses the recorded version chain when performing a normal SELECT operation. This allows read-write and write-read operations of different transactions to be executed concurrently, thus improving system performance.

A big difference between the two isolation levels of READ COMMITTD and REPEATABLE READ is that the timing of generating ReadView is different. READ COMMITTD will generate a ReadView before each ordinary SELECT operation, while REPEATABLE READ will only generate a ReadView after the normal SELECT operation. Just generate a ReadView before performing a normal SELECT operation, and reuse this ReadView for subsequent query operations, thus basically avoiding the phenomenon of phantom reading.

We said before that executing a DELETE statement or an UPDATE statement that updates the primary key will not immediately completely delete the corresponding record from the page. Instead, it will perform a so-called delete mark operation, which is equivalent to just marking the record with a mark. Delete the flag bit, which mainly serves MVCC. In addition, the so-called MVCC only takes effect when we perform ordinary SEELCT queries. All SELECT statements we have seen so far are ordinary queries. As for what is an extraordinary query, we will talk about it later.

The above is the detailed content of What is the MVCC principle of MySQL InnoDB?. For more information, please follow other related articles on the PHP Chinese website!

Statement of this Website

The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Hot AI Tools

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress images for free

Clothoff.io

AI clothes remover

Video Face Swap

Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Article

How to fix KB5055518 fails to install in Windows 10?

1 months ago By DDD

How to fix KB5055523 fails to install in Windows 11?

1 months ago By DDD

Roblox: Grow A Garden - Complete Mutation Guide

3 weeks ago By DDD

Roblox: Bubble Gum Simulator Infinity - How To Get And Use Royal Keys

3 weeks ago By 尊渡假赌尊渡假赌尊渡假赌

How to fix KB5055612 fails to install in Windows 10?

3 weeks ago By DDD

Hot Tools

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

God-level code editing software (SublimeText3)

Hot Topics

Java Tutorial

1664

CakePHP Tutorial

1423

Laravel Tutorial

1317

PHP Tutorial

1268

C# Tutorial

1246

Related knowledge

Laravel Introduction Example Apr 18, 2025 pm 12:45 PM

Laravel is a PHP framework for easy building of web applications. It provides a range of powerful features including: Installation: Install the Laravel CLI globally with Composer and create applications in the project directory. Routing: Define the relationship between the URL and the handler in routes/web.php. View: Create a view in resources/views to render the application's interface. Database Integration: Provides out-of-the-box integration with databases such as MySQL and uses migration to create and modify tables. Model and Controller: The model represents the database entity and the controller processes HTTP requests.

MySQL and phpMyAdmin: Core Features and Functions Apr 22, 2025 am 12:12 AM

MySQL and phpMyAdmin are powerful database management tools. 1) MySQL is used to create databases and tables, and to execute DML and SQL queries. 2) phpMyAdmin provides an intuitive interface for database management, table structure management, data operations and user permission management.

MySQL vs. Other Programming Languages: A Comparison Apr 19, 2025 am 12:22 AM

Compared with other programming languages, MySQL is mainly used to store and manage data, while other languages such as Python, Java, and C are used for logical processing and application development. MySQL is known for its high performance, scalability and cross-platform support, suitable for data management needs, while other languages have advantages in their respective fields such as data analytics, enterprise applications, and system programming.

Solve database connection problem: a practical case of using minii/db library Apr 18, 2025 am 07:09 AM

I encountered a tricky problem when developing a small application: the need to quickly integrate a lightweight database operation library. After trying multiple libraries, I found that they either have too much functionality or are not very compatible. Eventually, I found minii/db, a simplified version based on Yii2 that solved my problem perfectly.

Laravel framework installation method Apr 18, 2025 pm 12:54 PM

Article summary: This article provides detailed step-by-step instructions to guide readers on how to easily install the Laravel framework. Laravel is a powerful PHP framework that speeds up the development process of web applications. This tutorial covers the installation process from system requirements to configuring databases and setting up routing. By following these steps, readers can quickly and efficiently lay a solid foundation for their Laravel project.

MySQL for Beginners: Getting Started with Database Management Apr 18, 2025 am 12:10 AM

The basic operations of MySQL include creating databases, tables, and using SQL to perform CRUD operations on data. 1. Create a database: CREATEDATABASEmy_first_db; 2. Create a table: CREATETABLEbooks(idINTAUTO_INCREMENTPRIMARYKEY, titleVARCHAR(100)NOTNULL, authorVARCHAR(100)NOTNULL, published_yearINT); 3. Insert data: INSERTINTObooks(title, author, published_year)VA

Solve MySQL mode problem: The experience of using the TheliaMySQLModesChecker module Apr 18, 2025 am 08:42 AM

When developing an e-commerce website using Thelia, I encountered a tricky problem: MySQL mode is not set properly, causing some features to not function properly. After some exploration, I found a module called TheliaMySQLModesChecker, which is able to automatically fix the MySQL pattern required by Thelia, completely solving my troubles.

MySQL: Structured Data and Relational Databases Apr 18, 2025 am 12:22 AM

MySQL efficiently manages structured data through table structure and SQL query, and implements inter-table relationships through foreign keys. 1. Define the data format and type when creating a table. 2. Use foreign keys to establish relationships between tables. 3. Improve performance through indexing and query optimization. 4. Regularly backup and monitor databases to ensure data security and performance optimization.

See all articles