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;

mysql行锁机制--未完_第1张图片

最简单的情况:不使用索引
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)
mysql行锁机制--未完_第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+树的记录(主键锁住的值有问题)

你可能感兴趣的