What is the scope of MySQL next-key lock?

Preface

One day, I was asked about the next-key lock of MySQL. My immediate reaction was:

What's all this? ? ?

I can't see anything in this screenshot?

Take a closer look, it seems familiar. Isn't this the content in "MySQL 45 Lectures"?

What is next-key lock

A next-key lock is a combination of a record lock on the index record and a gap lock on the gap before the index record.

The explanation on the official website probably means: next-key lock is a combination of the record lock on the index record and the gap lock on the gap before the index record.

Give yourself a series of small question marks first? ? ?

  1. What indexes are locked by locking on the primary key, unique index, ordinary index, and ordinary field?
  2. Which range of data is locked under different query conditions?
  3. What is the lock range of for share and for update equivalent query and range query?
  4. When the query equivalent does not exist, what is the scope of the lock?
  5. What is the difference when the query conditions are primary key, unique index, and ordinary index?

Since I don't understand anything, I have to start from scratch and practice it!

Let's take a look at the conclusion of Teacher Ding Qi in "MySQL 45 Lectures":

After reading this conclusion, you should be able to answer most of the questions, but there is a very, very important sentence that needs attention:MySQL 后面的版本可能会改变加锁策略,所以这个规则只限于截止到现在的最新版本,即 5.x 系列<=5.7.24,8.0 系列 <=8.0.13

Therefore, the above rules, the current version does not necessarily apply, Here I MySQL 8.0.25version, for example, multi-angle validate next-key lock locking range.

Environmental preparation

MySQL version: 8.0.25

Isolation level: Repeatable Read (RR)

Storage engine: InnoDB

mysql> select @@global.transaction_isolation,@@transaction_isolation\G
mysql> show create table t\G
How to use Docker to install MySQL, please refer to another article "Use Docker to install and connect to MySQL"

Primary key index

First, verify the scope of the next-key lock of the primary key index

The data of the database at this time is shown in the figure. For the primary key index, the data gap at this time is as follows:

Primary key equivalent query-data exists

mysql> begin; select * from t where id = 10 for update;

This SQL, to id = 10be locked, you can think about adding what lock? What data is locked?

Can data_locksview the lock information, SQL as follows:

# mysql> select * from performance_schema.data_locks;
mysql> select * from performance_schema.data_locks\G

Meaning specific field can refer to the official documentation

The results mainly include information such as engines, libraries, tables, etc. We need to focus on the following fields:

  • INDEX_NAME: The name of the locked index
  • LOCK_TYPE: The type of lock. For InnoDB, the allowed values ​​are RECORD row-level locks and TABLE table-level locks.
  • LOCK_MODE: The type of lock: S, X, IS, IX, and gap locks
  • LOCK_DATA: Data associated with the lock. For InnoDB, when LOCK_TYPE is RECORD (row lock), the value is displayed. When the lock is on the primary key index, the value is the primary key value of the locked record. When the lock is on the auxiliary index, the value of the auxiliary index is displayed and the primary key value is appended.

Obviously a result, there is recorded a table IX added and lock the primary key index id = 10, the addition of a X,REC_NOT_GAPlock, the lock indicates that only a recording.

The same for shareis added to a table and lock IS primary key index record id = 10, S add a lock.

conclusion can be made:

When the primary key equivalent value is locked, and when the value exists, an intention lock is added to the table, and a row lock is added to the primary key index at the same time.

Primary key equivalent query-data does not exist

mysql> select * from t where id = 11 for update;

If the data does not exist, what lock will be added? What is the scope of the lock?

Before verification, analyze the gaps in the data.

  1. id = 11It certainly does not exist. But it is added for update. At this time, you need to add next-key lock, id = 11which belongs to the front opening and back closing interval of (10,15) ;
  2. Because it is 等值查询not necessary lock id = 15that record, next-key lock latch will degenerate into a gap;
  3. The final interval is (10,15) opening before and after opening.

Use data_locks to analyze lock information:

Look lock information X,GAPindicates the lock plus gap, wherein LOCK_DATA = 15, indicates the lock is the primary key index id = 15 before the gap.

At this time, executing SQL in another Session, the answer is obvious, id = 12 cannot be inserted, and id = 15 can be updated.

It can be concluded that when the data does not exist, the primary key equivalent query will lock the gap where the primary key query condition is located.

Primary key range query (emphasis)

mysql> begin; select * from t where id >= 10 and id < 11 for update;

