MySQL next-key lock MySQL next-key lock

preface

Three articles through the actual operation, respectively, introduced the primary key, non-primary key unique index, common index, common field four aspects of the scope of locking.

This article will conclude again.

data_locks

select * from performance_schema.data_locks;
Copy the code

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 separate introduction is to hope that THERE is no mistake in my understanding. If the big guy sees the mistake, he must help correct it.

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

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.

conclusion

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

The paper comes to light.

Related articles

• Mp.weixin.qq.com/s/JS2gJHb1q…
• MySQL Next-key lock bug not fixed
• MySQL > alter database lock ()