以下文章来源于楼仔 ,作者楼仔
如有侵权,联系删除
文章目录:
01 环境准备
在验证之前,我们先准备好具体的环境和数据,事务隔离级别 RR,数据库版本 5.7.26。
为了方便测试,索引都是整型:
CREATE TABLE user (
id int(11) unsigned NOT NULL AUTO_INCREMENT,
user_no int(11) NOT NULL COMMENT '用户编号',
user_name varchar(16) DEFAULT NULL COMMENT '用户名',
age int(3) DEFAULT NULL COMMENT '年龄',
PRIMARY KEY (id),
UNIQUE KEY un_idx_user_no (user_no),
KEY idx_age (age)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;
初始化数据:
insert into user values(1, 10, '楼仔', 18);
insert into user values(4, 15, '二哥', 28);
insert into user values(8, 20, '一灰', 38);
常用命令操作:
> start transaction; // 开启事务
> commit; // 提交事务
> rollback; // 回滚事务
> select @@transaction_isolation; // 查看事务隔离级别
> select @@version; // 查看数据库版本
> SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS; // 查询锁
02 场景分类
2.1 主键(有值)
说明:主键查询,查询数据存在。
执行悲观锁查询:
select * from user where id = 1 for update;
执行更新操作,被锁住了:
update user set user_name = "楼仔小弟" where id = 1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
查看锁信息:
-
lock_mode 为 X(排他锁)
-
lock_type 为 RECORD,行级锁
结论:查询条件为主键,且有值,行锁
2.2 主键(空值)
操作:主键查询,查询数据不存在。
执行悲观锁查询:
select * from user where id = 2 for update;
执行插入操作,被锁住了:
insert into user values(3, 14, '楼仔小弟', 28);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
这里的间隙锁,锁住的区间是 id 字段的 (1,4) 区间,查看锁信息:
-
lock_mode 为 X(排他锁)+ Gap(间隙锁)
-
lock_type 为 RECORD,行级锁
结论:查询条件为主键,且空值,间隙锁
2.3 唯一索引(有值)
说明:唯一索引查询,数据存在。
执行悲观锁查询:
select * from user where user_no = 10 for update;
执行更新操作,被锁住了:
update user set user_name = "楼仔小弟" where user_no = 10;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
结论:查询条件为唯一索引,且有值,行锁
2.4 唯一索引(空值)
说明:唯一索引查询,数据不存在。
执行悲观锁查询:
select * from user where user_no = 11 for update;
执行插入操作,被锁住了:
insert into user values(3, 14, '楼仔小弟', 28);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
这里的间隙锁,锁住的区间是 user_no 字段的 (1,4) 区间。
结论:查询条件为唯一索引,且空值,间隙锁
2.5 普通索引(有值)
说明:普通索引,数据存在。
执行悲观锁查询:
select * from user where age = 18 for update;
执行更新操作,被锁住了:
update user set user_name = "楼仔小弟" where age = 18;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
执行插入操作,被锁住了:
insert into user values(3, 14, '楼仔小弟', 20);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
这里锁住的是 age 字段的 [18, 28) 这区间。
结论:查询条件为普通索引,且有值,间隙锁
2.6 普通索引(空值)
说明:普通索引,数据不存在。
执行悲观锁查询:
select * from user where age = 19 for update;
执行插入操作,被锁住了:
insert into user values(3, 14, '楼仔小弟', 20);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
这里锁住的是 age 字段的 (18, 28) 这区间。
结论:查询条件为普通索引,且空值,间隙锁
2.7 索引(范围查询)
说明:这里的索引,包括主键索引、唯一索引和普通索引。
执行悲观锁查询:
select * from user where id > 1 for update;
执行插入操作,被锁住了:
insert into user values(3, 14, '楼仔小弟', 20);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
这里其实可以对 id = 1 的数据进行更新,对于其它数据,都被锁住,锁住的范围是 id 字段的 (1, 4],(4, 8],(8, 正无穷) 区间。
结论:查询条件为索引,且是范围查询,间隙锁。
2.8 无索引
执行悲观锁查询:
select * from user where user_name = "楼仔" for update;
执行插入操作,被锁住了:
insert into user values(3, 14, '楼仔小弟', 20);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
这里明显是锁表了,但是为什么锁的信息还是行锁呢,知道的同学,可以私我哈~~
结论:查询条件为无索引,表锁。
03 加锁规则
3.1 规律总结
我们把上面的结论进行汇总:
总结如下规律:
-
当查询条件为主键和唯一索引,当有值时,是行锁;
-
当查询条件为主键和唯一索引,当为空值时,是间隙锁;
-
当查询条件为普通索引,是间隙锁;
-
当查询条件为索引,且为范围查询,是间隙锁;
-
当查询条件无索引,是表锁。
3.2 加锁规则
那是否有一套加锁规则呢?
为了便于大家理解,我先普及 3 个概念:
-
Record Lock:行锁
-
Gap Lock:间隙锁,锁定一个范围,但不包含记录本身
-
Next-Key Lock:行锁 + 间隙锁,左开右闭,比如(1,5]
其实 MySQL 大佬林晓斌在极客时间讲过,后来也有很多博主转发过他的加锁规则,我直接把这套规则贴一下。
两个“原则”:
-
原则 1:加锁的基本单位是 next-key lock,其中 next-key lock 是前开后闭区间;
-
原则 2:查找过程中访问到的对象才会加锁。
两个“优化”:
-
优化 1:索引上的等值查询,给唯一索引加锁的时候,next-key lock退化为行锁;
-
优化 2:索引上的等值查询,向右遍历时且最后一个值不满足等值条件的时候,next-key lock 退化为间隙锁。
3.3 分析一下
这里我们结合上面的案例,来解读这套加锁规则。
针对我们前面总结的 5 条规律,我们先分析这两条:
-
当查询条件为主键和唯一索引,当有值时,是行锁;
-
当查询条件为主键和唯一索引,当为空值时,是间隙锁。
下面我们根据 “两个原则” + “两个优化” 来分析一下。
根据 “原则 1”,加锁的基本单位是 next-key lock,当 “索引上为等值查询” 时(即能查到该数据),根据 “优化 1”,间隙锁退化为行锁。
同理,根据 “优化 2”,索引上的等值查询,向右遍历时且最后一个值不满足等值条件的时候,next-key lock 退化为间隙锁。
再分析这两条:
-
当查询条件为普通索引,是间隙锁;
-
当查询条件为索引,且为范围查询,是间隙锁;
同上,通过 “原则 1” 和 “优化 2”,普通索引 是加的间隙锁。
对于范围查询,个人认为上面的规则还不能完全覆盖,当时林晓斌针对这些规则,举了 4 个示例,然后进行详细剖析,包括间隙锁的范围区间计算。
因为篇幅原因,这里就不再详细展开,如果后续需要,我也可能会单独出一篇。
04 写在最后
最后我们再回顾一下(RR 隔离级别):
-
当查询条件为主键和唯一索引,当有值时,是行锁;
-
当查询条件为主键和唯一索引,当为空值时,是间隙锁;
-
当查询条件为普通索引,是间隙锁;
-
当查询条件为索引,且为范围查询,是间隙锁;
-
当查询条件无索引,是表锁。
至于间隙锁的范围,如何计算,本文没有详细阐述,但是上面的这些规则,就能基本满足我们日常工作需要。