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

This article will explain in detail about MySQL solution magic reading method, Xiaobian think quite practical, so share for everyone to make a reference, I hope you can have some harvest after reading this article.

1. What is magic reading?

In a transaction, after multiple queries, the number of inconsistent result sets is called phantom reading.

And whichever line is more or less is called phantom.

2. Why should we solve the illusion reading

In a highly concurrent database system, you need to ensure the isolation between transactions and the consistency of the transactions themselves.

3. How MySQL solves illusion reading

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

1. Multiversion concurrency control (MVCC)(snapshot read)

Most databases implement multiversion concurrency control, and they do so by keeping snapshots of their data.

In InnoDB, for example, there are two word breaks redundant in each line. One is the creation version of the row and one is the deletion (expiration) version of the row. The version number increments with each transaction opening. Each time a transaction fetches data, it fetches data that was created at a version less than the current transaction version, and data that is older than the current version.

A normal select is a snapshot read.

select * from T where number = 1;

Principle: Keep a snapshot of historical data, so other transactions add and delete data that is invisible to the current transaction.

2. next-key lock (currently read)

next-key lock consists of two parts

Record lock (row lock)

gap lock

Record locks are locks placed on indexes and gap locks are locks placed between indexes. (Thinking: 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

Principle: Lock the gap between the current data line and the previous data and the next data to ensure that the data read within this range is consistent.

Other: MySQL InnoDB engine RR isolation level whether to solve the phantom read

To quote a comment on github:

Mysql officially gives the illusion reading explanation: as long as in a transaction, the second select more than row is considered illusion reading.

Transaction a selects first, transaction b inserts will indeed add a gap lock, but if transaction b commits, this gap lock will be released (after release, transaction a can operate dml at will), the result of transaction a's re-selection is still the same as the first select under MVCC, and then transaction a updates unconditionally, this update will act on all rows (including the newly added transaction b), transaction a selects again will appear in transaction b, and this new row has been updated and modified, which is true at RR level.

If understood in this way, Mysql's RR level is indeed unable to prevent phantom reading.

Some fellow daoists replied to the address:

In the case of snapshot reads, mysql uses mvcc to avoid phantom reads.

In the current reading situation, mysql uses next-key to avoid phantom reading.

select * from t where a=1; belongs to snapshot read

select * from t where a=1 lock in share mode; belongs to current read

A snapshot read cannot be read differently from the current read. This is considered a phantom read, and is used for two different purposes. So I think the RR level of mysql is solving the illusion of reading.

To conclude, MySQL storage engine InnoDB isolation level RR solves the phantom read problem.

As quoted in a question, update after T1 select will update the insert data in T2 together, so it is considered that there is one more line, so it cannot prevent phantom reading. InnoDB has two modes: snapshot read and current read. If there is only snapshot read, then there is naturally no magic read problem. However, if the statement is promoted to current read, then T1 needs to use the following syntax when selecting: select * from t for update (lock in share mode) to enter current read, then naturally there is no T2 to insert data.

note

Next-key is a good way to solve the illusion problem, but it still follows the general law that the higher the isolation level, the lower the concurrency.

About "MySQL solution magic reading method" This article is shared here, I hope the above content can be of some help to everyone, so that you can learn more knowledge, if you think the article is good, please share it to let more people see.

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.