Explain in detail how MySQL solves illusion reading

1. What is Fantasy Reading

In a transaction, after multiple queries, the number of result sets is not 1, which is called phantom reading.

And which one line is called magic line, which one is extra or less

2. Why do you want to solve illusion reading

In a highly concurrent database system, it is necessary to ensure the isolation between transactions and the uniformity of transactions themselves.

3. How does MySQL solve phantom reading

If you see this article, then I will acquiesce that you know dirty reading, unrepeatable reading and repeatable reading.

1. Multi-Version Concurrency Control (MVCC) (Snapshot Read)

Most databases implement multi-version concurrency control, and all of them rely on saving snapshots of data.

Take InnoDB as an example, there are two redundant words in every line. 1 is the created version of the row and 1 is the deleted (expired) version of the row. The version number increases with each transaction. Every time a transaction fetches data, it fetches data whose created version is smaller than the current transaction version, and data whose expired version is larger than the current version.

A normal select is a snapshot read.

select * from T where number = 1;

Principle: Save a snapshot of historical data, so the addition and deletion of data by other transactions is invisible to the current transaction.

2. next-key lock (currently read)

The next-key lock consists of two parts

Record lock (row lock) Gap lock

Record locks are locks added to indexes, and gap locks are added between indexes. (Think: What happens if there is no index on the column?)

select * from T where number = 1 for update;
select * from T where number = 1 lock in share mode;
insert
update
delete

Principle: Lock the gap between the current data row and the previous data and the next data to ensure that the data read in this range is 1.

Others: Does MySQL InnoDB Engine RR Isolation Level Solve Magic Read
Refer to 1 comment address above github:

The official explanation of magic reading given by Mysql is that as long as there is more row in the second select in one transaction, it will be considered as magic reading.
a transactions begin with select, b transaction insert does add an gap lock, But if the b transaction commit, This gap lock will be released (a transaction can operate dml at will after release), the result of a transaction re-select will be the same as the first select1 under MVCC, then update of a transaction without condition, this update will act on all rows (including new ones added by b transaction), and a new row in b transaction will appear when a transaction re-select, and this new row has been modified by update, which is actually the case at RR level.

If understood in this way, the RR level of Mysql really can't prevent phantom reading

Youdaoyou reply address:

In the case of snapshot reading, mysql uses mvcc to avoid phantom reading.
In the current reading situation, mysql uses next-key to avoid phantom reading.
select * from t where a=1; Belonging to snapshot reading
select * from t where a = 1 lock in share mode; Belonging to the current read

A snapshot read cannot be considered a phantom read if the result is not the same as the current read. These are two different uses. So I think the rr level of mysql solves the illusion reading.

First of all, the MySQL storage engine InnoDB isolation level RR solves the phantom read problem.

As mentioned in question 1, update after T1 select will update the data of insert in T2, so it is considered that there is one extra row, so illusion reading cannot be prevented. It seems that the statement is impeccable, but it is actually wrong. There are two modes of snapshot reading and current reading in InnoDB. If there is only snapshot reading, there is naturally no magic reading problem. However, if the statement is promoted to current reading, T1 needs to use the following syntax when select: select * from t for update (lock in share mode) to enter the current reading, so naturally there is no T.

Attention
Although next-key solves the phantom reading problem well, it still follows the general law that the higher the isolation level, the lower the concurrency.