preface

MySQL Next-key lock (); MySQL next-key lock (); The primary key index lock scope has been introduced in the.

  1. Add an intent lock (IX or IS) to the table.
  2. If multiple scopes are locked, multiple locks are added separately and each scope has a lock. (This can be implemented in the case of id < 20)
  3. Primary key equivalent query, when the data exists, the value of the primary key index will be lockedX,REC_NOT_GAP;
  4. Primary key equivalent query. If the data does not exist, a gap lock is added to the gap where the primary key value of the query condition residesX,GAP;
  5. Primary key equivalent query, range query is more complex:
    1. Version 8.0.17 is open before closed, while version 8.0.18 and later, modified toBefore open after openInterval;
    2. The critical< =8.0.17 would lock the next next key’s open and close interval, but 8.0.18 and later fixed this bug.

This article will work with non-primary key unique indexes.

Database table data

CREATE TABLE `t` (
  `id` int NOT NULL COMMENT 'primary key'.`a` int DEFAULT NULL COMMENT 'Unique index'.`c` int DEFAULT NULL COMMENT 'Normal index'.`d` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_a` (`a`),
  KEY `idx_c` (`c`))ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
Copy the code

The database data is as follows:

Field A of the database is the unique index.

Non-primary key unique index

Non-primary key unique index equivalent query — data exists

mysql> begin; select * from t where a = 110 for update;
Copy the code

Parse this SQL:

  1. Select * from ‘select *’;
  2. Unique index, so after locating the data do not need to continue the query;
  3. The guess is to lock the unique index and corresponding primary key index.

Check the data_locks

  1. IX table lock;
  2. Index uniq_AX,REC_NOT_GAPRow lock, where110, 10A = 110, and 10 is the primary key of the row;
  3. Added to primary key id = 10X,REC_NOT_GAPRow locks.

Everything is as analyzed.

If you replace “for update” with “for share”, you will do the same thing, locking both the primary key and the unique index.

The SQL that is executed here is select *. What if you replace it with select ID?

mysql> begin; select id from t where a = 110 for update;
Copy the code

Parse this SQL:

  1. Select ID, meet index coverage, will not return to the table;
  2. Unique index, so after locating the data do not need to continue the query;
  3. So is this primary key index locked or both?

So there’s no difference.

Replace “for update” with “for share”.

There are only two lock records: ideographic and UNIQ_A index S,REC_NOT_GAP lock.

Obviously, when for Share overwrites an index, it simply locks its own index.

update t set c = 2101 where id = 10;
Copy the code

Can c be updated with the primary key? What about the following two SQL statements?

update t set a = 1101 where id = 10;

update t set c = 2101 where a = 110;
Copy the code

The first one will execute, and the last two will block.

SQL > alter table share lock (); SQL > alter table share lock (); SQL > alter table share lock ();

Non-primary key unique index equivalent query — data does not exist

mysql> begin; select * from t where a = 111 for update;
Copy the code

Parse this SQL statement:

  1. Select * from uniq_a; select * from uniq_a; select * from uniq_a;
  2. Field A is unique, but the dataa = 111It doesn’t exist. It keeps looking until it reaches 115;
  3. So a gap lock is added for uniq_A and primary key indexes. (Not true)

As it turned out, the analysis was incorrect.

Update t set c = 2101 where id = 15; Also.

So, it can be interpreted as: non-primary key index equivalent query, data does not exist, equivalent to a range query, only the non-primary key index will be locked, or gap lock, open before open interval;

What if we go index overwrite at this point? In fact, the result is the same.

Non-primary key unique index range query

mysql> begin; select * from t where a >= 110 and a < 115 for update;
Copy the code

Analysis of SQL

  1. Select * from a where a >= 110 and a < 115;
  2. The corresponding primary key index 10 should also be locked!

Wrong again, it turns out!

Analysis data_locks:

  1. How can I lock 110 that is not the unique index of primary key? LOCK_MODE is still X, but I can understand if I have a row lock.
  2. Why lock 115 on index not unique to primary key?

Clearly the gaps between 110 and 115 and their own records are locked.

After some analysis, is it because the front is open and the back is closed?

Next-key lock (); next-key lock (); next-key lock ();

Someone who knows why can tell me.

What if I changed my SQL to something like this?

mysql> begin; select *  from t where a > 110 and a < 114 for update;
Copy the code

Aye???????

That’s weird!

The only reason I can think of is because it’s open and closed. Because a >= 10 is equal to the upper interval, so we need to lock the upper interval.

I can only say meng force three even!!

There are some conclusions:

In a non-primary key unique index range query, an open before closed interval is applied to the corresponding range, and a row lock is applied to the corresponding primary key if data exists.

What if you overwrite the index?

mysql> begin; select id from t where a >= 110 and a < 115 for update;
Copy the code

Open front and close back:

  1. Lock primary key index 110 range, 115 range
  2. Locks the row lock for primary key 10

Wrong again!

The row lock for primary key 15 is also locked.

Get rid of the equal sign. 15 is locked.

I feel like I don’t have enough brains. The point is I don’t understand why the primary key is still front open and back open, so this is front open and back closed, right?

Am I trying that bug here?

Slap your face!

This bug was fixed in 8.0.18 and was optimized to have a front and back open interval.

A > 100 and a <= 115 for update; It locked 120 for me. It’s a next-key bug.

Try SQL

This bug is not fixed on non-primary key unique indexes.

conclusion

In the case of non-primary key unique indexes:

  1. SQL > select * from share where SQL > select * from share where SQL > select * from share where SQL > select * from share where SQL > select * from share where SQL > select * from share where SQL > select * from share where SQL > select * from share where SQL > select * from share where SQL > select * from share where SQL > select * from share where SQL > select * from share where SQL > select * from share where SQL > create;
  2. Non-primary key index equivalent query, data does not exist, no matter whether the index coverage, equivalent to a range query, only on the non-primary key index lock, add or gap lock, open before open interval;
  3. In the case of non-primary key unique index range query, when the index is not overwritten, the corresponding range will be open before closed interval, and if there is data, the corresponding primary key will be locked.
  4. When a non-primary key unique index range is queried and an overwrite index is queried, a row lock is added to all primary keys corresponding to the closed range.

After practicing all the operations in this article, I am in a somewhat confused state. The version I use is 8.0.25

  1. The primary key is not open and closed, not the primary key and the unique index seems to follow the open and closed principle very well;
  2. Next key bug in non-primary key unique index, not fixed!

On second thought, it seems understandable.

Because the next-key bug on the primary key was fixed, and the index on the primary key was optimized to be open before closing instead of open after closing, this bug was not fixed, so it was not optimized.

Well, that’s probably it!

Related to recommend

  • MySQL next-key lock MySQL next-key lock MySQL next-key lock
  • Install and connect to MySQL using Docker
  • How does Spring address loop dependencies