RR隔离级别下锁情况(探究gap锁和行锁)

boredbird 2020-03-28

!!!我的数据库演示版本为5.5,以后会追加最新数据库的演示版本

间隙锁(GAP Lock)时InnoDB在可重复读下的隔离级别下为了解决幻读问题引入的锁机制。幻读存在的问题是因为在新增或者更新时如果进行查询,会出现不一致的现象,这时单纯的使用行锁无法满足我们的需求,我们需要对一定范围的数据加锁,防止幻读。

可重复读隔离级别就是数据库通过行锁和间隙锁(不要搞混gap lock和next-key lock共同组成来实现的,在网上抄了一下加锁的规则,然后自己进行一下验证:

  • 1.加锁的基本单位是(next-key lock),遵循前开后闭原则,对于闭合的索引采用行锁,区间范围是间隙锁,间隙锁锁住的是insert操作,而行锁会锁住所有当前读操作
  • 2.插叙过程中访问的对象会增加锁
  • 3.索引上的等值查询-------给唯一索引加锁的时候,next-key lock会升级为行锁
  • 4.索引上的等值查询-------向右遍历时最后一个值不满足查询需求时,行级锁会退化为间隙
  • 5.唯一索引上的范围查询到不满足条件的第一个值为止(看不懂这条可以看下文中的主键索引+范围查询的情况3)

下图是演示数据库表(第一列是主键索引,第二个普通索引,第三个是没有索引,第四个是唯一索引)

RR隔离级别下锁情况(探究gap锁和行锁)

 以上数据为了解决幻读问题,更新时除了对该四条数据加行锁外,还会对他们的中间范围加锁分别为(无穷小,5] (5,10] (10,15] (15,20] (20,无穷大] 如上文规则1中所说都是遵循左开右闭原则的

其实主键索引和唯一索引最终的结果是相同的,区别就是对唯一索引加锁最终也会通过聚簇索引对主键索引加锁。

主键索引(唯一索引)+等值查询+条件命中

RR隔离级别下锁情况(探究gap锁和行锁)

  1. 上图中左边事务执行查询主键索引为5的记录,且表中真实存在5,此时事务尚未提交,右边事务想要在主键索引为3的位置插入索引
  2. 规则1中介绍,加锁的基本单位是next-key lock,锁的范围是(无穷小,5]  但此时属于规则3中所说的"唯一索引上的等值查询"
  3. (无穷小,5] 的间隙锁升级为了行锁,只锁住了5这条索引记录,所以右侧事务可以正常执行insert操作。

主键索引(唯一索引)+等值查询+条件未命中

RR隔离级别下锁情况(探究gap锁和行锁)

 上图中左边事务查询主键索引为6的记录,且表中并不存在6,此时事务尚未提交,右边事务想要在主键索引为7的位置插入索引。规则1中介绍,加锁的基本规则是next-key lock,锁的范围是(5,10]  ,规则3中说唯一索引上的等值查询会升级为行锁,但是规则4中又介绍,条件未命中的索引会由行锁退化为间隙锁,此时锁的范围(5,10),右边事务阻塞。

RR隔离级别下锁情况(探究gap锁和行锁)

 上图可以看到主键索引为10的位置已经放锁了,说明next-key lock已经变为了间隙锁,放开了索引为10这个位置,进一步证明了此时为间隙锁,锁范围为(5,10)

 主键索引(唯一索引)+范围查询

情况1.查询范围都在next-key lock的一个范围内

RR隔离级别下锁情况(探究gap锁和行锁)

  1. 上图主键查询的范围是 4<=id_primary_key < 5根据
  2. 原则1在(1,5] 之间加入key-next lock,右边事务执行结果可以看出在主键索引为3的位置可以正常执行当前读操作但是不能执行insert操作,说明有间隙锁
  3. 在主键索引为5的位置不能执行当前读操作,说明有行锁。

情况2.查询范围在next-key lock的多个范围内(圆圈代表阻塞,对号代表未阻塞)

RR隔离级别下锁情况(探究gap锁和行锁)

RR隔离级别下锁情况(探究gap锁和行锁)

  1. 黑色图片部分代表了事务1,查询范围为5<=id_primary_key<10
  2. 此时由规则1,key-next lock包含的范围有(负无穷,5]  (5,10]   
  3. 对比蓝色图片事务2中的1、3和5操作我们可以发现,(负无穷,5] 由于属于主键索引的等值查询已经升级到了行锁(主键索引5的位置当前读阻塞,但是主键索引为3的位置可以正常执行insert操作)
  4. (5,10] 部分依旧是key-next lock(主键索引为8的位置可以执行当前读操作,但是不能执行insert操作)。对于主键索引为10的位置处于行锁状态(因为这是key-next lock的闭合位置),忘了测了,但结论不会错 

情况3.在情况2下多了一小点,用来介绍规则5

RR隔离级别下锁情况(探究gap锁和行锁)

 左边图片事务1执行了查询主键索引10<=id_primary_key<11的范围

根据规则1,此时会锁住(5,10] 

根据规则5:唯一索引上的范围查询到不满足条件的第一个值为止

我们的查询范围中第一个不满足条件的位置当然是id_primary_key=11的这个位置,所以此时依旧会将(10,15] 范围锁住

根据情况2我们就可以知道(5,10] 的key-next lock已经升级到了行锁,所以此时的锁范围是 [10,15]

普通索引+等值查询+条件命中(未完待续)

在开始测试普通索引之前,先向表中增加一条数据

RR隔离级别下锁情况(探究gap锁和行锁)

相关推荐