preface

The locking range for primary key indexes and non-primary key unique indexes has been described.

MySQL next-key lock MySQL next-key lock MySQL next-key lock

MySQL Next-key lock bug not fixed

Primary key index:

  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.

Non-primary key unique index:

  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, a row lock is added to all primary keys corresponding to the closed range if the index is an overwrite index.
  5. There is still a next-key lock bug when a non-primary key unique index is locked.

This article will look at the common index and common field lock range is what?

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:

The idea is the same as for non-primary key unique indexes, except that we are looking at c and D fields.

Since you already know datA_LOCKS, let’s analyze the datA_LOCKS data directly.

Normal index

Plain index equivalent query – data exists

mysql> begin; select * from t where c = 210 for update;
Copy the code

Analyze datA_LOCKS directly

  1. Ideographic lock;
  2. Index IDX_C added a front open back latching of 210 interval;
  3. Index IDx_C added interval 215 gap lock, LOCK_MODE isX,GAP;
  4. Added row lock 15 on primary key, LOCK_MODE is set toX,REC_NOT_GAP.

This is mainly because normal indexes cannot lock a single record uniquely, so the front and back ranges of the field are locked.

Plain index equivalent query – data does not exist

mysql> begin; select * from t where c = 211 for update;
Copy the code

Analyze datA_LOCKS directly

  1. Ideographic lock;
  2. Interval 215 gap lock was added to index IDx_C.

Analysis is because the data does not exist, just need to lock 215 gap, because 215 and 210 definitely do not belong to this range.

Plain index range query

mysql> begin; select * from t where c > 210 and c <= 215 for update;
Copy the code

It is understandable for idx_C index 215 to be locked before opening and after closing interval, but it is not quite understandable for idx_C index 220 to be locked.

Common field

Plain fields are a little easier to understand.

For a normal field, any query would need to scan all records, so the lock is placed directly on the primary key and holds the entire range.

conclusion

Based on the first and second articles, this article analyzes datA_LOCKS information directly to determine the scope of locking.

select * from performance_schema.data_locks;
Copy the code
LOCK_MODE LOCK_DATA The lock range
X,REC_NOT_GAP 15 15 Row lock for that data
X,GAP 15 The gap before 15 does not include 15
X 15 The gap between the 15 data contains 15
  1. LOCK_MODE = XIs the front open after closed interval;
  2. X,GAPIs the front open after open interval (clearance lock);
  3. X,REC_NOT_GAPRow locks.

This leads to common indexes and common fields.

Normal index

  1. Ordinary index equivalent query, because the uniqueness cannot be determined, even if the record is located, the query will be backward until the record is not queried with this value, so as to lock the range of this value.
  2. A normal index lock is also loaded on that index. If an existing record is involved, a row lock is applied to the primary key.
  3. Common index range query, the same next-key query next range bug.

Common field

A normal field query will query the entire table, where the lock will lock all intervals of the primary key.