Shulou(Shulou.com)05/31 Report--

This article shows you how to solve phantom reading in MySQL. The content is concise and easy to understand. It will definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

What is Phantom Reading

In a transaction, the situation in which the number of result sets is inconsistent after multiple queries is called phantom reading. Which line is more or less is called illusion.

Second, why solve phantom reading

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

Third, how does MySQL solve phantom reading?

If you read this article, then I will acquiesce that you understand dirty reading, non-repeatable and repeatable.

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

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 specific version number (trx_id) is stored in the information_schema.INNODB_TRX table. The version number (trx_id) 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

Principle: keep a snapshot of historical data, so adding and deleting data from other transactions is not visible to the current transaction.

2. Next-key lock (currently read)

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; 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 within this range is consistent.

Other: whether the MySQL InnoDB engine RR isolation level solves phantom reading

Quote a comment address on github:

The official explanation of phantom reading given by Mysql is that as long as there is more row in the second select in a transaction, it is a phantom reading.

A transaction first select,b transaction insert does add a gap lock, but if b transaction commit, the gap lock will be released (after releasing a transaction can be operated as dml), the result of a transaction select is the same as * times select under MVCC, and then a transaction unconditionally update, this update will act on all rows (including new b transactions), and a transaction will select again and new rows in b transaction will appear. And this new line has been modified by update, which is true at the RR level.

If you understand it this way, the RR level of Mysql really can't prevent illusions.

A Taoist friend replied to the 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 astat1; belongs to snapshot read

Select * from t where astat1 lock in share mode; belongs to the current read

Snapshot reading can not be different from the current reading results, this situation is regarded as illusory reading, these are two different uses. So I think the rr level of mysql solves the illusion.

First of all, the MySQL storage engine InnoDB isolation level RR solves the problem of phantom reading. The interview asks about the four isolation levels of bad MySQL. I suggest you read this article.

As mentioned in a quoted question, after T1 select, update will update the data of insert in T2 together, so you think that there is an extra line, so you can't prevent false reading. The statement is impeccable, but in fact it is wrong. Snapshot read and current read are set in InnoDB. If there is only snapshot read, then there is naturally no phantom reading problem, but if you promote the statement to the current read, then T1 needs to use the following syntax when select: select * from t for update (lock in share mode) enters the current read, then there is no such thing as T2 can insert data.

Be careful

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

The above is how to solve phantom reading in MySQL. Have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.