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

This article will explain in detail how to solve the problem that MySQL database is easy to produce phantom reading under the RR isolation level. The content of the article is of high quality, so the editor shares it for you to do a reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

Review of Part Ⅰ issues

The definition of phantom reading: phantom reading means that when a transaction reads a record in a certain range, another transaction inserts a new record in that range, and a magic row occurs when the previous transaction reads the record in that range again.

For example, id is the primary key index and T1 is the primary transaction in the user table:

T2 is an interfering transaction:

Step1:T1 starts and checks if there is a record of id=1 in the user table.

Step2:T2 starts, inserts a record with id 1 and executes successfully.

Step3:T1 starts inserting id=1 records when it finds no id=1 records, but fails (primary key conflict).

Step4:T1 can't accept the fact that he checked again whether there was a record of id=1, and found that there was no such thing as a complete collapse.

From step 4, we can see that no matter how many times we check before the main transaction executes commit, we cannot get this record of id=1, because it has already produced illusory readings.

Part Ⅱ solution

There are two solutions to solve the problem of phantom reading. one is to use SERIALIZABLE data isolation level, but this scheme will force all transactions to be sorted in order to achieve the problem that transactions do not conflict with each other. When the concurrency of transactions is high, it is easy to produce a large number of timeouts and lock competition, so this scheme is generally not recommended. Another solution is to manually add x locks (exclusive locks) or s locks (shared locks) to select operations under the RR data isolation level, which are described in detail below.

1. What are shared locks and exclusive locks?

Shared lock (SELECT... LOCK IN SHARE MODE) means that when a transaction acquires a record shared lock, other transactions can also acquire the shared lock of the record, but if multiple transactions acquire the shared lock of the record at the same time, no one can modify the record until the shared lock is released and only one transaction owns the lock of the record.

Exclusive lock (SELECT... FOR UPDATE) that is, when a transaction acquires an exclusive lock for a record, other transactions cannot acquire the shared lock and exclusive lock for that record, nor can it modify the record until the transaction releases the lock.

two。 Similarities and differences

Similarity: while a transaction acquires a shared or exclusive lock for a record, no other transaction can modify the record until the transaction releases the lock.

The difference: exclusive locks block other transactions' shared locks on the same record more than shared locks, but do not affect snapshot reads.

3. Give examples to illustrate

Shared lock:

Exclusive lock:

Part Ⅲ

Whether the shared lock and the exclusive lock can replace each other depends on the specific scenario, such as the above two examples. In the first example, if an exclusive lock is used, it will cause a user to operate the union. Other users will not be able to obtain this record sharing lock.

In the second example, if a shared lock is used, other transactions can acquire a shared lock for the record of the goods table, resulting in a situation where no one can update the value of the remaining number. Therefore, both shared lock and exclusive lock have their own functions and can not replace each other.

On how to solve the MySQL database in the RR isolation level is easy to produce phantom reading problem to share here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to 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.