Lock List
- Shared and Column Exclusive Locks
- Intent Lock
- Record Lock
- Gap lock
- Next-Key Lock
- Insert Intent Lock
- AUTO-INC lock
This time we will only discuss and experiment with intent locks.
Gap lock
Gap locks are also index records of locks, locking values that do not exist in gaps.
-
What is gap lock?
Gap locks are locks on gaps between index records or between the first index record in a query or after the last index record.
Gap locks are blocked by locking a range where other transactions want to insert data that does not exist within that range. -
What can I do with the gap lock?
InnoDB's repeatable read transaction isolation level When a query uses a non-unique index or primary key, InnoDB uses gap locks to block insertions of other transactions between gaps. -
Gap Lock Classification
Gap locks can be divided into gap S-lock and gap X-lock.
Gap locks support adding gap shared locks and gap exclusive locks in the same gap between different transactions. -
Clearance lock example
Gap is a range, and the following SQL s use gap locks: -
Query a range using BETWEEN
select * from sys_user where age between 1 and 20 for update;
- Query a range using equivalents (premise: age is not a primary key or unique index)
Even if a query is an equivalent query, but the query range is either a non-primary key or a unique index, then the query also uses a gap1 to lock the index nodes.
select * from sys_user where age=1 for update；
Experiment
The following experiment is based on MySQL 8.0. Version X.
Table building statement:
CREATE TABLE `sys_user` ( `id` int NOT NULL AUTO_INCREMENT, `name` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT 'Full name', `name_pinyin` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT 'Phonetic Name', `id_card` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT 'ID number', `phone` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT 'Cell-phone number', `age` int(11) NOT NULL DEFAULT 1 COMMENT 'Age', PRIMARY KEY (`id`), UNIQUE KEY `uni_idx_id_card` (`id_card`) USING BTREE COMMENT 'unique index-ID number', KEY `idx_phone_name` (`phone`,`name`) USING BTREE COMMENT 'Normal Index-Cell-phone number' ) ENGINE=InnoDB AUTO_INCREMENT=3495 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='user'
Table structure:
MySQL [employees]> desc sys_user; +-------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+--------------+------+-----+---------+----------------+ | id | int | NO | PRI | NULL | auto_increment | | name | varchar(10) | NO | | NULL | | | name_pinyin | varchar(255) | NO | | NULL | | | id_card | varchar(255) | NO | UNI | NULL | | | phone | varchar(20) | YES | MUL | NULL | | | age | int(1) | NO | | 1 | | +-------------+--------------+------+-----+---------+----------------+ 5 rows in set (0.25 sec)
Index structure:
The age field has an index.
You can try gap locks on fields where there is no index on the condition.
Lock gap with gap lock
- Insert records within gap lock interval
Transaction A, locking records between 4000 and 5000 with gap locks
begin; select * from sys_user where age between 4000 and 5000 for update;
Transaction B, trying to insert a record with id 4001 between 4000 and 5000
begin; insert into sys_user values (4001, 'Little Six', 'xiaoliu', 200007510, 13000007509, 4001);
Transaction B executes after transaction A executes, resulting in transaction B timeout:
MySQL [employees]> begin; Query OK, 0 rows affected (0.00 sec) MySQL [employees]> insert into sys_user values (4001, 'Little Six', 'xiaoliu', 200007510, 13000007509, 4001); ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction MySQL [employees]>
- Insert record outside gap lock interval
Transaction A, using gap locks to lock records between ID <100
begin; select * from sys_user where age between 1 and 10 for update;
Transaction B, trying to insert a record with age 11 outside the gap:
begin
insert into sys_user (name, name_pinyin, id_card, phone, age)values ('Little Six', 'xiaoliu', 300000000, 13000008000, 11);
Transaction B can be inserted successfully normally.
MySQL [employees]> begin;
Query OK, 0 rows affected (0.00 sec)
MySQL [employees]> insert into sys_user (name, name_pinyin, id_card, phone, age)values ('Little Six', 'xiaoliu', 300000000, 13000008000, 11);
Query OK, 1 row affected (0.01 sec)
Gap lock interval
According to the search criteria, search left for the record value A closest to the search criteria, and right for the record value B closest to the search criteria, that is, the locked gap is (A, B).
For example, if where age=5 and age has 4 and 6, the range of gap locks is (4,6);
If there is no index in the query criteria, InnoDB locks records that already exist and do not exist in the entire table. Based on this feature, we want our queries to use or overwrite the index whenever possible.
More interval explanations can be referred to: https://blog.csdn.net/bigtree_3721/article/details/73731377
Be careful
- Gap locks can be used to prevent magic reads and transaction isolation levels can be reused.
- If the equivalence query condition uses a primary key or unique index, then the gap lock is not used, but the record lock is used directly.
- Gap locks can be disabled using the read committed transaction isolation level.
- Retrieval criteria must have an index (mysql scans the entire table without an index, which locks all records of the entire table, including those that do not exist, and other transactions cannot be modified without deleting or adding)
Reference resources
- https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html#innodb-record-locks
- https://dev.mysql.com/doc/refman/8.0/en/innodb-transaction-isolation-levels.html
- https://www.infoq.cn/article/zau0ewzsdtx9zofr6c8w
- https://blog.csdn.net/bigtree_3721/article/details/73731377