1. What is MVCC? (what)

First of all, for concurrent reads and writes of data, we need to recognize the basic fact that read-read does not conflict, and write-write and read-write do. Read-read does not conflict, so there is no need to do any mutex processing; Write-write conflict may cause loss and update problems, while read-write conflict may cause isolation problems of transactions, such as dirty reads, phantom reads, and unrepeatable reads. Therefore, mutually exclusive processing is required for write-write conflict and read-write conflict. MVCC is the MySQL InnoDB storage engine’s way of resolving read-write conflicts at the read Committed (RC) and repeatable Read (RR) isolation levels. For convenience, the following uses MySQL instead of MySQL InnoDB.

2. Why MVCC? (according to)

The process of conflict resolution is actually the process of mutually exclusive access to a shared resource. To achieve mutually exclusive access to a shared resource, locks are usually required. The conceptual dimension can divide locks into pessimistic and optimistic locks, with the following differences:

Pessimistic lock: Pessimistic in the operation of shared resources, thinking that the operation will cause concurrency problems (other threads will modify the shared resources). Therefore, the shared resources are locked and unlocked after the operation is complete. Application scenario: This method is applicable to the scenario with many concurrent conflicts and writes, but the efficiency is low.

Optimistic lock: Optimistic when operating on a shared resource. It believes that the operation will not cause concurrency problems (no other thread will modify the shared resource) and therefore will not lock the shared resource. During the modification, it will determine whether other threads have modified the resource before, and decide to continue or abandon the modification according to the judgment result. Generally, version number mechanism or CAS will be used to implement the modification. Application scenario: This method is applicable to the scenario where there are few concurrent conflicts and many reads, saving the overhead of locking and releasing locks and increasing system throughput. However, if conflicts occur frequently, optimistic locking can consume CPU retries, which can degrade performance, so pessimistic locking is appropriate.

Note: Optimistic lock and pessimistic lock are not mutually exclusive. In many scenarios, they are used together. For example, the ReentrantLock mutex based on AQS concurrent synchronization framework in JDK is a typical pessimistic lock. At the bottom of AQS, CAS operation is used to preempt and release locks and to manage the threads that preempt locks.

It is feasible to use pessimistic locking to solve read-write conflicts. For a single data record in a database, the frequency of read operations is generally higher than that of write operations. Thus, pessimistic locking of both read operations and write operations will bring system throughput problem: a small number of write operations will affect the efficiency of most read operations. MVCC is the application of optimistic lock in MySQL database. As described in the beginning of this article, MVCC is a way to solve read-write conflicts under the isolation level of READ committed (RC) and repeatable read (RR) in MySQL database. When read-write operations are carried out simultaneously, read is not locked, and read snapshot is adopted to achieve read-write separation and improve system throughput.

3. How to implement MVCC? (How)

MVCC separates read-write operations and resolves read-write conflicts without locking read snapshots, so the key to implementing MVCC intuitively is to generate accurate data snapshots. Generate an accurate snapshot of the data, depending on hidden columns, undo logs, and ReadView.

undo log

Multi-versioning in multi-versioning concurrency control means that there are multiple versions of each data record so that different transactions can see different data records based on the isolation level. These different versions of data records are stored in undo log. One fact to recognize here is that only additions, modifications, and deletions can cause version additions, not queries.

Hidden columns

MySQL storage engine InnoDB adds three hidden fields to each row of data records:

  • DB_TRX_ID: specifies the transaction ID used to insert or modify this record. The difference is that the undo log uses a bit to indicate whether it is deleted or modified, and the info_bits of the row are marked as deleted when deleted, and the purge thread is used to perform the actual deletion.

  • DB_ROLL_PTR: indicates the rollback pointer to undo log records. Undo log stores the records of each record and each change. Each record version in undo log points to the previous record version, which is linked by DB_ROLL_PTR to form a multi-version chain of data records.

  • DB_ROW_ID: Increments as records are inserted. This column acts as a hidden primary key when no primary key is defined. (This field is not really related to MVCC)

ReadView

