Shulou(Shulou.com)06/03 Report--

This article will explain in detail what is magic reading. The content of the article is of high quality. Therefore, Xiaobian shares it with you for reference. I hope you will have a certain understanding of relevant knowledge after reading this article.

Fantasy reading is that transaction A first obtains N pieces of data according to the conditional index, and then transaction B changes M pieces of data other than the N pieces of data or adds M pieces of data that meet the search conditions of transaction A, causing transaction A to search again and find N+M pieces of data, which will generate fantasy reading.

How to solve illusion reading?

It is solved by next-key lock.

The reason for this is that row locks can only lock rows, but the action of inserting a new record updates the "gap" between records. Therefore, to address phantom reads, InnoDB introduces gap locks.

Gap lock is only effective at repeatable read level

Gap locks and row locks are collectively called next-key locks, and each next-key lock is an interval that is open before and closed after.

Of the four isolation levels defined by the database,

SERIALIZABLE_READ, the highest isolation level, guarantees that there will be no phantom reading problems.

Repeatable Read (RR)

For the current read, RR isolation level ensures that the read record is locked (record lock), and at the same time, it ensures that the read range is locked, and new records meeting the query conditions cannot be inserted (gap lock), and there is no phantom read phenomenon.

About what is magic reading to share here, I hope the above content can be of some help to everyone, you can learn more knowledge. If you think the article is good, you can share it so that more people can see it.

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.