preface

You’ve already seen the locking range for primary key indexes and for indexes that are not unique to the primary key.

Primary key index:

  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.

Non-primary key unique 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. A row lock is applied to all the primary keys in the index range that are not primary key unique.
  5. There is also a bug where the next key locks the next range when a non-primary key unique index is locked.

In this article, we will look at the locking scope of common indexes and common fields.

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 idea is the same as for a non-primary key unique index, except that you are looking at the C and D fields.

Now that you know something about DATA_LOCKS, we’ll analyze it directly.

Normal index

Common index equivalent query — data exists

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

Analyze data_locks directly

  1. Ideographic lock;
  2. Index idx_c with a range of 210 open and close;
  3. Index idx_c adds interval 215 gap lock, LOCK_MODE isX,GAP;
  4. A row lock of 15 is added on the primary key, and LOCK_MODE isX,REC_NOT_GAP.

The main reason is that a normal index can’t lock a single record, so you want to lock the range of the field.

Normal index equivalent query — data does not exist

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

Analyze data_locks directly

  1. Ideographic lock;
  2. A gap lock for interval 215 has been added to index idx_c.

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

General index range query

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

– it is understandable if the index 215 is locked, but it is not so clear if the index 220 is locked, it is not completely fixed.

Common field

Normal fields are easier to understand.

For a common field, no matter which query, all records need to be scanned, so this lock is applied directly to the primary key, and the entire range is locked.

conclusion

This article builds on the first and second articles and determines the locking range directly by analyzing the data_locks information.

select * from performance_schema.data_locks;
LOCK_MODE LOCK_DATA The lock range
X,REC_NOT_GAP 15 The row lock for that data
X,GAP 15 The gap before that data, 15, does not include 15
X 15 The gap in that data, 15, contains 15
  1. LOCK_MODE = XIs the front open and back closed interval;
  2. X,GAPIs the front opening and back opening interval (gap lock);
  3. X,REC_NOT_GAPRow locks.

So we can draw the conclusion of common index and common field.

Normal index

  1. Ordinary index equivalent query, because the uniqueness cannot be determined, so even if the location of the record, it will also query backward until the query of the record is not the value, so as to lock the value of the interval;
  2. The lock on a normal index is also attached to that index. If an existing record is involved, a row lock is placed on the primary key.
  3. The general index range query, the next key query next interval of the same bug.

Common field

A normal field query will query the entire table, and this lock will lock all the ranges of the primary key.