首先在说明间隙锁之前,我们说下mysql中的幻读
假设具有一张表 t
| 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), (10,10,10),(15,15,15),(20,20,20),(25,25,25); |
并插入了一些初始化的数据
那么,考虑如下的语句的执行,是如何加锁的(在可重复读隔离级别下)
begin;
select * from t where d=5 for update;
commit;
首先说,这个语句会命中d=5的这一行,然后加一个写锁,然后这个写锁会在commit的时候进行释放
其次,由于字段d没有索引,然后会进行一个全表扫描
然后重点来了,其他的行再扫描过程中会不会加锁呢
如果所有行都不加锁
执行如上语句会出现如下的问题
由于T1,T3,T5时刻都是查询的当前读,会导致将新修改的和新插入的数据一并读出来
这样就导致了幻读问题,即为同一个事务中前后两次查询同一个范围,却有新的数据出现
还会带来破坏锁的问题
sessionA在T1时刻声明了,将所有d=5的行锁住,但是后来插入了两条新的,如果对这两条新的数据进行修改的话,那么就破坏了原本的锁住所有d=5的数据
然后还会破坏数据的一致性
锁的设置是为了保证数据的一致性,但是如果出现了幻读问题,可能会带来日志上的不一致
如果按照如下进行更新
而且没有加上锁的话
在主库中,出现如下的情况
T1之后,id=5变成了5,5,100
T2之后,id = 0 变成了 0,5,5
T4过后,多了一行 1,5,5
sessionA原本想要将d=5修改的语义也不符了
那么不加任何的锁,这个设定并不合理,需要改,改为了将扫描过程中碰到的行,加上写锁
这样就能阻止session B妄想再修改id=0这一行了,
必须等到T6时刻后才能继续修改了
但是也可以看出来,把所有的记录都加上了锁,仍然阻止不了新插入的记录,虽然把所有的行都加上了锁,但是插入新的数据的这一操作,是对记录之间的间隙进行的操作,所以想要解决幻读问题,需要使用间隙锁
比如这个表中,有六个数据,d索引就有这七个间隔
然后在进行执行select * from t where d = 5 for update的时候,还会加上了间隙锁
然后说明下行锁和间隙锁的区别
行锁是具有区分的,分为读锁和写锁
而间隙锁之间不会出现冲突,其目的都是为了保护这个间隔不被插入新的数据,所以之间不出现冲突
间隙和行锁组合起来称为next-key lock,那么这个被锁住的表上共有七个next-key-lock为
(-无穷,0](0,5)一直到(25,+supermum)
但是出现了新的问题,假设一个语句这样执行
由于间隙锁 (5,10]的存在
sessionB的插入被A的间隙所阻塞了
sessionA的插入被B的间隙锁阻塞了
导致了死锁,因此mysql启动了死锁回滚
于是可以将隔离级别设置了读已提交,避免间隙锁的出现
同样将binlog设置为row,避免日志和数据的不一致
那么关于间隙锁如何加的,接下来我们就说一下
可以简单的分为了,两个”原则” 两个”优化” 一个bug
原则1:加锁的基本单位是 next-key-lock 前开后闭的区间
原则2:查找过程中访问到的对象都加锁
优化1:等值查询,在给唯一索引加锁的时候,next-key-lock 退化为行锁
优化2:向右遍历到最后一个不满足条件的值的时候,next-key-lock 退化为间隙锁
一个bug,唯一索引上的范围查询会访问到不满足条件的第一个值为止
我们拿下面的数据进行举例
| CREATE TABLE `t1` (
`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 t1 values(0,0,0),(5,5,5), (10,10,10),(15,15,15),(20,20,20),(25,25,25); |
接下来如何证明
上面的流程中对于id=8的被锁了,id为10的没有锁
可以判断得出
1.根据原则1,加锁的单位是next-key lock session A的加锁范围就是(5,10]
2,根据优化2,这是一个等值查询,会退化为间隙锁(5,10)
所以SessionB这个新插入的记录会被锁住,但是sessionC修改 id=10这行可以的
接下来这情况
这个加锁的方式就比较绕了
根据原则1,需要给(0,5]加上 next-key-lock
然后由于c并不是普通索引,所以不会立刻停止,而是继续向右遍历,查到了c=10,于是给(5,10]加上了索引
根据优化2,退化为了间隙锁(5,10)
于是sessionC中插入一个(7,7,7),会被间隙锁(5,10)锁住
但是由于锁只锁当前索引,而c上有一个索引,所以可以进行update语句
如果想过要绕过索引优化,可以采用如下方法:
1.避免使用lock in share mode,因为其加的是写锁
可以使用for update,执行for update的时候,会给主键索引上满足条件的行加上行锁
2.在查询的时候,多查询几种数据的值,比如使用select *就可以避免使用直接索引
索引范围是如何加锁的
select * from t where id>=10 and id<11 for update
加锁的步骤是
现在(5,10)加上next-key-lock,但由于优化,next-key-lock化为了行锁,只加了一行的行锁
然后是id = 15,于是SessionA的范围就是主键索引上,行锁id=10和next-key lock(10,15]
优化二并没有生效,是因为采用的范围查询
普通索引的范围查询
因为c并非是唯一索引,可能会出现相同的,
导致了在加上(5,10]这一区间锁的时候,因为是非唯一索引,所以并不会退化为行锁,最后SessionA加的是(5,10]和(10,15]这两个next-key lock
导致的插入8失败了
最后看,那个所谓bug的出现,就是如果唯一索引上进行范围查询,会进行额外的搜索
可以看出,实际上仍然往后走了一格,给(15,20]这个区间加上next-key lock
如果一个非主键的索引上存在等值的话,如何去应对的呢
假设
如何应对呢?
主键id不同,但是值都是10
这个加锁的范围为
首先在会加锁到第一个碰见的值,也就是加上一个next key lock (5,10]
然后加上第二个锁(10,10],
然后加上c(10,15],由于是一个等值查询,所以退化为了(10,15)的间隔锁
整体如上
limit语句的加锁能力
进行如下的语句,会加上什么样的锁呢
上面不限制12是因为delete语句中指明了只删除两个limit2的限制,于是在遍历到c(10,30)这一行,就不在往后找一行了
然后是一个死锁的问题
首先是根据lock in share mode 加上了 next-key lock (5,10] 和间隙锁 (10,15)
这时候sessionB理所当然的阻塞了,但是sessionB已经加上了间隙锁,而sessionA在下一时刻需要插入(8,8,8)这一行,发现被sessionB的间隙锁锁住了,发现死锁,进行回滚
这就说明了,加next-key lock是分为两步的,首先加间隙锁,然后加行锁,在加间隙锁的时候是能够成功的,但是在加行锁的时候,发生了锁住
上面说的next-key-lock是由行锁加上间隙锁完成的,如果将隔离级别转为了读已提交的级别的话,就next-key-lock只剩下了行锁的部分
读已提交的情况下,有一个关于update语句的优化
sessionB并不会被b=1的锁进行阻塞
这是因为在读已提交的隔离级别下,update语句有一个semi-consistent的优化
对于update语句,如果碰到了已经锁了的行,会读入最新的版本,然后判断一下是否满足条件,然后进行跳过或者等待
但是对于delete语句,并没有作用
最后,是关于倒序的话,如何进行加锁
由于倒序,会先加上(20,25)和next-key lock(15,20]
然后在索引C上遍历,会一直扫描到c=10,于是next-key lock会加到(5,10]
导致了阻塞了sessionB