Isolation level, unreal read, gap lock, next key lock

https://developpaper.com/take-it-isolation-level-unreal-read-gap-lock-next-key-lock/

I’ve written a lot of knowledge points related to MySQL before. I can string them up a little in this article. From SQL execution process, mvcc to lock, I may feel that I have understood gap lock and next key lock, but I seem to feel that I don’t understand them very well. This article combs the concepts from the beginning to clarify these knowledge.

lock
First, MySQL implements two row level locks:

Shared lock: a transaction is allowed to read a row of data, which is generally recorded as s, also known as read lock

Exclusive lock: allows a transaction to delete or update a row of data, which is generally marked as X, also known as write lock

As for the mutex of read-write locks, it should be clear that read locks can only be compatible with read locks, and other scenarios cannot be compatible. I won’t repeat it here.

Take it! Isolation level, unreal read, gap lock, next key lock

Isolation level
Continue to review the four isolation levels of MySQL:

Read uncommitted: you can read data that has not been committed by other transactions. This phenomenon is called dirty reading.

Read committed: only the committed data of other transactions will be read, so dirty reading of RC will not occur. Therefore, another problem is called non repeatable reading. Two SQL queries in a transaction may find different results.

Repeatable read: RR is the default isolation level of MySQL. Two SQL queries in a transaction will always find the same results. There is no problem of non repeatable reading, but there will still be unreal reading.

Serial serializable: there is no problem in the serial scenario. The fully serialized operation includes read lock and write lock.

Take it! Isolation level, unreal read, gap lock, next key lock

Unreal reading, next key lock, mvcc
After a brief review of the basics, let’s take a look at the problem of unreal reading at the RR level. The official MySQL document describes this:

The so-called phantom problem occurs within a transaction when the same query produces different sets of rows at different times. For example, if a SELECT is executed twice, but returns a row the second time that was not returned the first time, the row is a “phantom” row.

Phantom reading refers to the collection of different row records obtained at different time points and the same query under the same transaction.

If you say oneselectIt was executed twice, but the second time there were more lines of records than the first time. This is phantom reading.

Therefore, for unreal reading, it must be the newly inserted data!

For example, in a transaction, query firstselect * from user where age=10 for update, the result is the record with ID [1,2,3]. Execute the query again to get the record with result [1,2,3,4], which is unreal reading.

How to solve the problem of unreal reading? Previously, I said in my article that the principle of solving illusory reading is mvcc（Mvcc principle see here）Many online articles also write this. In fact, you can’t say it wrong, but it’s certainly not quite right. To be exact, the problem of unreal reading should be solved by mvcc + next key lock.

