Detailed explanation of how MySQL solves phantom reads

1. What is phantom reading?

In a transaction, after multiple queries, the situation where the number of result sets is inconsistent is called phantom read.

The extra or missing row is called a phantom row.

2. Why should we solve phantom reading?

In a high-concurrency database system, it is necessary to ensure the isolation between transactions and the consistency of the transactions themselves.

3. How does MySQL solve phantom reading?

If you see this article, I will assume that you understand dirty reads, non-repeatable reads, and repeatable reads.

1. Multi-version concurrency control (MVCC) (snapshot read)

Most databases implement multi-version concurrency control, and they all rely on saving data snapshots to achieve this.

Taking InnoDB as an example, two redundant bytes are added to each row. One is the created version of the row, and one is the deleted (expired) version of the row. The version number increments with each transaction. Each time a transaction retrieves data, it retrieves data whose creation version is smaller than the current transaction version, and data whose expired version is larger than the current version.

Ordinary select is snapshot read.

select * from T where number = 1;

Principle: A snapshot of historical data is stored, so data added or deleted by other transactions is not visible to the current transaction.

2. Next-key lock (current read)

The next-key lock consists of two parts.

1. Record lock (row lock)
2. Gap Lock

Record locks are locks added to indexes, and gap locks are locks 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 and next data rows to ensure that the data read within this range is consistent.

Others: Does the RR isolation level of the MySQL InnoDB engine solve the phantom read? Reference a comment address on github:

The official explanation of phantom read given by MySQL is: as long as there is an extra row in the second select in a transaction, it is considered a phantom read.
Transaction a selects first, and transaction b inserts, which will indeed add a gap lock. However, if transaction b commits, the gap lock will be released (after which transaction a can perform DML operations at will). The result of transaction a's select is the same as the first select under MVCC. Then transaction a updates unconditionally, and this update will act on all rows (including those newly added by transaction b). When transaction a selects again, the new row in transaction b will appear, and this new row has been modified by the update. This is indeed the case at the RR level.

If this is the case, then the RR level of MySQL cannot prevent phantom reads.

A friend replied to the address:

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

The situation where the results of snapshot read and current read are different cannot be considered as phantom read. These are two different uses. So I think MySQL's rr level solves the problem of phantom reading.

Let me first state the conclusion. The MySQL storage engine InnoDB isolation level RR solves the phantom read problem.

As mentioned in the previous question, if T1 is updated after the select, the data inserted in T2 will be updated together, so it is considered that there is an extra row, so phantom reading cannot be prevented. This statement seems impeccable, but it is actually wrong. InnoDB has two modes: snapshot read and current read. If there is only snapshot read, then there is naturally no phantom read problem. However, if the statement is promoted to current read, then T1 needs to use the following syntax when selecting: select * from t for update (lock in share mode) to enter current read, then naturally there is no such thing as T2 being able to insert data.

Notice
Next-key certainly solves the phantom read problem very well, but it still follows the general rule that the higher the isolation level, the lower the concurrency.

The above is a detailed explanation of how MySQL solves phantom reads. I hope it will be helpful to you. If you have any questions, please leave me a message and I will reply to you in time. I would also like to thank everyone for their support of the 123WORDPRESS.COM website!