What is phantom reading?

What is phantom reading?

Phantom reading means that transaction A first obtains N pieces of data based on the conditional index, and then transaction B changes M pieces of data other than these N pieces of data or adds M pieces of data that meet the search conditions of transaction A. As a result, transaction A searches again and finds N M pieces of data, which will cause phantom reading.

How to solve phantom reading?

• Solved by next-key lock.

• The reason for phantom reading is that row locks can only lock rows, but when inserting new records, what needs to be updated is the "gap" between records. Therefore, in order to solve phantom reads, InnoDB introduces gap locks.

• Gap lock Gap lock is only effective under the repeatable read level

• Gap lock and row lock are collectively called next-key lock, each next-key lock is an open and closed interval.

Among the four isolation levels defined in the database,

the highest isolation level SERIALIZABLE_READ can ensure that there will be no phantom reading problem.

Repeatable Read (RR)

For the current read, the RR isolation level ensures that the read record is locked (record lock), and the read range is guaranteed to be locked. , new records that meet the query conditions cannot be inserted (gap lock), and there is no phantom reading phenomenon.

Recommended tutorial: "MySQL Tutorial"

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