ReadView is a snapshot of the transaction system at one point in time. Including the following key members:

  • Trx_ids: the list of active transaction ids in the current transaction system, that is, the list of transaction ids that have not been committed.

  • Up_limit_id: the minimum value in trx_IDS. Any data record in the version chain whose DB_TX_ID is less than this value is visible to the current transaction.

  • Low_limit_id: Specifies the Id that will be assigned to the next transaction when the ReadView is generated. The current transaction cannot see records in the version chain where the DB_TX_ID value is greater than or equal to this value.

  • Creator_trx_id: indicates the Id of the current transaction when the ReadView is generated.

The ReadView is used to determine whether the current version of the record is eligible. The ReadView is used to determine whether the current version of the record is eligible.

  1. The accessed version DB_TX_ID is the same as creator_trx_id in ReadView, that is, the current transaction is accessing its own modified record and that version can be read by the current transaction.
  2. The DB_TX_ID value of the accessed version is less than the up_limit_id value in ReadView, meaning that the transaction that generated this version was committed before the current transaction generated ReadView, and this version can be read by the current transaction.
  3. If the value of DB_TX_ID is greater than or equal to low_limit_id in ReadView, the transaction that generates this version is enabled only after the ReadView is generated by the current transaction. This version cannot be read by the current transaction.
  4. DB_TX_ID is between the ReadView’s up_limit_id and low_limit_id, so check whether DB_TX_ID is in the trx_IDS list. If yes, the transaction that generated the version of the ReadView is still active when it is created, and the version cannot be read. If not, the transaction that generated the version of the ReadView when it was created has been committed and the version can be read.

MVCC at the RC isolation level

The RC isolation level solves the problem of dirty reads, including unrepeatable reads and phantom reads. At the RC isolation level, a new ReadView is generated for each Select query operation in the same transaction. For other transactions, after the commit, the system transaction Id changes in the ReadView. This ensures that the current transaction can read records that have been modified and committed by other transactions.

MVCC at the RR isolation level

The RR isolation level solves the problem of unrepeatable reads and phantom reads. MVCC differs from MVCC in the RC isolation level only in the timing of the ReadView generation. RR isolation level ReadView is generated only at the beginning of a transaction and data is read from this ReadView. In addition, phantom reading is for a certain range of data, the above mentioned MVCC is only for a single row of records read operations. MySQL at the RR isolation level can use MVCC to resolve phantom reads during snapshot reads. Need to use next-key lock (Rcord lock + Gap lock) (lock in share mode/for update lock). However, the RR isolation level cannot completely resolve phantom reads. Phantom reads may occur when snapshots are changed in the same transaction. The following is a typical phantom read example:

Transaction A Transaction B> start transaction;                            > start transaction;
Query OK, 0 rows affected (0.0654 sec)          Query OK, 0 rows affected (0.0645 sec)
Empty set (0.0648 sec)
(END)

> select * from t where id = 1;
Empty set (0.0659 sec)
(END)

                                                > insert into t (id, name) values (1, name_1);
                                                Query OK, 1 row affected (0.0654 sec)
                                         
                                                > commit;
                                                Query OK, 0 rows affected (0.0742 sec)
                                         
> update t set name = 'new-name' where id = 1;
Query OK, 1 row affected (0.0645 sec)

> select * from t where id = 1;
+----+----------+
| id | name     |
+----+----------+
|  1 | new-name |
+----+----------+
1 row in set (0.0648 sec)                                      
Copy the code

In the above example, transaction A finally selects the new data with id = 1, but does not read the first data, which meets the definition of phantom read. Before the last select, USER A updates the version number of the record to the Id of the current transaction, which meets the conditions for judging by ReadView in snapshot read and reads the data inserted by transaction B. However, at the business level, transaction A’s premise for the update operation is to acquiesce to the existence of the record with id = 1. Can this be considered as an expected and negligible phantom read?

4. To summarize

  • MVCC works in RC and RR to separate reads and writes, improving MySQL throughput by reading without locking.
  • MVCC at the RR isolation level can solve the phantom read problem of snapshot reads. The phantom read under the current read must be locked.
  • The RR isolation level cannot completely resolve phantom reads. Phantom reads can still occur when two snapshot reads in the same transaction contain modified phantom rows.
  • The article only talked about a little superficial understanding of MVCC, if there is insufficient, welcome to give advice.