What is the MVCC mechanism?

What is MVCC multi-version concurrency control

As mentioned earlier, a transaction in a concurrent environment can cause many problems such as dirty reads, dirty writes, and unrepeatable reads. To solve these problems and ensure ACID properties of the transaction, there are many isolation levels to solve these problems. The default isolation level used by mysql is repeatable reads. So how does mysql keep transactions isolated at repeatable read isolation level? The answer is via the Multi-version Concurrency Control (MVCC) mechanism.

How does MVCC multi-version concurrency control ensure isolation

If the MVCC mechanism is designed to guarantee isolation, how does it guarantee isolation? It is through the undo log version chain and read View mechanism.

Undo log version chain and Read View mechanism

Undo log version chain

The undo log version chain means that after each transaction changes the data, the previous data and its transaction ID are recorded. The newly recorded data points to the previous data, forming a version chain. In this way, when the transaction is executed, if the data we read is not the previous data, we can roll back.

Read the view mechanism

The undo log version chain gives us the results of the transaction’s execution at different stages, so how do we know we’re not reading the previous data? Is through the

The read View mechanism implements this. The process is as follows:

After the transaction is started, an array of views is generated, consisting of the minimum and maximum ids of the current uncommitted transaction and the ids of the largest transaction in the database e.g. [60,80],100

1. If the current trx_id is less than min_id and falls in green, it means that the version was generated by committed transactions and this data is visible.

If the current value of trx_id is greater than the value of max_id, it will be visible in blue, indicating that the version was generated by an uncommitted transaction (the transaction ID is generated by executing SQL, not by committing).

3. If the current trx_id is in red (min_id <=trx_id<= max_id), there are two cases

  • If the trx_ID of the row is in the view array, it means that the version was generated by a transaction that has not yet committed and is not visible.
  • If the trx_ID of the row is not in the view array, it indicates that the version was generated by a committed transaction.