preface

The three articles introduce the range of locking from the four aspects of primary key, non-primary key unique index, common index and common field through practical operation.

Here is a summary of this article.

data_locks

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.

I hope there is no mistake in my understanding of this separate introduction. If the boss sees it, he must help to correct it.

The 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.

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.

conclusion

Through practical operation, the biggest feeling is not high, reading or reading articles, must be practical operation.

On the paper come finally feel shallow.

Related articles

  • https://mp.weixin.qq.com/s/JS2gJHb1qS618TQISxMAAw
  • MySQL Next-Key Lock bug has not been fixed.
  • MySQL > SELECT * FROM ‘index’ WHERE ‘index’ = ‘index’ WHERE ‘index’ = ‘index’