How does MySQL implement MVCC?

Transaction Isolation Level

A Critique of ANSI SQL Isolation Levels

In Japanese it is very helpful to understand above article.

A critique of ansi sql isolation levels 解説公開用

We should understand at first Transaction Isolation Level on MySQL.
17.7.2.1 Transaction Isolation Levels

MySQL Default Isolation Level is REPEATABLE READ.

What is REPEATABLE READ?

What is Dirty Read

The term “Dirty Read” refers to the phenomenon where a transaction can read changes made by another transaction that has not yet been COMMITTED. In other words, a transaction can read data that is not yet finalized or committed.

What is Fuzzy Read

Fuzzy Read(Non-Repeatable Read) refers to a type of read operation where the values read may differ between the first and second reads of the same data. This can occur if the data is modified by another transaction between the first and second reads, resulting in an inconsistency.

Phantom Read

Phantom Read refers to a situation where, during the execution of a transaction, another transaction inserts or deletes the same data, causing the results of the same query to change.

How does MySQL implement MVCC

Phantom reads do not occur in MySQL (InnoDB, REPEATABLE READ).

According to the ANSI standard, phantom reads are supposed to occur in REPEATABLE READ, but it was found that phantom reads do not occur in MySQL (InnoDB, REPEATABLE READ).

MySQL (InnoDB), in addition to MVCC, an algorithm called Next-Key Locking is also used to prevent phantom reads.

17.7.4 Phantom Rows

To prevent phantoms, InnoDB uses an algorithm called next-key locking that combines index-row locking with gap locking. InnoDB performs row-level locking in such a way that when it searches or scans a table index, it sets shared or exclusive locks on the index records it encounters. Thus, the row-level locks are actually index-record locks. In addition, a next-key lock on an index record also affects the “gap” before the index record. That is, a next-key lock is an index-record lock plus a gap lock on the gap preceding the index record. If one session has a shared or exclusive lock on record R in an index, another session cannot insert a new index record in the gap immediately before R in the index order.