preface

In the previous article, “What is the scope of MySQL Next-Key Lock?” The locking scope of the primary key index has been described in the

  1. When a table IS locked, an intention lock, IX or IS, IS added to the table.
  2. If multiple ranges are locked, multiple locks are added separately, and each range has a lock. (This can be done if id < 20)
  3. Primary key equivalent query. If the data exists, a row lock is placed on the value of the primary key indexX,REC_NOT_GAP;
  4. Primary key equivalent query. If the data does not exist, a gap lock will be added to the gap where the primary key value of the query condition isX,GAP;

  5. Primary key equivalent query, range query situation is more complicated:

    1. In 8.0.17, it is open and closed, while in 8.0.18 and later, it is changed toBefore open after openInterval;
    2. The critical< =In 8.0.18 and later versions, 8.0.17 will lock the opening and closing range of the next next-key when querying.

This article gives you a hands-on look at non-primary key unique indexes.

Database table data

CREATE TABLE 't' (' id 'int NOT NULL COMMENT' primary key ', 'a' int DEFAULT NULL COMMENT ', 'c' int DEFAULT NULL COMMENT '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

The database data is as follows:

The database field A 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;

Parse this SQL:

  1. Select * from the table where the query condition is returned;
  2. Unique index, so no query needs to continue after the data is located;
  3. The guess is to lock the unique index and the corresponding primary key index.

Check the data_locks

  1. IX table lock;
  2. The index uniq_a has been addedX,REC_NOT_GAPRow lock, where110, 10A = 110, and the next 10 is the primary key for this row;
  3. The primary key id = 10 was addedX,REC_NOT_GAPRow locks.

Everything is as it was analyzed.

If you replace “for update” with “for share”, it is the same, with a lock on both the primary key and the unique index.

SQL > SELECT * FROM SQL > SELECT id FROM SQL > SELECT id FROM SQL > SELECT id FROM SQL > SELECT id FROM SQL > SELECT id FROM SQL > SELECT id FROM SQL > SELECT id FROM SQL >

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

Parse this SQL:

  1. Select id from table; select id from table; select id from table;
  2. Unique index, so no query needs to continue after the data is located;
  3. So does this lock the primary key index or does it lock both?

So it doesn’t make any difference.

For update instead of for share, here’s the difference:

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

Obviously, when For Share overwrites an index, it is only locking its own index.

update t set c = 2101 where id = 10;

Is it possible to update C using 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;

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

For update will lock on the primary key, while for share will lock on its own index only if the index is overwritten.

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

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

Parse this SQL:

  1. For update: lock on uniq_a and primary key index;
  2. Field A is unique, but dataa = 111It doesn’t exist, it keeps looking until it reaches 115;
  3. So the gap lock for the index uniq_a and the primary key index is added. (Not true.)

As it turned out, the analysis was incorrect.

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

If the data does not exist in the non-primary key index, it is equivalent to a range query, and only locks on the non-primary key index. The lock is still a gap lock, and the interval is opened before and after the lock.

What if I go index overlay? It turns out to be the same thing.

Non-primary key unique index range query

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

Analysis of SQL

  1. If (a = 110 and a < 115) and (a = 110 and (a < 115));
  2. The corresponding primary key index 10 should also be locked!

Wrong again, as it turns out!

Analysis data_locks:

  1. How can I lock 110 on an index that is not unique to the primary key? The LOCK_MODE is still X, and I understand if I add a row lock.
  2. How can I lock 115 on an index that is not unique to the primary key?

It’s clear that the gap between 110 and 115 and their own records are locked.

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

A next-key lock on a primary key index can be used to lock a primary key index. The next key lock can be used to lock a primary key index.

If you know the reason, you can tell me.

What if I change my SQL to something like this?

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

Aye???????

That’s weird!

The only reason I can think of is that it’s open and closed. Because a times b times b is equal to 10 is going to be in the last interval, so we have to lock the last interval.

I can only say that three times!!

There is a conclusion:

When a range query is not a primary key unique index, a range is added to the corresponding range, and a row lock is added to the corresponding primary key if data is present.

What if you go to overwrite the index?

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

Following the previous train of thought, the front opens and the back closes:

  1. Lock the range of primary key index 110, the range of primary key 115
  2. Lock row locks on primary key 10

Wrong again!

It also locks the row lock on primary key 15.

If you get rid of the equal sign 15 is locked.

It feels like there’s not enough of it. The point is I didn’t understand how the primary key is open front and open back, and here it’s open front and close, right?

Am I going to try that bug here?

Pat on the face ah!

I mentioned earlier that this bug was fixed in 8.0.18, and optimized to open front and back, which is a direct indication that there is no fix.

Where a = 100 and a <= 115 for update; The next key is locked in the next key.

Try SQL

Obviously, this bug is not fixed on the non-primary key unique index!!

conclusion

In case of non-unique primary key index:

  1. The primary key index only equivalent query, data exist, will be the primary key lock for update, but for share only in the case of go cover index, can only lock on their index;
  2. Non-primary key index equivalent query, data does not exist, whether the index is overwritten or not, is equivalent to a range query, only the non-primary key index lock, add or gap lock, open interval;
  3. When an index is not overwritten, a range is added to the corresponding range, and if there is data, a row lock is added to the corresponding primary key.
  4. If the index is overridden, a row lock will be placed on all the primary keys of the backclosed range.

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

  1. It’s not a primary key, it’s not a primary key, it’s not a primary key and it’s a unique index that looks like it’s going to follow the open and close principle;
  2. Next Key bug on non-primary key unique index has not been fixed!

If you think about it, it seems understandable again.

The next-key bug on the primary key has been fixed, and the front-open and back-close interval has been optimized to be front-open and back-open interval, but the bug on the non-primary key unique index has not been fixed, so it is not optimized.

Well, that’s probably it!

Related to recommend

  • MySQL > insert next key lock into mysql. MySQL > insert next key lock into mysql. key
  • Use Docker to install and connect to MySQL
  • How does Spring resolve circular dependencies