A brief discussion on the lock range of MySQL next-key lock

Preface

One day, I was suddenly asked about MySQL's next-key lock, and my immediate reaction was:

What the hell is this? ? ?

I can't see anything in this screenshot?

Looking closely, 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 official website's explanation roughly means: the 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.

Give yourself a bunch of little question marks first? ? ?

• When locking the primary key, unique index, common index, and common field, which indexes are locked?
• Which ranges of data are locked by different query conditions?
• What are the lock scopes for value queries and range queries such as for share and for update?
• What is the lock scope when the queried equal value does not exist?
• What is the difference when the query conditions are primary key, unique index, and common index?

Since I don’t understand anything, I have to start from the beginning and practice!

Let’s first look at the conclusion of Mr. Ding Qi in “MySQL 45 Lectures”:

After reading this conclusion, most of the questions should be answered, but there is one very important sentence that needs attention: MySQL 后面的版本可能會改變加鎖策略，所以這個規則只限于截止到現在的最新版本，即5.x 系列<=5.7.24，8.0 系列<=8.0.13

Therefore, the above rules may not be applicable to the current version. Below I will take MySQL 8.0.25 as an example to verify the next-key lock range from multiple angles.

Environment 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 

To learn how to install MySQL using Docker, refer to another article: Install and connect to MySQL using Docker.

Primary key index

First, let's verify the range of the next-key lock of the primary key index.

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

Primary key equal value query - data exists

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

This SQL statement locks id = 10 You can first think about what lock is added? What data is locked?

You can view the lock information through data_locks . The SQL is as follows:

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

For specific field meanings, please refer to the official documentation

The results mainly include information such as engines, libraries, and tables. 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 lock and TABLE table-level lock.
• LOCK_MODE: Lock type: S, X, IS, IX, and gap locks
• LOCK_DATA: lock-associated data. For InnoDB, when LOCK_TYPE is RECORD (row lock), the value is displayed. When the lock is on a primary key index, the value is the primary key value of the locked record. When the lock is on a secondary index, the value of the secondary index is displayed, appended with the primary key value.

The result is obvious. Here, an IX lock is added to the table and an X,REC_NOT_GAP lock is added to the record with primary key index id = 10, indicating that only the record is locked.

Similarly, for share , an IS lock is added to the table and an S lock is added to the record with primary key index id = 10.

It can be concluded that:

When locking the primary key value and the value exists, an intention lock is added to the table and a row lock is added to the primary key index.

Primary key equal value 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.

• id = 11 definitely does not exist. But after adding for update , next-key lock is required, and the interval of id = 11 is the first open and the second closed interval of (10,15);
• Because it is等值查詢, there is no need to lock the record id = 15 , and the next-key lock will degenerate into a gap lock;
• The final interval is the front-open and back-open interval of (10,15).

Use data_locks to analyze the lock information:

Look at the lock information X,GAP indicates that a gap lock is added, where LOCK_DATA = 15, indicating that the lock is for the gap before the primary key index id = 15.

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

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

Primary key range query (key point)

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:

• id >= 10 locates the interval (10,+∞) where 10 is located;
• Because >= has an equal value judgment, the value 10 needs to be included, and it becomes a closed interval [10,+∞);
• id < 11 limits the subsequent range, so based on 11, the next interval is determined to be the first open and the second closed interval of 15;
• Combined together, it is [10,15]. (Not entirely correct)

First look at data_locks

You can see that in addition to the table lock, there is also a row lock ( X,REC_NOT_GAP ) for id = 10 and a gap lock ( X,GAP ) before the primary key index id = 15.

So in fact id = 15 can be updated. That is to say, there is a problem前開后閉區間. I personally think it should be the condition judgment of id < 11 , which leads to the unnecessary locking of row 15.

The result verification is also correct, id = 12 is blocked for insertion, and id = 15 is updated successfully.

What if the right side of the range contains an equal value query?

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

Let's analyze this SQL:

id > 10 is located in the interval (10, +∞) where 10 is located; id <= 15 is located in (-∞, 15]; combined, it is (10, 15].

Also take a look at data_locks

It can be seen that only one X lock is added for the primary key index id = 15.

Verify that id = 15 can be updated? Verify again whether id = 16 can be inserted?

It turns out 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.

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

Reference link address:

https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-18.html

Search keywords: Bug #29508068)

We can reproduce it with 8.0.17 respectively:

In 8.0.17, id <= 15 will also lock the data of id = 20, but in version 8.0.25, it will not. So this bug has been fixed.

Let's take a look at the question of whether it is前開后閉前開后開open at the back. Let's be rigorous and compare 8.0.17 and 8.0.18.

Now I estimate that it is very likely that when Bug #29508068 was fixed in version 8.0.18, this前開后閉was optimized to open-前開后開.

Compare data_locks data:

Note the red underlined part. In version 8.0.17, when id < 17 , LOCK_MODE is X , while in version 8.0.25 it is X,GAP .

Summarize

This article mainly verifies the next-key lock range when locking the primary key through actual operations, and consults materials and compares versions to draw different conclusions.

Conclusion 1:

• When locking, an intention lock, IX or IS, is first added to the table;
• 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)
• For primary key equal value queries, if the data exists, a row lock X,REC_NOT_GAP will be added to the value of the primary key index;
• For primary key equal value queries, if the data does not exist, a gap lock X,GAP will be added to the gap where the primary key value of the query condition is located;
• Primary key equal value query and range query are more complicated:
  • Version 8.0.17 is open at the front and close at the back, while version 8.0.18 and later have been optimized. The primary key is judged to be unequal and the interval closed at the back will not be locked.
  • When performing a critical <= query, 8.0.17 will lock the first open and last closed interval of the next next-key, but 8.0.18 and later versions fix this bug.

After optimization, it opens later. I don’t know whether this is because after optimization, the primary key interval will be opened directly later, or because it is a bug. Specific friends can try it.

Conclusion 2

By using select * from performance_schema.data_locks; and operating practice, you can see the relationship between LOCK_MODE and LOCK_DATE:

LOCK_MODE = X is an open-first-closed interval; X,GAP is an open-first-closed interval (gap lock); X,REC_NOT_GAP is a row lock.

The next-key lock range of the primary key has been basically understood. Note that the version used is 8.0.25.

doubt

• What is the next-key lock range of the unique index?
• What are the lock range and locked index when the index is covered?
• The reason why I said this bug has not been completely fixed is that this bug was reproduced in a non-primary key unique index.

The article is limited in length, so you can think about it for yourself first and try it out yourself. Practice makes perfect. As for the specific answer, the next article will be needed to verify and summarize the conclusion.

This is the end of this article about MySQL next-key lock locking range. For more information about MySQL next-key lock locking range, please search 123WORDPRESS.COM's previous articles or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!