[mysql]幻读与间隙锁.锁规则

先来看一个语句

sql语句

CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `c` (`c`)
) ENGINE=InnoDB;

insert into t values
(0,0,0),
(5,5,5),

 

有三个字段,id(主键),c(索引),d(普通字段)

初始化2条数据(0,0,0),(5,5,5)

实验1

	SessionA	SessionB	SessionC
t1时刻	transaction start: select * from A where d=5 for update
t2时刻	update A set c=55 where d=5; (1,1,1)(5,55,5)
t3时刻		update A set d=5 where id=1. (1,1,5)(5,55,5)
t4时刻			insert into A(id,c,d) values(20,20,5) (1,1,5)(5,55,5)(20,20,5)
t5时刻	commit

 

1.t1时刻,根据查询条件,只有(5,5,5)返回.假设只锁定了(5,5,5)这条

2.t2时刻,(5,5,5)--->(5,55,5)

3.t3时刻,(1,1,1)--->(1,5,1) 为啥可以更新嘞,因为只锁住了(5,5,5)

4.插入一条,(20,20,5)

5.sessionA事务提交.

因为你只锁定了(5,5,5)这行,数据理应变为 (5,55,5),(1,1,5),(20,20,5).

 

对于binlog怎么写的呢?

update A set d=5 where id=1

insert (10,5,5)

update A set c=55 where d=5

如果你拿着这个binlog去恢复数据或者拿着binlog去同步到从节点,数据库里就变成了(1,55,5),(5,55,5),(20,55,5),amazing.

这时候,binlog和库里的数据不一致,这个问题很严重了.

所以为了保证binlog里的顺序,SessionB的更新语句,需要在sessionA的更新后面.因此,要对扫描到的所有行,加上行锁.

 

 

	SessionA	SessionB	SessionC
t1时刻	transaction start: select * from A where d=5 for update
t2时刻	update A set c=55 where d=5; (1,1,1)(5,55,5)
t3时刻		update A set d=5 where id=1. (block)
t4时刻			insert into A(id,c,d) values(20,20,5) (1,1,1)(5,5,5),(20,20,5)
t5时刻	commit

 

现在当扫描的时候,会把所有行都block掉,这样避免了SessionB更新的问题.但是,避免不了c的插入问题.

此时binlog为
 

SessionC: insert into A(id,c,d) values(20,20,5) #数据变更为 (1,1,1)(5,5,5),(20,20,5).

SessionA:update A set c=55 where d=5; #数据变更为 (1,1,1)(5,55,5),(20,55,5).

SessionB:update A set d=5 where id=1; #数据变更为 (1,1,5)(5,55,5),(20,55,5).

发现还是有问题.因为插入的操作,被误更新了.

所以mysql引入了间隙锁.所谓间隙锁,见名知意,数据之间存在间隙.

 

3条数据,会有4个间隙

这样,在SessionC执行的时候,由于SessionA已经加了间隙锁,锁住了

间隙锁（Gap Lock）是Innodb在提交下为了解决幻读问题时引入的锁机制，

那么具体间隙锁和行锁是怎么共同使用呢?以及有什么规则呢?  感谢极客时间.

包含了两个“原则”、两个“优化”和一个“bug”。

• 原则 1：加锁的基本单位是 next-key lock。next-key lock 是前开后闭区间。
• 原则 2：查找过程中访问到的对象才会加锁。
• 优化 1：索引上的等值查询，给唯一索引加锁的时候，next-key lock 退化为行锁。
• 优化 2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock 退化为间隙锁。
• 一个 bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止。   8版本之后就没有了

我们需要搭建一个数据库,然后做各种测试.

可以查看https://blog.csdn.net/u012997470/article/details/110408038