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) ) ENGINEInnoDB AUTO_INCREMENT0 DEFAULT CHARSETutf8;初始化数据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 字段的 (14) 区间查看锁信息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 字段的 (14) 区间。结论查询条件为唯一索引且空值间隙锁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 隔离级别当查询条件为主键和唯一索引当有值时是行锁当查询条件为主键和唯一索引当为空值时是间隙锁当查询条件为普通索引是间隙锁当查询条件为索引且为范围查询是间隙锁当查询条件无索引是表锁。至于间隙锁的范围如何计算本文没有详细阐述但是上面的这些规则就能基本满足我们日常工作需要。如果想知道间隙锁区间如何计算欢迎催更哈~~写文不易家里还要带娃又肝了一个周末不过大家的支持永远是楼仔输出的最大动力。