MySQL锁(四)行锁的加锁规则和案例

在上一篇文章,我们学习了间隙锁和next-key lock,但是不知道怎么加锁,有哪些规则。间隙锁的概念不太好理解,尤其是配合上行锁后,很容易在判断是否会出现锁等待的问题上犯错。

今天我们就来学习一下加锁规则吧。

在学习前要说明一点,以下的规则只限于版本范围:5.x系列<=5.7.24,8.0系列<=8.0.13。

加锁规则

这个加锁规则包含两个“原则”、两个“优化”和一个“bug”。

  1. 原则1:加锁的基本单位是next-key lock。希望你还记得,next-key lock是前开后闭区间。
  2. 原则2:查找过程中访问到的对象才会加锁。
  3. 优化1:索引上的等值查询,给唯一索引加锁的时候,next-key lock退化为行锁。
  4. 优化2:索引上的等值查询,向右遍历时且最后一个值不满足等值条件的时候,next-key lock退化为间隙锁。
  5. 一个bug:唯一索引上的范围查询会访问到不满足条件的第一个值为止。

下面以表t为例来介绍一下这些规则。表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);

案例1:等值查询间隙锁

MySQL锁(四)行锁的加锁规则和案例
图1 等值查询间隙锁

分析:

  • 步骤1:根据原则1,加锁(5, 10]
  • 步骤2:根据优化2,id=10不满足查询条件,因此退化为间隙锁(5, 10)

结论:

  • Session B阻塞是因为id=8在间隙锁(5, 10)内
  • Session C可以执行是因为没有锁住id=10这行

案例2:非唯一索引等值锁

MySQL锁(四)行锁的加锁规则和案例
图2 非唯一索引等值锁

分析:

  • 步骤1:根据原则1,加锁(0, 5]
  • 步骤2:由于c是普通索引,因此还需要继续遍历,直到找到c=10,不满足条件。根据原则2,访问到的对象要加锁,因此要加(5, 10]。
  • 步骤3:同时根据优化2,这是一个等值查询,向右遍历的不满足条件的第一个值10,(5, 10]要退化为间隙锁(5, 10)
  • 因此加锁是索引c的next-key lock(0,5]和间隙锁(5,10)

结论:

  • Session B可以执行是因为加锁的是索引c,而不是主键索引
  • Session C阻塞是因为c的插入值是7,在间隙锁(5, 10)范围内

案例3:主键索引范围锁

MySQL锁(四)行锁的加锁规则和案例
图3 主键索引范围锁

分析:

  • 步骤1:根据原则1,加锁(5, 10]
  • 步骤2:根据优化1,退化为id=10的行锁
  • 步骤3:继续遍历,找到不满足id<11的值id=15,加锁(10, 15]

因此加锁id=15的行锁和id的next-key lock(10, 15]

结论:

  • 插入id=13被阻塞:next-key lock (10, 15]
  • 更新id=15被阻塞:next-key lock (10, 15]

案例4:非唯一索引范围锁

MySQL锁(四)行锁的加锁规则和案例
图4 非唯一索引范围锁

分析:

  • 步骤1:根据原则1,加锁(5, 10]
  • 步骤2:继续遍历,找到不满足c<11的值c=15,根据原则2,加锁(10, 15]

因此加锁索引c (5, 10]和(10, 15]

结论:

  • 插入c=8,被(5, 10]阻塞
  • 更新c=15,被(10, 15]阻塞

案例5:唯一索引范围锁bug

MySQL锁(四)行锁的加锁规则和案例
图5 唯一索引范围锁bug

分析:

  • 步骤1:根据原则1,加锁(10, 15],再根据优化1,退化为id=15的行锁
  • 步骤2:向右遍历,加锁(10, 15]
  • 步骤3:根据BUG,要访问到不满足条件的第一个值,即id=20,加锁(15 ,20]。

因此加锁为(10, 15]和(15, 20]

结论:

  • 更新id=20阻塞,被(15, 20]锁住
  • 插入id=16阻塞,被(15, 20]锁住

案例6:非唯一索引上存在"等值"的例子