According to the analysis of "MySQL 45 Lectures", the following results are obtained:

  1. id >= 10 Position to the interval of 10 (10,+∞);
  2. Because there is an equivalence judgment for >=, so the value of 10 needs to be included, which becomes [10,+∞) before and after the closed interval;
  3. id < 11To limit the follow-up range, the next interval is determined according to 11 as the front opening and back closing interval of 15 ;
  4. The combination is [10,15]. (Not exactly correct)

First look at data_locks

You can see that in addition to table locks, there are row locks with id = 10 ( X,REC_NOT_GAP) and gap locks before the primary key index id = 15 ( X,GAP).

So actually id = 15 can be updated. That 前开后闭区间there is a problem, personally I think that should be id < 11the conditional, resulting in 15 do not need to lock this row lock.

The result verification is also correct, id = 12 insert blocking, id = 15 update successfully.

When the right side of the range contains equivalence query?

mysql> begin; select * from t where id > 10 and id <= 15 for update;

Let's analyze this SQL:

  1. id > 10 Position to the interval of 10 (10,+∞);
  2. id <= 15 Positioning is (-∞, 15];
  3. The combination is (10,15].

Also look at data_locks first

It can be seen that only one X lock with primary key index id = 15 has been added.

Is it possible to update if id = 15 is verified? Then verify if id = 16 can be inserted?

It turns out that there is no problem!

Of course, some friends here will say that there is a bug in "MySQL 45 Lectures" that will lock the next next-key.

"MySQL 45 Lectures" Lecture 21

It turns out that this bug has been fixed. The repaired version is MySQL 8.0.18. But it has not been completely repaired! ! !

Reference link address:
https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-18.html#mysqld-8-0-18-bug
Search keyword: Bug #29508068)

We can use 8.0.17 to reproduce it separately:

MySQL 8.0.17

In the 8.0.17 id <= 15will id = 20 This data is also locked, and in the 8.0.25 version does not. So this bug has been fixed.

Let's look at are 前开后闭still 前开后开problems, rigorous about using 8.0.17 and 8.0.18 for comparison.

MySQL 8.0.17
MySQL 8.0.18

I guess now is a high probability in the 8.0.18 version of the repair Bug #29508068time, I put this 前开后闭to optimize became 前开后开the.

Compare data_locks data:

Note the red underlined part in the 8.0.17 version id < 17when LOCK_MODE Shi X, and in the 8.0.25 version is X,GAP.

to sum up

This article mainly uses actual operations to verify the next-key lock range when the primary key is locked, and consults data, compares the versions, and draws different conclusions.

Conclusion 1:

  1. When locking, it will first add an intention lock, IX or IS, to the table;
  2. Locking means that if there are multiple ranges, multiple locks are added separately, and each range has a lock; (this can be practiced in the case of id <20)
  3. Primary key equivalent query, when data exists, row lock will be added to the value of the primary key index X,REC_NOT_GAP;
  4. The primary key equivalent query, when the data does not exist, a gap lock will be added to the gap where the primary key value of the query condition is located X,GAP;
  5. The situation of primary key equivalent query and range query is more complicated:
  6. The 8.0.17 version is the front opening and then closing, and the 8.0.18 version and later, the optimization is carried out, the judgment of the primary key is different, and the interval after the closing will not be locked.
  7. The critical <=query, 8.0.17 will lock down after a former next-key opening and closing sections, and 8.0.18 and later versions, fixes this bug.
After optimization, it will be opened later. I don't know if this is because the interval of the primary key will be opened directly after optimization, or because it is a bug. Specific friends can try it.

Conclusion two

By using select * from performance_schema.data_locks;and operating practices, and can see the relationship LOCK_MODE LOCK_DATE of:

LOCK_MODELOCK_DATALock range
X,REC_NOT_GAP1515 Row lock for that data
X, GAP1515 The gap before the data, not including 15
X1515 The gap of the data, including 15
  1. LOCK_MODE = X It is the interval between front opening and back closing;
  2. X,GAP It is the front opening and rear opening interval (gap lock);
  3. X,REC_NOT_GAP Row lock.

I have basically figured out the next-key lock range of the primary key. Note that the version uses 8.0.25.

doubt

  1. What is the next-key lock range of the unique index?
  2. What is the locked range and the locked index when the index is covered?
  3. Why do I say that this bug has not been completely fixed? It is also reproduced in the non-primary key unique index​.

The length of the article is limited, so friends can think for themselves first, try their own operation as much as possible, and learn from practice. As for the specific answer, it needs to be verified and summarized in the next article.