mysql不同情况下加锁类型实验 record lock 和 gap lock

首先确定RR级别下mysql的加锁规则(实验环境 mysql 8.0)：

1. 加锁的基本单位是 next-key lock。是一个前闭后开的区间，也是record lock 和 gap lock 的组合。(很多人说是 左开右闭的区间 包括丁奇大神，我觉得可能是mysql版本的原因)，
2. 查找过程中访问到的对象才会加锁。

准备数据

注：下面测试数据所在环境，数据库的事务隔离级别都是RR级别

CREATE TABLE `gap_record` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `bk` int DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  KEY `gap_record_bk_IDX` (`bk`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin

等值条件主键 加锁类型

加锁类型看 mysql 的 performance_schema库下面的data_locks表

begin;
update gap_record set bk = 1 where id = 2;
rollback; 

这种情况 next-key lock退化成 行锁。

第一行 先对表加的意向排他锁 IX

第二行锁的数据是 id=2 的一行。
注意LOCK_MODE是REC_NOT_GAP，如果没有这个标识 也没有 GAP 标识，那么代表锁住的是 id (1,2]这个区间。

范围条件主键

begin;
update gap_record set bk = 1 where id < 3;
rollback;

id<3 只有两条符合的数据 ，但是却在id=3这一行也加了锁 但是LOCK_MODE是GAP
相当于在 id [2,3)这个区间加了锁。
id = 1 ,2 两处 LOCK_MODE没有GAP标识 也没有REC_NOT_GAP标识
也就是加了 next-key lock ： (-∞,1),[1,2) 这个区间也加了锁。

非主键 非唯一索引 等值条件 加锁类型

begin;
update gap_record set bk = 1 where bk = 100;
rollback;

这种情况，扫描到不符合条件的记录时 next-key lock退化成 gap lock

第二行查询结果：bk [59,100)这个区间加了next-key lock。
第三行查询结果：因为 select * 而且索引用的非聚簇索引，所以发生了回表，查了主键索引，对id = 100的主键索引 也加了锁。只是一个 行锁。因为LOCK_MODE 是 REC_NOT_GAP。
第四行查询结果：在bk [100,150) 这个区间加了gap lock 。此时 bk= 150 这样的记录是可以插入的。

采用当前读试一下

begin;
SELECT * FROM gap_record where bk = 100 FOR UPDATE ;
rollback;

发现加锁的位置和类型都一样。

此时我们先不急rollback 再新开一个数据库会话。执行下面的插入

begin;
INSERT into xingluo_ods.gap_record values (null,60) ;
rollback;

由于[100,150)的gap lock以及[59,100)的next-key lock ，bk 值在 [59,150)之间的都不能插入 。 bk=150是可以插入的

非主键 非唯一索引 范围

begin;
SELECT * FROM gap_record where bk < 150 FOR UPDATE ;
rollback;

所有非主键索引都加上了 next-key lock 包括 (100,150]这个区间。因为在查找时候 扫描到了 bk=150这条数据的索引。所以也会加锁。

同样插入bk=150

begin;
INSERT into xingluo_ods.gap_record values (null,150) ;
rollback; 

是插入成功的。说明bk=150这个索引位置没有被锁住。这也侧面印证了 mysql8 的 next-key lock 是左闭右开的区间。