mysql中使用临键锁 mysql 间隙锁和临键锁

本篇只分析mysql在可重复读隔离级别的加锁机制

前置知识
对于行级锁，主要分为以下三类：

1. 行锁（Record Lock）：锁定单个行记录的锁，防止其他事务对此行进行update和delete。在RC、RR隔离级别下都支持。
2. 间隙锁（Gap Lock）：锁定索引记录间隙（不含该记录），确保索引记录间隙不变，防止其他事务在这个间隙进行insert，产生幻读。在RR隔离级别下都支持。
3. 临键锁（Next-Key Lock）：行锁和间隙锁组合，同时锁住数据，并锁住数据前面的间隙Gap。在RR隔离级别下支持。

主要分为两个大的方向讨论
1.等值查询
2.范围查询

另，这两种情况又有多种的细分，等值查询可分为主键索引、唯一索引以及普通索引，每一种情况又包含命中和未命中，范围查询也一样

由于读分为当前读和快照读，而快照读实现为mvcc不加锁，只有当前读会加锁，所以只讨论当前读的范畴：select for update（更新和删除与此类似）或者select LOCK IN SHARE MODE

本文数据库版本号为5.7.30

我们使用下面这张 book 表作为实例，其中 id 为主键，ISBN（书号）为二级唯一索引，Author（作者）为二级非唯一索引，score（评分）无索引。

CREATE TABLE `book` (
	`id` INT(11) NOT NULL,
	`isbn` INT(11) NULL DEFAULT NULL,
	`author` INT(11) NULL DEFAULT NULL,
	`score` INT(11) NULL DEFAULT NULL,
	PRIMARY KEY (`id`) USING BTREE,
	UNIQUE INDEX `isbn` (`isbn`) USING BTREE,
	INDEX `author` (`author`) USING BTREE
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB;

最简单的情况：不使用索引

UPDATE book SET score = 9 WHERE score = 2

（强调一点：间隙锁和间隙锁不冲突，所以这种情况下 select * from book where id =12 for update这个语句不会阻塞，但是insert into book values(12,12,221,2)这种插入语句会被阻塞，所以间隙锁只会阻塞插入请求，验证间隙锁的范围也只能通过插入验证）

mysql加锁机制为锁索引，所以查询用不到索引的话，会直接锁表，

下面的请求直接阻塞：

select * from book where id =10 for update;（记录锁会相互阻塞）

insert into book values(12,12,221,2)

主键索引，等值命中

比如：select score from book where id = 20 for update;

其他事务所有id为20的查询写锁都被阻塞

（如：select score from book where id = 20 for update;）

结论：锁住主键20所在的B+树这一条记录

唯一索引，等值命中
比如：select isbn from book where isbn= 20 for update;
其他事务所有isbn为20的查询写锁都被阻塞
（如：select isbn from book where isbn= 20 for update;）
其他事务通过主键20的查询写锁也被阻塞
（如：select id from book where id = 20 for update;）
结论：锁住唯一索引20所在B+树的记录和主键20所在的B+树这一记录

普通索引，等值命中
比如：select id from book where author = 222 for update;
其他事务所有author为（111,333）区间的写锁都被阻塞
（如：select id from book where author = 222 for update;
insert into book values(60,60,221,2);
insert into book values(60,60,223,2);）
而insert into book values(60,60,333,2);insert into book values(1,60,111,2);可以成功，（一个问题：insert into book values(60,60,111,2);失败，包括其他值不变，主键为12,22,32,42,52都会失败？？？）

其他事务通过主键20的查询写锁也被阻塞
（如：select id from book where id = 20 for update;
select id from book where id = 30 for update;）
结论：锁住普通索引（111,333）所在B+树的记录和主键20、30所在的B+树的记录（主键锁住的值有问题）