Did MySQL solve phantom reading at the RR level?

Y said

Hi, long time no see.

There have been some changes at work recently, and the articles are seldom updated. However, I still collect some article idea at ordinary times, and I will write it slowly when I am free

This article is because I wrote an article about InnoDB lock before, and some readers left comments on my personal website asking “Gap lock has solved the illusion problem to a certain extent, why not completely solved it”, so I sort out the problem about illusion in MySQL again.

After writing the article, I found that I also have new harvest, and understand some strange knowledge points, haha.

Isolation levels and illusions

There are four isolation levels for database transactions, which are applied in different scenarios. The lower the isolation level, the higher the concurrency, but the worse the data consistency. Problems with the four isolation levels are listed as follows:

MySQL InnoDB’s default isolation level is Repeatable Reads (RR), but it solves most of the phantom reading problems (explained later why most not all) through MVCC+ gap locking.

What is illusory

To put it simply, a transaction with the same query condition, the number of rows queried twice is not the same, it will generate a phantom read.

For example, for the following data:

SQL > select * from transaction A;

SELECT * FROM demo WHERE id < 10;
Copy the code

In this case, only the data with id = 5 can be found.

If transaction B inserts a row with id = 6:

INSERT INTO demo VALUES (7.7.7);
Copy the code

Select * from user where id = 5 and id = 7; For transaction A, it is like an illusion that the same query condition detects an extra row of data.

This is also common in business scenarios: I first query the public account named “compiled a program” and find that it does not exist, so WHEN I try to create it, I find that it has been created by another transaction, so the unique index indicates that I failed to create it, and the changed name already exists. Then am I not hallucinating this business? Obviously just checked does not exist ah ~

Snapshot read and current read

Read the snapshot

In MySQL, there are two types of query statements. One is a simple select operation, which is snapshot read without lock. It reads the snapshot version of the record (this version is related to MVCC, more on that later).

select * from table where? ;Copy the code

The current reading

The other is a special read operation to lock, which reads the latest version of the record.

-- Shared read lock
select * from table where ? lock in share mode;
-- Shared write lock
select * from table where ? for update;

-- Add, delete and change also belong to the current read, because to see whether the record is in first
insert into table values(...). ; updatetable set ? where? ;delete from table where? ;Copy the code

Unreal read under snapshot read

InnoDB uses MVCC to solve the illusion problem under current read. Each transaction has an increasing transaction ID, and each row record has two hidden fields: create version and delete version. When operating, follow the following rules:

INSERT

Save the current transaction ID as the row version number

DELETE

Save the current transaction ID to the deleted version of this row.

UPDATE

Insert a new record, save the current transaction ID as the row version number, and save the current transaction ID to the deleted version of the original row.

SELECT

• Only rows whose version is less than or equal to the current transaction ID are read. This ensures that the transaction reads rows that either existed before or were inserted or modified by the transaction itself.
• Only rows whose Deleted version is empty or less than or equal to the current transaction ID are read. This ensures that rows read by the transaction are not deleted before the transaction.

The illusion under the current read

The illusion under the current read is implemented by gap_lock. When transaction A queries, A gap is locked, and other transactions insert, delete, and other operations into this gap are blocked by the lock.

For more information on how gap locking works, check out this article I wrote earlier: InnoDB row Locking, So much for You! In short, the gap lock and insert intention lock mutually exclusive, completely solve the illusion problem under the current read.

Does snapshot reading not solve illusions completely?

Yes, MySQL does not fully solve the phantom read problem under snapshot reads.

Here’s an experiment:

1. The current DB has three data ids: 5, 10, and 15.

2. Transaction A queries the data with id < 10, and finds A row with ID = 5

3. Transaction B inserts data with ID = 6

4. Transaction A then queries the data with ID < 10, and can find A row with ID = 5, but can not find the data with ID = 6 (read scenario, solved the phantom read).

5. Transaction A can update/delete data (id = 6), cannot insert data (id = 6)

MySQL can solve the problem by using the version number of MVCC, but it can’t solve the problem by using the version number of MVCC, because MVCC is unlocked.

Therefore, if you want to write data later, it is better to lock the data through the for UPDATE statement, otherwise you may have the problem of step 5 above.

And a support

My name is Yasin, a blogger who insists on original technology. My wechat official account is: Made a Program

All see here, if feel my article write also ok, might as well support once.

The first article will be sent to the public account, the best reading experience, welcome your attention.

Your every retweet, attention, like, comment is the biggest support for me!

There are learning resources, and frontline Internet companies within the push oh