What is the MVCC mechanism in MySQL

1. Overview:

MVCC, the full name is Multi-Version Concurrency Control, which is multi-version concurrency control. MVCC is a multi-concurrency control method. It is generally used in database management systems to achieve concurrent access to the database and to implement transactional memory in programming languages.
We know that MySql changed from MyISAM storage engine to InnoDB storage engine after 5.5, mainly because InnoDB supports transactions, so when multiple threads are executed at the same time, concurrency problems may occur. At this time, a method that can control concurrency may appear, and MVCC plays this role.

MVCC is mainly implemented by undo log version chain and ReadView.

2. What is Undo log

• Undo log is mainly used to restore the original data when a transaction is rolled back.

• When mysql executes sql, it will save the logically opposite log of one day to the undo log. Therefore, what is recorded in the undo log is also a logical log.

• But when mysql executes the Insert statement, the primary key id inserted this time will be recorded in the undo log. When the transaction is rolled back, delete deletes this id.

• When executing the update statement, MySQL will save the data before modification in the undo log. When the transaction is rolled back, update is performed again to obtain the original data.

• When MySQL executes the delete statement, the data before deletion will be saved in the undo log. When the transaction is rolled back, execute insert again and insert the original data.

• The four major characteristics of the database - atomicity, that is, transactions are indivisible, either all succeed or all fail, and the bottom layer is achieved by undo log. When the execution of a certain statement fails, the statements of the previous transaction will be rolled back.

3. Hidden columns of rows

• In each row of the database, in addition to storing the real data, there are also 3 hidden columns Columns: row_id, trx_id and roll_pointer

• row_id, row number:

If the current table has a primary key of integer type, then row_id The value is the value of the primary key
If there is no primary key of integer type, MySQL will select a non-empty unique index of integer type as row_id according to the order of the fields
If neither is found, an automatically growing one will be created Integer as row_id

• trx_id, transaction number:

When a transaction starts to be executed, MySQL will Transactions are assigned a globally incremented transaction ID.
Later, when the transaction performs operations such as addition, modification, or deletion, it will record its own transaction ID in trx_id.

• roll_pointer, rollback pointer:

When a transaction changes the current data, the old data will be recorded to In undo log, data is written to the current row, and the current roll_pointer points to the undo log just now, so the roll_pointer can be used to find the previous version of the row.
When there are transactions changing the row, undo log will be generated continuously, and an undo log version chain will eventually be formed.

4. Undo log version chain

At the beginning, we use the following statement to create a stduent table

CREATE TABLE `student` (
	`id` INT ( 11 ) NOT NULL AUTO_INCREMENT,
	`name` VARCHAR ( 255 ) NOT NULL,
	`age` INT ( 11 ) NOT NULL,
  PRIMARY KEY ( `id` ) USING BTREE 
) ENGINE = INNODB;

Now open the first transaction, the transaction id is 1 , execute the following insert statement.

INSERT INTO student VALUES ( 1, "a", 24 );

Then the current diagram is as follows:

Because the data is newly inserted, the undo log pointed to by its roll_pointer is empty.

Then open the second transaction, the assigned transaction ID is 2, and execute the following modification command.

UPDATE student SET NAME = 'b' WHERE id = 1;

The current diagram changes to:

When the third transaction is opened and the transaction ID assigned is 3, execute the following modification command.

UPDATE student SET age = 25 WHERE id = 1;

The diagram becomes:

#When each transaction changes the row, an undo log will be generated to save the previous version. Then point the new version of roll_pointer to the undo log just generated.
Therefore, roll_pointer can connect these different versions of undo log in series to form a version chain of undo log.

5. About ReadView

First of all, you need to understand snapshot reading and current reading
Snapshot reading: simple select query, that is, it does not include select … lock in share mode, select … for update, you may read the historical version of the data.
Current reading: The following statements are all current reading, always read the latest version, and lock the latest version read.

select ... lock in share mode
select ... for update
insert
update
delete

When a transaction performs each snapshot read or when a transaction performs a snapshot read for the first time, a consistent view, namely ReadView, will be generated.
The function of ReadView is to determine which data in the undo log version chain is visible to the current transaction.

ReadView contains the following important parameters:

• m_ids

• At the moment when ReadView is created, A collection of all uncommitted transaction IDs in mysql.

• min_trx_id

• m_ids中的最小值

• max_trx_id

• mysql即将为下一个事务分配的事务id，并不是m_ids中的最大值。

• creator_trx_id

• 即创建此ReadView的事务id

简要的示意图如下：

那么事务在执行快照读时，可以通过以下的规则来确定undo log版本链上的哪个版本数据可见。

• 如果当前undo log的版本的trx_id

• 如果当前undo log的版本的trx_id≥max_trx_id，说明该版本对应的事务在生成ReadView之后才开始的，因此是不可见的。

• 如果当前undo log的版本的trx_id∈[min_trx_id，max_trx_id)，如果在这个范围里，还要判断trx_id是否在m_ids中：

  在m_ids中，说明版本对应的事务未提交，因此是不可见的。

  不在m_ids中，说明版本对应的事务已经提交，因此是可见的。

• 如果当前undo log的版本的trx_id=creator_trxt_id，说明事务正在访问自己修改的数据，因此是可见的。

• 当undo log版本链表的头结点数据被判定为不可见时，则利用roll_pointer找到上一个版本，再进行判断。如果整个链表中都没有找到可见的数据，则代表当前的查询找不到数据。

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