How to solve the problem of Phantom Reading in MySQL

How to solve the problem of phantom reading in MySQL? I believe that many inexperienced people are at a loss about this, so this article summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

1. MVCC snapshot, which stores a snapshot of historical data and ensures that it is invisible to the current transaction when the data is added or deleted.

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

In InnoDB, for example, there are two redundant word breaks in each line. One is the created version of the line, and the other is the deleted (expired) version of the line. The version number increases with each transaction. Each time a transaction fetches data, it fetches data with a created version smaller than the current transaction version, and data with an expired version greater than the current version.

Ordinary select is snapshot reading.

Select * from T where number = 1

2. "next-key" lock, which locks 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 consistent.

The next-key lock consists of two parts

Record lock (row lock)

Gap lock

A record lock is a lock added to an index, and a gap lock is 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;insertupdatedelete

After reading this article, can you solve the problem of MySQL phantom reading independently? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel. Thank you for reading.