MySQL加锁分析专题

Updated on with 0 views and 0 comments

1 一个抛砖引玉的栗子

先看一下表结构和表里的数据：

a10c202fc2f34a6b8baca2ce674887ab.png

再来看下面的操作：

176396ca33e64c7cb095dcb36180ebbb.png

说明：请注意上图中的事务隔离级别为RR，还有SQL执行顺序，先执行 SQL 1，然后执行 SQL 2，最后执行 SQL 3。

问题：为什么 SQL 2 能够正常执行，而 SQL 3 会被锁住呢？

2 区分不可重复读和幻读

下图中 Q2 和 Q3 都是幻读吗？

bbff335f42f947aeb363628cc4b14d61.png

不可重复读和幻读到底有什么区别呢？

  • 不可重复读是读取了其他事务更改的数据，针对update操作

  • 幻读是读取了其他事务新增的数据，针对insert和delete操作

3 记录锁、间隙锁和临键锁

3.1 记录锁（Record Lock）

记录锁，是去锁住索引记录，所以记录锁也叫行锁，如果InnoDB存储引擎表在建立的时候没有设置任何索引，那么这时InnoDB存储引擎会使用隐式的主键来进行锁定。记录锁的栗子如下图所示：

52aba0e734844ad6b71f5bb5213a9442.png

3.2 间隙锁（Gap Lock）

间隙锁，锁定一个范围，但不包含记录本身，间隙锁是封锁索引记录中的间隔，或者第一条索引记录之前的范围，又或者最后一条索引记录之后的范围。

5f94d34237ec4a44ace377b7c9287803.png

如果我们执行这样的SQL：SELECT * FROM user WHERE id > 5 AND id < 9 FOR UPDATE，会锁住(5,9)这个范围，如下图所示：

64ffa3e80bd641ab901d87076a6c4456.png

说明：

  • 当事务隔离级别是RR时，间隙锁才能生效
  • 间隙锁是不互斥的
  • 间隙锁的引入，可能会导致同样的语句锁住更大的范围，这其实是影响了并发度

3.3 临键锁（Next-Key Lock）

Next-Key Lock 是结合了 Gap Lock 和 Record Lock 的一种锁定算法（Next-Key Lock = Gap Lock + Record Lock），InnoDB对于行的查询采用的就是这种锁定算法。Next-Key Lock 主要是为了解决幻读（Phantom Problem）的问题，Next-Key Lock 锁定的不是单个值，而是一个范围，是谓词锁（Predict Lock）的一种改进。

e3cf6a04cf254945bf455c5543361366.png

结合上图的数据来看一个具体的栗子：

59daffbc81004030a206d9de3a1c1b61.png

说明：

  • 上面的SQL锁住的不是9这单个值
  • Next-Key Lock 是前开后闭区间
  • Next-Key Lock 也要求事务隔离级别是RR
  • Next-Key Lock 可以解决幻读问题

4 RR隔离级别加锁规则总结

为了方便大家理解，这里把加锁规则总结为三个“原则”和两个“优化”：

  • 原则一：加锁的基本单位是 Next-Key Lock
    • 注意 Next-Key Lock 的区间是前开后闭，公式 Next-Key Lock = Gap Lock + Record Lock
    • 先判断查询条件落在哪个区间，后面再来判断是否要优化
    • 加锁操作有一个过程，并非一次性就加好所有的锁
    • 间隙会随着记录的变化而变化，关键是找准间隙边界，一定不要想当然地认为加了间隙锁就不会发生变化
  • 原则二：查找过程中访问到的索引才会加锁
    • 注意是对索引加锁
    • 如果不清楚走什么索引一定要看看 EXPLAIN
    • 如果不会用 EXPLAIN，出门右转看 MySQL查询优化探秘
    • 如果不需要回表，就不需要对主键索引加锁
    • 如果不知道什么是主键索引、二级索引、覆盖索引和回表，还是出门右转看 MySQL查询优化探秘
  • 原则三：查询条件不作为锁的边界，除非会退化为记录锁，否则要向外扩张锁
    • 开区间变成闭区间也是扩张
    • 即便边界值是停止索引查找的最后一个值，也需要扩张
    • MySQL 5.x系列 <= 5.7.28，MySQL 8.x系列 <= 8.0.13 可以保证原则三，否则可能对唯一索引做优化进而不扩张锁
  • 优化一：唯一索引等值查询且记录存在时，Next-Key Lock 退化为记录锁
    • 这里说的等值查询是指 Point 类型查询
    • 加记录锁的前提是记录要存在
    • 如果等值条件没有对应的记录，就不会退化为记录锁
  • 优化二：索引等值查询，向右遍历索引且最后一个值不满足等值条件的时候，Next-Key Lock 退化为间隙锁
    • 最后一个值是指停止索引查找的那个值
    • 向右查找是指顺着建索引时顺序查找，反之就称为向左查找
    • ORDER BY 控制着索引查找的方向
    • B+树叶子结点之间是用双向链表连接的

