preface

MySQL is one of the popular open source databases at present. Major companies use MySQL as their own relational database. However, as a database, MySQL is very simple to use, as long as you can build tables a little bit (you can use tools to build tables). A few queries can be used to store data in MySQL.

This kind of soulless operation may have been routine for many beginners. But for some people who already have development experience, this is not enough. You have to learn a lot of database related knowledge, and this article is a thorough analysis of MySQL MVCC implementation. After reading this article, you should know what the role of MVCC is at various isolation levels. When will MVCC be used? How to use it?

The 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,
  `country` 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, 'lock '); Insert into person values (null, '1351111112', 'bat', 21, 'u ');Copy the code
id phone name age country
1 1351111111 any 20 shu
2 1351111112 bat 21 wu

MVCC

MVCC is an implementation of lockless operation, which means no lock. Lock-free can greatly improve database concurrency. Its most basic form is consistent non-locked reads, which are implemented through MVCC (Multi-version Concurrency Control). MVCC is implemented mainly by Read View.

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

  • DB_TRX_ID: This column represents the transaction ID of this record
  • DB_ROLL_PTRThis column represents a pointer to the rollback segment, which is actually a version chain of the record
  • DB_ROW_ID: The ID of the record, which is the primary key if one is specified. If there is no primary key, the first unique index defined is 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

After executing the above two statements, but before committing the transaction, its version chain looks like this:

The Read View is used to determine which record in the version chain each Read statement is eligible to Read. So before reading, a Read View is generated. The record is then Read based on the generated Read View.

In a transaction, a transaction ID is assigned only when an insert, update, or delete operation is performed. If the transaction is a pure read transaction, its transaction ID is 0 by default.

The structure of the Read View is as follows:

  • Rw_trx_ids: Represents the array of Read and write transactions that are currently active when the Read View is generated.

  • Min_trx_id: Indicates the current committed transaction number + 1, which is the minimum transaction number in rw_trx_IDS, when the Read View is generated.

  • Max_trx_id: Indicates the currently allocated transaction number + 1 that will be assigned to the next transaction when the Read View is generated.

  • Curr_trx_id: id of the current transaction that created the Read View.

  • trx_id < min_trx_id, the record is in the current transactionvisibleBecause the transaction that modifies this version of the record is committed before the current transaction generates the Read View.
  • trx_id in (rw_trx_ids), then the record is in the current transactioninvisibleBecause the transaction to change this version of the record has not been committed before the current transaction generates the Read View.
  • trx_id > max_trx_id, then the record is in the current transactioninvisibleBecause the transaction that modifies this version of the record is not started before the current transaction generates the Read View.
  • trx_id = curr_trx_id, then the record is in the current transactionvisibleBecause the transaction that modifies the record for this version is the current transaction.

Such as:

We started a read transaction in Step 1, and since it is a read-only transaction, its transaction ID is 0 (hereinafter referred to as transaction 0). We then query the record with ID 1 in transaction 0. At this point, the version chain is as follows:

Note: records connected to the red header are in the B+ tree, while records connected via the roll_ptr pointer are in the undo log. All of the following version chains are in this form.

READ UNCOMMITTED

MVCC is not used for this isolation level. It just performs select, and it gets the latest records in the B+ tree. Regardless of whether the transaction for the record has been committed.

READ COMMITTED

At the READ COMMITTED isolation level, MVCC is used. After a read transaction is started, it will run in eachselectEach operation generates a Read View.

Since there are no active transactions when the SELECT in Step 2 reads, it means that all transactions are committed. So it can read the first record.

Perform Step 3 to start a new transaction with the transaction ID 101 (check transaction 101 below).

Perform Step 4, which modifies the record with ID 1, and the version chain will look like this:

In Step 5, transaction 0 performs a SELECT operation and generates a Read View. The value of Read View is as follows:

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

  1. The first record in the version chain, itstrx_idNot less thanmin_trx_id, so the record is not visible.
  2. The second record in the version chain, itstrx_idLess thanmin_trx_id, so the record is visible.

So for this query, the records it can get are:

Transaction 101 performed an update operation in Step 5, and after committing the transaction in Step 6, the version chain is as follows:

In step 7, we perform a SELECT query in transaction 0, which also generates a READ View because our isolation level is READ COMMITTED. The Read View has the following values:

Then according to the version chain visibility rule:

  • Since there are no active transactions, we know that all transactions have been committed, sorw_trx_idsIs empty.
  • Version chain record 1 of ittrx_idLess thanmin_trx_id, so this record is visible.

The result of this query is as follows:

After transaction 0 executes the query, we start 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 is self-analyzed. We directly give the final version chain of transaction 102 after executing the two update statements:

Perform 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 the READ View is generated.

READ COMMITTED is when a new READ View is generated every time a SELECT operation is performed. REPEATABLE READ generates a READ View only on the first SELECT operation, and all select operations use the first generated READ View until the transaction commits.

Let’s redo the steps in the table.

First, perform step 2, after transaction 0 starts the transaction, and perform a SELECT query. A Read View is generated. The structure of the Read View is as follows:

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

So, although two transactions are opened later and records are modified, the final version chain looks like this:

Transaction 0 can still only read the first record, which is:

Select * from person where id = 1; It will only use the first generated Read View to select the records in the version chain that can be Read.

SERIALIZABLE

MVCC is not used for this isolation level. If a normal SELECT statement is used, it adds lock in share mode to the end of the statement to make it a consistent lock read. If one transaction reads a record, changes made to the record by other transactions are blocked. If a transaction is changing a record, all other transactions are blocking access to the record.

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

conclusion

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

But READ COMMITTED and REPEATABLE READ use MVCC differently:

  • READ COMMITTED is COMMITTED on each executionselectEach operation generates a Read View.
  • REPEATABLE READ Is executed only on the first timeselectThe Read View is generated when the operation is performed, and subsequentselectAll operations will use the Read View generated the first time.

The READ UNCOMMITTED and SERIALIZABLE isolation levels do not use MVCC.

They also have different read operations:

  • READ UNCOMMITTED This command is executed each timeselectI read the latest records.
  • SERIALIZABLE is executed each timeselectOperations are appended to the statementlock in share modeTo makeselectBecomes a consistent locked read that serializes reads and writes.