InnoDB的间隙锁是避免在RR隔离级别下出现”幻读“,间隙相对于行锁来说更加复杂,且加锁范围更大,如果业务可以保证不会出现幻读,可以考虑关闭。但是如果业务不能保证,那么间隙锁确实能为业务省去不少烦恼,那么mysql针对间隙锁是怎么处理加锁规则的,下面根据我的理解简单总结一下,以备以后翻阅。
开始前先建以下表:
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);
然后上几个规则:
原则 1:加锁的基本单位是 next-key lock。希望你还记得,next-key lock 是前开后闭区间。
原则 2:查找过程中访问到的对象才会加锁。
优化 1:索引上的等值查询,给唯一索引加锁的时候,next-key lock 退化为行锁。
优化 2:索引上的等值查询,向右遍历时且最后一个值不满足等值条件的时候,next-key lock 退化为间隙锁。
针对上面几个规则,先说说我觉得比较绕的地方,然后再用例子辅助理解:
1. ”next-key lock 是前开后闭区间“:间隙锁可以理解成都是开区间,加上行锁,才组成前开后闭的情况。
2. “给唯一索引加锁的时候,next-key lock 退化为行锁” :退化意思就是,在唯一索引上做等值查询,那么原本要加上的间隙锁就被去掉了,只留下行锁。
3. “索引上的等值查询,向右遍历时且最后一个值不满足等值条件的时候”:这个意思就是,在遍历索引时,如果遍历的最后一个值不是等值查询的那个值,那么next-key lock 里的行锁就去掉了,只留下间隙锁。这里强调的是最后一个节点。
下面我举个几个例子来理解上面的规则:
非唯一索引的加锁
select id from t where c = 5 lock in share mode;
这个sql的查询过程大致 如下,通过c索引,找到5这个节点,那么会加行锁和间隙锁,范围是(0,5],但是c索引不是唯一索引,索引innodb会继续往下遍历,找到c索引的下一个值,就是10,满足了规则2,即访问到的节点都需要加锁,所以会加(5,10],但是这个是最后一个节点,且10不等于等值查询条件(c = 5),所以这个next-key lock就退化成间隙锁(5,10),所以这个语句一共会加以下两个间隙锁和一个行锁,那么我另外一个事务里,有
update t set c = c +1 where id = 5 lock in share mode;
第一个直觉会想到,id对应的那条记录已经加锁里,所以会等待,但结果是这个sql是可以执行成功的,原因是,锁是加在索引上的,也就是说上面的锁都是在索引c上,而select语句用到里索引覆盖,并没有回表去访问主键索引上的值,所以主键上没有加锁,所以update语句可以执行成功,因为是用主键进行树搜索的。但是lock in share mode改成for update是会给主键对应的节点加锁的。
如果上面的sql改成
select id from t where c = 5 limit 1 lock in share mode;
那么innodb在找到5这个节点后,并不会往后遍历到10,索引这个语句加锁是间隙锁和行锁(0,5]
唯一索引范围锁
select * from t where id >= 10 and id < 15 lock in share mode;
对于上面这个语句,加锁过程大致如下:
先在主键索引上找到值为10的节点,加锁(5,10],但由于优化1,间隙锁会去掉,留下行锁,然后遍历下一个值,找到15,访问到的节点都需要加锁,于是加next-key lock,所以整个sql加锁是id=10的行锁和(10,15]间隙锁
非唯一索引范围锁
select * from t where c >= 10 and c < 15 in lock share mode;
过程和上面的例子类似,先访问c索引值为10的节点,加锁(5,10],而由于c是非唯一索引,所以不会退化成行锁,再访问15,加锁(10, 15],因为这个查询不是等值查询,所以锁不会退化成间隙锁。
如果上面的例子改成c <= 15,向后遍历c索引时,还会找20那个值,那么间隙锁的范围会更大。
参考:《mysql实战45讲》