MySQL间隙锁分析

掘金小勇士
1,136 阅读2分钟

锁概念

InnoDB支持几种不同的行级锁： 1、行锁(Record Lock)： 对索引记录加锁。 2、间隙锁(Gap Lock)： 锁住整个区间，包括：区间里具体的索引记录，不存在的空闲空间（可以是两个索引记录之间，也可能是第一个索引记录之前或最后一个索引记录之后的空间）。 3、next-key锁： 行锁和间隙锁组合起来。

间隙锁

Gap Lock的唯一目的就是阻止其他事务插入到间隙中（间隙锁是在可重复读隔离级别下才生效）

丁奇老师文章中总结到加锁的2个原则和2个优化如下，下面针对实例验证

  1. 原则1：加锁的基本单位是next-key lock（next-key lock是前开后闭区间）。
  2. 原则2：查找过程中访问到的对象才会加锁。
  3. 优化1：索引上的等值查询，给唯一索引加锁的时候，next-key lock退化为行锁。
  4. 优化2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock退化为间隙锁。

创建示例表用户演示

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

初始化如下数据

idcd
055
101010
151515
202020
252525

主键等值查询间隙锁

SessionA:

begin; // 开事务
update t set d = d+1 where id =7 ； //执行SQL正常

SessionB：

insert into t values(9,9,9);  //执行SQL阻塞

SessionC:

update t set d = d+1 where id =10 ； //执行SQL正常

SessionB为何阻塞？

更新t表中id=7这行记录，根据加锁规则分析

  • 根据规则1分析，坐开右闭加锁区间 即（5,10]
  • 原则2中提到遇到访问对象加锁，更新条件是id =7 而 id =10不满足条件，所以next-key lock 转为间隙锁 ，锁区间（5,10）

所以上述现象中SessionB看见阻塞，而SessionC正常执行。

主键索引范围锁

SessionA:

begin; // 开事务
select * from t where id >= 10 and id < 11 for update ； //执行SQL正常

SessionB：

insert into t values(7,7,7);  //执行SQL正常
insert into t values(14,14,14);  //执行SQL阻塞

SessionC:

update t set d = d+1 where id =15 ； //执行SQL阻塞

分析SessionA是如何加锁

  • Session A 查找id = 10 这条记录，根据规则1可知加锁加锁范围（5，10],由于 id是主键唯一，找到id =10后 ，退化为行锁，只对 id=10这行加锁
  • 由于是范围查询，继续向后查询直到id=15这行停止，所以加锁范围（10,15]

所以执行insert into t values(14,14,14)和update t set d = d+1 where id =15是有锁等待。

非唯一索引范围锁

t表中c字段为非唯一索引，按c字段范围查询

SessionA:

begin; // 开事务
select * from t where c >= 10 and c < 11 for update ； //执行SQL正常

SessionB：

insert into t values(7,7,7);  //执行SQL阻塞

SessionC:

update t set d = d+1 where c =15 ； //执行SQL阻塞

分析SessionA是如何加锁

  • 查询c >= 10 首先增加了（5,10] 间隙锁，由于 c不是唯一索引，所以不会退化为行锁，继续向由查找到 c=15终止查找，所以在c上加锁范围（5,10] 和（10,15]

所在在执行insert into t values(7,7,7);是锁等待，执行update t set d = d+1 where c =15 也是同样原理。

其他场景也可按上述规则分析。

avatar
文章
阅读
粉丝