Shulou(Shulou.com)06/02 Report--

How is MVCC in MySQL? I believe many inexperienced people don't know what to do about it. Therefore, this paper summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

Preface

MySQL is one of the popular open source databases, companies use MySQL as their own relational database, but MySQL as a database, the basic use is very simple, as long as a little bit of building table statements (you can use tools to build tables), a little query statements can use MySQL to store data.

This soulless operation may have become commonplace for many beginners. But for some people who already have development experience, this is far from enough. You have to learn a lot about databases, and this article is a thorough analysis of how MVCC in MySQL is implemented. After reading this article, you can know what the role of MVCC is under various isolation levels. When will MVCC be used? How do I use it?

Sample table CREATE TABLE `test`.`Untitled` (

`id`int (10) UNSIGNED NOT NULL AUTO_INCREMENT

`phone` char (11) NOT NULL

`name` varchar (255) NOT NULL

`age`int (3) NOT NULL

`roomy` varchar (255) NOT NULL

PRIMARY KEY (`id`) USING BTREE

UNIQUE INDEX `uk_ phone` (`phone`) USING BTREE

INDEX `idx_ name` (`name`) USING BTREE

) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic

Copy the code insert into person values (null, '1351111111', 'any', 20,' Shu')

Insert into person values (null, '1351111112,' bat', 21,'Wu')

Copy the code idphonenameagecountry11351111111any20 Shu 21351111112bat21 Wu MVCC

MVCC is an implementation of lock-free operation. No lock means no lock. No lock can greatly improve the concurrency of the database. Its most basic form of expression is consistent unlocked read, which is realized by MVCC (multi-version concurrency control). MVCC is mainly realized by Read View.

There are actually three hidden columns for every record in the database:

DB_TRX_ID: this column represents the transaction ID for this record

DB_ROLL_PTR: this column represents a pointer to the rollback segment, which is actually a chain of versions pointing to the record

DB_ROW_ID: the ID of the record. If there is a specified primary key, then this value is the primary key. If there is no primary key, the first unique index defined will be used. If there is no unique index, a value is generated by default.

Start transaction

Update person set age = 22 where id = 1

Update person set name = 'out' where id = 1

Commit

Copy the code

When the above two statements are executed, but before the transaction is committed, there is no exception in its version chain.

Read View is used to determine which record in the version chain each read statement is eligible to read. So a Read View is generated before it is read. Then read the record according to the generated Read View.

In a transaction, a transaction id is assigned only when insert, update, or delete operations are performed. If a transaction is simply a read transaction, then its transaction id is 0 by default.

The structure of Read View is as follows:

Rw_trx_ids: represents the currently active array of read and write transactions when the Read View is generated.

Min_trx_id: indicates the transaction number + 1 that is currently committed when the Read View is generated, which is the minimum transaction number in the rw_trx_ids.

Max_trx_id: indicates the currently assigned transaction number + 1 when the Read View is generated, that is, the transaction number to be assigned to the next transaction.

Curr_trx_id: creates the current transaction id of the Read View.

MySQL determines which version (record) in the version chain is visible in the transaction according to the following rules:

Trx_id

< min_trx_id，那么该记录则在当前事务可见，因为修改该版本记录的事务在当前事务生成 Read View 之前就已经提交。 trx_id in (rw_trx_ids)，那么该记录在当前事务不可见，因为需改该版本记录的事务在当前事务生成 Read View 之前还未提交。 trx_id >

Max_trx_id, then the record is not visible in the current transaction because the transaction that modified this version of the record was not opened before the current transaction generated Read View.

Trx_id = curr_trx_id, then the record is visible in the current transaction, because the transaction that modified this version of the record is the current transaction.

We first started a read transaction in step 1, because it is a read-only transaction, so its transaction id is 0 (hereinafter referred to as transaction 0). Then we query the record with id 1 in transaction 0.

Note: records connected to red headers are in the B+ tree, while records connected by roll_ptr pointers exist in undo log. All of the following version chains are in this form.

READ UNCOMMITTED

This isolation level does not use MVCC. As long as it executes select, it will get the latest records on the B+ tree. Regardless of whether the recorded transaction has been committed or not.

READ COMMITTED

Under the READ COMMITTED isolation level, MVCC is used. After a read transaction is started, it generates a Read View before each select operation.

Because when the select in step 2 is read, there are no active transactions, which means that all transactions have been committed. So it can read the first record.

Perform step 3 to open a new transaction with a transaction id of 101 (below detect transaction 101).

Perform step 4, which modifies the record with id 1, at which point the version chain

In step 5, transaction 0 performs a select operation, and transaction 0 generates a Read View.

We follow the above rules for record visibility in the version chain:

The first record in the version chain whose trx_id is not less than min_trx_id, so the record is not visible.

The second record in the version chain, whose trx_id is less than min_trx_id, is visible.

So for this query, the record it can get is:

Transaction 101 performs an update operation in step 5, performs step 6, and commits the transaction.

In step 7, we execute a select query in transaction 0, which also generates a Read View because our isolation level is READ COMMITTED.

Then according to the version chain visibility rules:

Because there are no active transactions, you know that all transactions have been committed, so rw_trx_ids is empty.

The first record of the version chain, whose trx_id is less than min_trx_id, is visible.

Then the records that can be obtained by this query. After transaction 0 executes the query, we open a new transaction with transaction id 102 (hereinafter referred to as transaction 102), which also updates the record with id 1.

The query in step 9 analyzes itself. We directly give the final version chain in which transaction 102 executes two update statements.

In step 11, according to the version chain visibility rules, it can obtain the records:

REPEATABLE READ

In fact, the only difference between REPEATABLE READ and READ COMMITTED is when Read View is generated.

READ COMMITTED generates a new Read View each time a select operation is performed. REPEATABLE READ only generates a Read View the first time the select operation is executed, and all select operations use the first generated Read View until the transaction commits.

Let's re-perform the steps in the table.

First, execute to step 2, after transaction 0 starts the transaction, and executes a select query. A Read View is generated. The Read View is structured as follows:

The generated Read View will be used until transaction 0 commits.

So, although two transactions are opened later and the record is modified so that the final version chain looks like this:

However, transaction 0 can still only read the original record.

Whenever transaction 0 executes select * from person where id = 1; to read records, it will only use the Read View generated for the first time to select records that can be read in the version chain.

SERIALIZABLE

This isolation level does not use MVCC. If you are using a normal select statement, it will add lock in share mode to the statement and become a consistent locked read. If a transaction reads a record, changes made by other transactions to that record will be blocked. If a transaction is changing a record, the reading of that record by other transactions will be blocked.

At this isolation level, read and write operations become serial operations.

Summary

From the above article, we know that MVCC is only used below the READ COMMITTED and REPEATABLE READ isolation levels.

But READ COMMITTED and REPEATABLE READ use MVCC differently:

READ COMMITTED generates a Read View every time a select operation is performed.

REPEATABLE READ generates Read View only when the first select operation is performed, and subsequent select operations will use the Read View generated for the first time.

MVCC is not used for READ UNCOMMITTED and SERIALIZABLE isolation levels.

Their read operations are also different:

Every time READ UNCOMMITTED executes select, it reads the latest record.

Every time SERIALIZABLE performs a select operation, it adds a lock in share mode to the statement, making the select a consistent locked read and serializing the read and write.

After reading the above, have you mastered the method of MVCC in MySQL? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

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.