How to solve MySQL phantom reading?

How to solve phantom reading in MySQL?

1. MVCC snapshot, which stores historical data in a snapshot. When a transaction adds or deletes data, it is guaranteed to be invisible to the current transaction;

Most databases Multi-version concurrency control is implemented, and all is achieved by saving data snapshots.

Taking InnoDB as an example, there are two redundant word breaks in each line. One is the created version of the row, and one is the deleted (expired) version of the row. The version number increases automatically with each transaction. Every time a transaction fetches data, it fetches 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 reading.

select * from T where number = 1;

2. "next-key" lock locks the gap between the current data row and the previous data and the next data to ensure that the data read within this range is consistent.

next-key lock contains two parts

• Record lock (row lock)

• Gap lock

Record locks are locks added to the index, and gap locks are added between indexes. (Thinking: What will happen 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

Recommended tutorial: "MySQL Tutorial"

The above is the detailed content of How to solve MySQL phantom reading?. For more information, please follow other related articles on the PHP Chinese website!