关于MySQL加锁分析的一般步骤是：先对应原则，再找优化。

5 案例分析

下面所有的栗子都是使用最开始给出的表结构和数据，涉及到的 Next-Key Lock 分别为：

65768236f7bf46a6ba58b2fdbd222de0.png

说明：因为+∞是开区间，所以InnoDB给每个索引加了一个不存在的最大值supremum，这样才符合前面说的“前开后闭”。

5.1 唯一索引等值查询

不存在等值查询条件的记录：

95dc16def3234584a2adec33793443fe.png

分析：

  • 先根据 id=6 确定 Next-Key Lock 为 (5,10]
  • 基于优化二，Next-Key Lock 退化为间隙锁 (5,10)

存在等值查询条件的记录：

17aa79c5485f4d37bb374793a6d5c25c.png

分析：

  • 先根据 id=10 确定 Next-Key Lock 为 (5,10]
  • 基于优化一，Next-Key Lock 退化为记录锁，只锁住 id=10 这一条记录

5.2 非唯一索引等值查询

555a4468e0654dc9aa3ffdfa1f8484bf.png

分析：

  • 先根据 k=5 确定要加 Next-Key Lock 为 (0,5]
  • 因为不需要回表，基于原则二，不需要给主键索引加锁
  • 再看原则三，需要向右扩张一个 Next-Key Lock，即 (5,10]
  • 基于优化二，索引查找的最后一个值为 10，不满足等值条件，退化为间隙锁，即 (5,10)
  • 最终加的锁为 (0,5] 和 (5,10)

如果将 lock in share mode 改成 for update，就需要回表了，也就是说会锁住对应的主键索引，如下图所示：

495d3d01b17b4f31b762efc131a38d17.png

5.3 唯一索引范围查询

下面两条SQL查询结果是相同的，但是加的锁不一样：

select * from t where id=10 for update;
select * from t where id>=10 and id<11 for update;

第1条SQL只会加 id=10 的记录锁，第2条SQL加锁情况如下图所示：

80d29299d16246c190d4eadbe5442314.png

分析：

  • 根据查询条件确定要加 Next-Key Lock 为 (5,10] 和 (10,15]
  • 这里需要注意 id>=10 这个条件跨了两个 Next-Key Lock，所以我们需要分开来分析
  • 首先看 id=10 这个条件最终会退化成记录锁，只锁住记录 id=10
  • id>10 和 id<11 属于同一个Next-Key Lock，由于不满足优化条件，需要加的锁还是 (10,15]
  • 经过叠加后最终加的锁是 记录锁 id=10 和 临键锁 (10,15]

再来看一个更普通的栗子：

bd031e7cf1984bb9accd13fb14edbe41.png

分析：

  • 根据查询条件得到加锁单元为 (5,10]
  • 基于原则三，需要向右扩展一个 Next-Key Lock，即 (10,15]
  • 范围查询不满足优化二，所以最终加的锁为 (5,10] 和 (10,15]

5.4 非唯一索引范围查询

还是先看看下面两条查询结果相同的SQL：

select * from t where k=10 for update;
select * from t where k>=10 and k<11 for update;

第1条SQL加的锁为 (5,10] 和 (10,15)，第2条SQL加锁情况如下图所示：

cb757f0982bb45fca4f4c3c028a421a2.png