Reading in mvcc can be divided into two types, which are calledSnapshot readandCurrent read(I’ve searched the book for a long time and haven’t found this statement of current reading, but looking at a pile of online materials and bosses, it’s called current reading, so let’s call it current reading. If you know, you can tell me which book has this title. Mysql, I only see the name of lock reading or lock reading. Some say that lock reading is current reading, but I haven’t found the name of current reading Where is the source of the.

Snapshot readIt’s simpleselectIn this scenario, the query is based on mvcc, so there is no unreal reading problem. It can also be considered as one of the solutions to unreal reading. For the RC level, because each query regenerates a read view, that is, the query is the latest snapshot data, Therefore, different data may be queried every time, resulting in non repeatable reading. For RR level, read view is generated only for the first time, and the query is the snapshot data at the beginning of the transaction, so there is no problem of non repeatable reading, and of course there is no problem of unreal reading.

Therefore, when we talk about phantom reading, we don’t mean the scene of snapshot reading, but the scene of current reading.

Current readrefer tolock in share mode、for update 、insert、update、deleteThese operations require locking. For mvcc, it is the scenario of snapshot reading, while for current reading, it is the next key lock.

So what is next key lock? How to solve the unreal reading?

There are two types of row lock: write lock X and read lock S. in fact, there are three implementation algorithms for uplink lock, and next key lock is one of them.

The first is calledRecord LockLiterally, a lock on a row record actually refers to a lock on an index record.

For example, execute statementsselect * from user where age=10 for update, will lockuserTable allage=10Row record of all pairsage=10All recorded operations will be blocked.

The second kind is familiar, calledGap Lock, that isClearance lock, which is used for the gap between locked indexes, but does not contain the record itself.

For example, statementsselect * from user where age>1 and age<10 for update, will lockageIn the range of (1,10), the operations of other transactions on the range will be blocked.

Gap locks are unique under the isolation level of repeatable read RR. In addition, there are several scenarios that do not use gap locks.

The transaction isolation level is set to non repeatable read RC, so there must be no gap lock.
Innodb_locks_unsafe_for_binlogSet to 1
Another case applies toPrimary key index or unique indexEquivalent query criteria, such asselect * from user where id=1，idIt is a primary key index, so only record lock can be used. Because it can lock only one record, there is no need to add a gap lock. This is the process of lock degradation.
And the thirdNext-Key LockIn fact, it is equivalent to the combination of record lock + gap lock. For example, if the index has 10, 20 and 30 values, the locked interval may be (- ∞, 10], (10,20], (20,30], (30, + ∞).

Solve unreal reading
Last article aboutUpdate SQL execution procedureWe have a certain understanding of this foundation. Here, we remove some log details irrelevant to the content here and add the process of locking data. In this way, we can better understand how next key lock solves unreal reading through SQL execution. The execution process is as follows:

Take it! Isolation level, unreal read, gap lock, next key lock

First, the server layer will query the data
After the storage engine finds the data according to the query criteria, it locks the data, record lock or gap lock, and then returns the data
After the Server layer gets the data, it calls API to store the engine update data.
Finally, the storage engine returns the result and the process ends
Make a table to explain,userThe table has four fields,idIs the primary key index,nameIs a unique index,ageIt’s a normal index,cityThere is no index, and then insert some test data. Next, distinguish several cases to explain how to add next key lock, and then you will know why there is no unreal reading problem.

Take it! Isolation level, unreal read, gap lock, next key lock

No index
UPDATE statementupdate user set city=‘nanjing’ where city='wuhan’What will happen?

becausecityThere is no index, so the storage engine can only lock all records, and then return the data to the server layer, and then the server layercityChange tonanjingAnd then update the data.

Therefore, first, record lock will lock the existing 7 records, and gap lock will add gap locks to all the gaps of the primary key index.

Therefore, it is a terrible thing that there is no index when updating. It is equivalent to locking the whole table. Of course, there is no unreal reading.

Take it! Isolation level, unreal read, gap lock, next key lock

General index
Let’s assume another statementselect * from user where age=20 for update。

becauseageIt is a common index. The storage engine filters and finds all matches according to conditionsage=20Add write locks to their records. The gap lock will be added to the interval of (10,20), (20,30), so it can’t be inserted anywayage=20My records

Why lock these two intervals? If you do not lock these two intervals, you can insert, for exampleid=11,age=20perhapsid=21,age=20So there is unreal reading.

(in fact, the writing lock is not only added to theageOn the ordinary index, it will be added to the primary key index, because the data is under the primary key index, right? This must also be locked. In order to look simpler, it will not be drawn.)

Take it! Isolation level, unreal read, gap lock, next key lock

Unique & primary key index
What happens if the query is a unique index? For example, there are query statementsselect * from user where name=‘b’ for update。

As mentioned above, if it is a unique index or primary key index and an equivalent query, the lock will actually be degraded to record lock, and there will be no gap lock.

Because the primary key or unique index can ensure that the value is unique, there is no need to increase the gap lock.

Obviously, it can’t be insertedname=bThere is no unreal reading problem.

If it is a range query, for exampleid>1 and id<11In fact, it is the same locking method, so I won’t repeat it.

Slightly different from the above, the unique index not only locks the unique index, but also locks the primary key index. For the primary key index, just index the primary key index.

Take it! Isolation level, unreal read, gap lock, next key lock

summary
Finally, after all this, haven’t the problem of unreal reading been solved at RR level? How can we say that there is a problem of unreal reading?

For this problem, you can see the bug reportedhttps://bugs.mysql.com/bug.ph…, the reply said that this is not a bug, this is a design that meets the isolation specification. If you are interested, take a look for yourself.

Take it! Isolation level, unreal read, gap lock, next key lock

Tags: clearance, Indexes, Primary key, Record, Snapshot