mysql> insert into t values(30,10,30);
MySQL锁(四)行锁的加锁规则和案例
图6 非唯一索引上存在"等值"的例子

分析:

  • 步骤1:根据原则1,(c=5,id=5)到(c=10,id=10)这个next-key lock
  • 步骤2:向右查找,直到碰到(c=15,id=15)这一行,循环才结束。根据优化2,这是一个等值查询,向右查找到了不满足条件的行,所以会退化成(c=10,id=10) 到 (c=15,id=15)的间隙锁

因此加锁(c=5,id=5)到(c=10,id=10)这个next-key lock和(c=10,id=10)到(c=15,id=15)这个间隙锁

结论:

  • 插入c=12阻塞,被(c=10,id=10)到(c=15,id=15)这个间隙锁锁住
  • 更新c=15成功,没有锁住c=15

案例7:limit 语句加锁

先插入一条记录。

mysql> insert into t values(30,10,30);
MySQL锁(四)行锁的加锁规则和案例
图7 limit 语句加锁

分析:

  • 步骤1:根据原则1,(5, 10],因为c=10有两条行,因此遍历到这里就结束
  • 步骤2:因为是delete,因此加两个行锁(id=10和id=30)

因此加锁c (5, 10)和两个行锁(id=10和id=30)

结论:

  • 插入c=12成功,因为c=12没有被锁住

说明:这个例子对我们实践的指导意义就是,在删除数据的时候尽量加limit。

案例8:一个死锁的例子

这个案例的目的是说明:next-key lock实际上是间隙锁和行锁加起来的结果。

MySQL锁(四)行锁的加锁规则和案例
图8 一个死锁的例子

分析:

  • 步骤1:根据原则1,加锁(5, 10]
  • 步骤2:继续遍历,直到c=15不满足条件,加锁(10, 15],根据优化2,退化为(10, 15)

结论:

  • Session B在等待锁,此时Session B已经加了间隙锁(5, 10),在等待加行锁c=10。
  • Session A插入c=8,也在等待锁,从而导致死锁

说明:next-key lock具体执行的时候,是要分成间隙锁和行锁两段来执行的。

案例9:非唯一索引排序范围锁

MySQL锁(四)行锁的加锁规则和案例
图9 非唯一索引排序范围锁

分析:

  • 步骤1:先执行c=20,加锁(15, 20]
  • 步骤2:根据优化2,加间隙锁(20, 25)
  • 步骤3:再执行c=15,加锁(10, 15]
  • 步骤4:继续向左遍历,找到记录id=10为止,加锁(5, 10]

在扫描过程中,c=20、c=15、c=10这三行都存在值,由于是select *,所以会在主键id上加三个行锁。

因此要加锁索引c (5, 25)和三个行锁(id=10,id=15,id=20)。

结论:

  • 插入c=6,被c(5, 25)锁住

案例10:不等号条件里的等值查询

begin;
select * from t where id>9 and id<12 order by id desc for update;

在执行过程中,通过树搜索的方式定位记录的时候,用的是“等值查询”的方法。

分析:

  • 步骤1:根据原则1,加锁 (10, 15]
  • 步骤2:根据优化2,退化为(10, 15)
  • 步骤3:向左遍历,找到id=10,加锁(5, 10],继续找到id=5为止,加锁(0, 5]

案例11:in范围锁

begin;
select id from t where c in(5,20,10) lock in share mode;

分析:

说明:锁是逐个逐个加的。

  • 步骤1:先c=5,加(0, 5]和(5, 10)
  • 步骤2:再c=10,加(5, 10]和(10, 15)
  • 步骤3:后c=20,加(15, 20]和(20, 25)

间隙锁是不互斥的,因为加锁范围是(0, 25),除c=15外。

死锁情况

select id from t where c in(5,20,10) order by c desc for update;

有一种情况,同时执行倒序语句,因为刚好同时执行,逐渐加锁(倒序加锁),会出现死锁情况。

参考资料

MySQL锁(四)行锁的加锁规则和案例

上一篇:无法打开到SQL Server的连接 (Microsoft SQL Server, 错误:53) .


下一篇:开始使用ElasticSearch(四)之将SqlServer数据库数据导入ES