Locking of MySQL Ordinary Index

Preface

The lock range of the primary key index and the lock range of the non-primary key unique index have been introduced earlier.

Primary key index:

  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. Version 8.0.17 is open before and then closed, and version 8.0.18 and later, modified to 前开后开interval;
  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.

Non-primary key unique index:

  1. Non-primary key unique index equivalent query, data exists, for update will lock the primary key, and for share will only lock on its own index when it is covering the index;
  2. Non-primary key index equivalent query, the data does not exist, regardless of whether the index is covered, it is equivalent to a range query, only the non-primary key index will be locked, and the gap lock will be added, and the interval will be opened after opening;
  3. When querying the range of a non-primary key unique index, not a covering index, the corresponding range will be added with the opening and closing interval, and if there is data, the row lock will be added to the corresponding primary key;
  4. When querying a range of a non-primary key unique index, if it is a covering index, row locks will be added to all the primary keys corresponding to the closed interval;
  5. When the non-primary key unique index is locked, there is still a bug that next-key locks the next interval.

In this article, let's take a look at what is the locking range of ordinary indexes and ordinary fields?

Database table data

CREATE TABLE `t` (
  `id` int NOT NULL COMMENT '主键',
  `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 idea is the same as that of a non-primary key unique index, but the only difference is that we look at the c and d fields.

Because the previous friends should have a certain understanding of data_locks, here we will directly analyze the data information of data_locks.

Normal index

Ordinary index equivalent query-data exists

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

Analyze data_locks directly

  1. Express intention lock;
  2. The index idx_c adds the 210 interval before opening and then closing;
  3. A gap lock of interval 215 is added to the index idx_c, and the LOCK_MODE is X,GAP;
  4. A row lock of 15 is added to the primary key, and LOCK_MODE is X,REC_NOT_GAP.

The main reason is that ordinary indexes cannot uniquely lock a record, so the front and back ranges of the field must be locked.

Ordinary index equivalent query-data does not exist

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

Analyze data_locks directly

  1. Express intention lock;
  2. A gap lock of 215 intervals has been added to the index idx_c.

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

Ordinary index range query

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

It is understandable to lock the front opening and back closing interval of 215 indexed by idx_c, but it is not understandable when 220 is locked. It should be that the bug has not been completely repaired.

Common field

Ordinary fields are better understood.

For ordinary fields, no matter which query it is, all records need to be scanned, so this lock is directly added to the primary key, and it locks all intervals.

to sum up

Based on the first and second articles, this article directly analyzes the data_locks information to determine the lock range.

select * from performance_schema.data_locks;
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.

So as to draw the conclusion of common index and common field.

Normal index

  1. Ordinary index equivalent query, because the uniqueness cannot be determined, even if the record is located, it will be queried backwards until the record that is not the value is queried, thereby locking the range of the value;
  2. The lock of the ordinary index is also loaded on the index. If the existing record is involved, the row lock will be added to the primary key;
  3. The range query of the ordinary index also has the bug of next-key querying the next range.

Common field

Ordinary field query will query the entire table. If locked here, all intervals of the primary key will be locked.