锁列表
- 共享与列排他锁
- 意向锁
- 记录锁
- 间隙锁
- Next-Key锁
- 插入意向锁
- AUTO-INC锁
这次我们只来讨论和实验意向锁。
间隙锁
间隙锁也是锁的索引记录，锁住间隙中不存在的值。
- 什么是间隙锁？
间隙锁是在索引记录之间的间隙上的锁，或者在查询的第一条索引记录之前或最后一条索引记录之后的间隙上的锁。
间隙锁通过锁住一个范围，如果其他事务想要在这个范围内插入该范围不存在的数据时就会被阻塞。
- 间隙锁可以用来做什么？
InnoDB的可重复读事务隔离级别在查询使用了非唯一索引或主键时，InnoDB会使用间隙锁来阻止阻塞其他事务在间隙之间的插入。
- 间隙锁分类
间隙锁可以分为间隙共享锁（gap S-lock）和间隙排它锁（gap X-lock）。
间隙锁支持在不同事务可以在同一间隙添加间隙共享锁和间隙排他锁。
- 间隙锁示例
间隙是一个范围，以下SQL都会使用间隙锁：
- 使用BETWEEN查询一个范围
select * from sys_user where age between 1 and 20 for update;- 使用等值查询一个范围(前提：age非主键或唯一索引)
就算一个查询是等值查询，但是查询范围是非主键或唯一索引，那么这个查询也会使用一个gap1来锁住索引节点。
select * from sys_user where age=1 for update；实验
以下实验基于MySQL 8.0.x版本。
建表语句：
CREATE TABLE `sys_user` ( `id` int NOT NULL AUTO_INCREMENT,
`name` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '姓名',
`name_pinyin` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '姓名拼音',
`id_card` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '身份证号',
`phone` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '手机号',
`age` int(11) NOT NULL DEFAULT 1 COMMENT '年龄',
PRIMARY KEY (`id`),
UNIQUE KEY `uni_idx_id_card` (`id_card`) USING BTREE COMMENT '唯一索引-身份证号',
KEY `idx_phone_name` (`phone`,`name`) USING BTREE COMMENT '普通索引-手机号' ) ENGINE=InnoDB AUTO_INCREMENT=3495 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='用户'
表结构：
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)
索引结构:
age字段有索引。
大家可以在where条件上没有索引的字段上尝试一下间隙锁。
使用间隙锁锁定间隙
- 间隙锁区间之内插入记录
事务A，使用间隙锁锁定4000~5000之间的记录
begin;
select * from sys_user where age between 4000 and 5000 for update;事务B，尝试在4000~5000之间插入一条id为4001的记录
begin;
insert into sys_user values (4001, '小六', 'xiaoliu', 200007510, 13000007509, 4001);事务B在事务A执行后执行，结果事务B超时：
MySQL [employees]> begin;
Query OK, 0 rows affected (0.00 sec)
MySQL [employees]> insert into sys_user values (4001, '小六', 'xiaoliu', 200007510, 13000007509, 4001);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
MySQL [employees]>
- 间隙锁区间之外插入记录
事务A，使用间隙锁锁定id<100之间的记录
begin;
select * from sys_user where age between 1 and 10 for update;事务B，尝试在间隙之外插入一条age为11的记录：
begin
insert into sys_user (name, name_pinyin, id_card, phone, age)values ('小六', 'xiaoliu', 300000000, 13000008000, 11);事务B可以正常插入成功。
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 ('小六', 'xiaoliu', 300000000, 13000008000, 11);
Query OK, 1 row affected (0.01 sec)
间隙锁锁定区间
根据检索条件向左寻找最靠近检索条件的记录值A，作为左区间，向右寻找最靠近检索条件的记录值B作为右区间，即锁定的间隙为（A，B）。
例如：where age=5的话并且age存在4和6的话，那么间隙锁的区间范围为（4,6）；
如果查询条件中没有索引，那么InnoDB会锁定整张表中已经存在的记录和不存在的记录，基于这个特性我们要让我们的查询尽可能的使用索引或者覆盖索引。
更多区间解释可以参考：https://blog.csdn.net/bigtree_3721/article/details/73731377
注意
- 间隙锁可以用来防止幻读，事务隔离级别可重复读使用。
- 如果等值查询条件使用了主键或者唯一索引，那么不会使用间隙锁，而是直接使用记录锁。
- 使用读已提交事务隔离级别可以禁用间隙锁。
- 检索条件必须有索引（没有索引的话，mysql会全表扫描，那样会锁定整张表所有的记录，包括不存在的记录，此时其他事务不能修改不能删除不能添加）