分析：

  • 根据查询条件确定要加 Next-Key Lock 为 (5,10] 和 (10,15]
  • 这里需要注意 k>=10 这个条件跨了两个 Next-Key Lock，所以我们需要分开来分析
  • 首先看 k=10 这个条件，上面已经分析了，这个条件加的锁为 (5,10] 和 (10,15)
  • k>10 和 k<11 属于同一个Next-Key Lock，由于不满足优化条件，需要加的锁还是 (10,15]
  • 经过叠加后最终加的锁为 (5,10] 和 (10,15]

再来看一个更普通的栗子：

139ab052c00e4f54a97af5143f51408c.png

分析：

  • 根据查询条件得到的加锁单元为 (5,10] 和 (10,15]
  • 基于原则三，向右扩张一个 Next-Key Lock，即 (15,20]
  • 不适用任何优化，所以最终加锁为 (5,10]、(10,15] 和 (15,20]

5.5 查询带limit

6794cb649be74bf0a0d8b649ebab1e43.png

分析：

  • 根据 id>5 得到 Next-Key Lock 为 (5,10]、(10,15]、(15,20]、(20,25]、(25,supremum]
  • 然后根据 limit 2 这个条件，可以进一步减少 Next-Key Lock，剩下 (5,10] 和 (10,15]
  • 因为不满足其他优化，所以最终加的锁为 (5,10] 和 (10,15]

5.6 倒序查询

24c10971ad754c4eaa7ce1db544c6b2e.png

如果去掉 ORDER BY，得到的加锁结果为 记录锁 id=15、(15,20] 和 (20,25]，显然跟上面加了 ORDER BY 的测试结果不相同。

分析：

  • 首先要搞清楚索引是向右还是向左查找，上面栗子加了 ORDER BY 之后应该是向左查找
  • 明确了是向左查找索引后，我们需要转换一下思路，这里先将 Next-Key Lock 理解成“前闭后开”
  • 基于上面的假设，可以从查询条件得到的 Next-Key Lock 为 [15,20) 和 [20,25)
  • 接着应用原则三，向左扩张一个 Next-Key Lock，即 [10,15)
  • 两个优化都不适用，所以我们得到的加锁结果为 [10,15)、[15,20) 和 [20,25)
  • 最后还需要将上面的加锁结果转换成正常的“前开后闭”的 Next-Key Lock，即 (5,10]、(10,15]、(15,20] 和 (20,25)

5.7 死锁

87962df8af064a6d89c64d3c0f81b4be.png

上图右边的 update 语句已经被锁住了，接下来执行一条 insert 语句，如下图所示：

221065525d984a07aa4ff257c0117bb4.png

insert 语句执行成功，但是 update 语句报了一个死锁错误。

分析：

  • 先根据 k=10 确定要加 Next-Key Lock 为 (5,10]
  • 再看原则三，需要向右扩张一个 Next-Key Lock，即 (10,15]
  • 基于优化二，索引查找的最后一个值为 15，不满足等值条件，退化为间隙锁，即 (10,15)
  • 左边会话最终加的锁 (5,10] 和 (10,15)
  • update 语句加的锁为 (5,10]，进入锁等待
  • 当左边会话执行 insert 语句时，被 update 已经加上的间隙锁 (5,10) 锁住，因此出现了死锁
  • 注意，加 (5,10] 锁其实是分了两步，update 语句成功加上了间隙锁 (5,10)，因为间隙锁不互斥，但是被记录锁锁住了，这才是公式 Next-Key Lock = Gap Lock + Record Lock 要表达的意思

5.8 动态变化的间隙锁

我们回过头来看看文章开头那个栗子，你能自己解释原因吗？

要分析这个问题，不能像上面那样静态的分析加了哪些锁，我们一定要理解间隙锁是一个动态的概念，它会随着记录而变化。再来看一个更有说服力的栗子：

dd17ad491f5d416380322a41d0d395db.png

分析：

  • 左边会话执行完 select 语句会加这些锁 (10,15] 和 (15,20]
  • 右边会话执行 delete 语句时 id=10 的记录未被锁住，所以成功删除
  • 删除成功后，间隙锁变成 (5,15)，这时再执行 insert 语句显然会被锁住

看了上面分享的内容，你学废了吗？有没有感觉自己以前是摸着黑做CRUD？

标题：MySQL加锁分析专题
作者：yanghao
地址：http://solo.fancydigital.com.cn/articles/2022/02/24/1645694257820.html

↑ ↓