innodb加锁机制

2019-06-19 11:01:09 浏览数 (2)

参考

  • innodb-transaction-isolation-levels.html
  • MySQL Gap Lock问题

0. 前置知识

默认读者已经了解四个隔离等级、mvcc的机制、各种行锁的作用。

序

我们已知，RC、RR下：

  1. 快照读(普通select)会开启ReadView，使用mvcc机制防止脏读/不可重复读/幻读，不加锁。
  2. 当前读(select...for update、update、delete、insert)不启用ReadView，而是使用锁来保证并发下操作的正确性。

另外：

  • RU下，读取不加锁，修改加锁
  • RC下，查找索引不用到gap lock和next-key lock，只有record lock。所以当前读只会施加record lock。
  • SR下，没有mvcc机制，读、写都靠加锁来维持正确性。
  • 我们最常用的还是RR等级，其加锁机制较为复杂，判断条件似乎很多，因此需要重点讨论。另外，为了简化讨论，本文只讨论RR下select...for update的加锁机制。

1. RR下有着繁杂的加锁机制

那么，RR等级下，到底何时会只用到record lock，何时会用到gap lock/next-key lock？如果用到gap lock/next-key lock，又会施加到多大的范围上呢？ 对于所有情况来说：

  • 索引可以是单列索引或联合索引，可以是unique也可以是非unique
  • 查询时，可以是等值查询(where xx = ?)，也可以是范围查询(where xx > ?)
  • 查询的条件，既可以存在，也可以不存在：
    • 等值查询where xx = ?中，xx = ?的记录既可能存在，也可能不存在
    • 范围查询where xx > ?中，可能存在xx = ?的记录，也可能不存在。

看起来有很多情况，我们先查阅下官方

官方文档

查阅官方文档innodb-transaction-isolation-levels.html

  • REPEATABLE READ This is the default isolation level for InnoDB. Consistent reads within the same transaction read the snapshot established by the first read. This means that if you issue several plain (nonlocking) SELECT statements within the same transaction, these SELECT statements are consistent also with respect to each other. See Section 15.7.2.3, “Consistent Nonlocking Reads”. For locking reads (SELECT with FOR UPDATE or FOR SHARE), UPDATE, and DELETE statements, locking depends on whether the statement uses a unique index with a unique search condition, or a range-type search condition.
    • For a unique index with a unique search condition, InnoDB locks only the index record found, not the gap before it.
    • For other search conditions, InnoDB locks the index range scanned, using gap locks or next-key locks to block insertions by other sessions into the gaps covered by the range. For information about gap locks and next-key locks, see Section 15.7.1, “InnoDB Locking”.

第一段不多说了，Consistent reads下会使用快照读，利用mvcc机制。 第二段翻译如下： locking reads(select ... for update或for share), update、delete语句，其上加的锁取决于语句是用唯一索引进行唯一条件查找，还是用范围查找。

  • 对于使用了唯一索引的唯一条件查找，innodb只会锁住记录，不会锁其前面的间隙。
  • 对其它情况，innodb会用gap lock或next-key lock锁住覆盖到的索引的范围，以便阻塞其它在该范围的gap上的插入操作。

似乎满足唯一索引的唯一条件查找就行，但实际上要严格地说，还要加上一个条件, 即索引要命中。它真正强调的，是要唯一查找，而并没有对单列/联合索引作约束。

也就说：

  • 如果你在用单列唯一索引，你得用等值查询命中唯一的一条记录
  • 如果你在用联合唯一索引，为了命中唯一的记录，你得用上全部的索引，并用等值查询命中唯一的一条记录。

理解规则

理解上面的规则？我们首先要理解，在当前读操作中，gap lock和next-key lock是为了防止幻读和不可重复读的，我们现在关注防止幻读的需求。

  • 首先，不基于索引的查询需要锁表，这个容易理解。
  • 如果基于索引
    • 我们的查询要么最多只可能命中一行，而且
      • 确实命中了。
      • 没有命中。
    • 要么可能命中连续的几行。

如果我们的查询可能命中连续的多行 则为了防止幻读，就得加上gap lock或next-key lock。比如，假如有属性(id, col)，id为主键，col上有非唯一索引，则执行select * from t where col = 7时，不管有没命中，都可能查到多行结果。如果查到了一行，为了防止幻读，我们也要在锁住该记录的前后空间，以防其它事务在上面插入了col=7的行，从而造成幻读。

插入新记录

我们注意到，如果用gap lock锁住(4,7)和(7,9)，确实能防止记录7的前后插入数据，但也使col=5、col=6等数据的插入被阻塞。这是"不可避免的误伤"，是gap lock/next-key lock基于索引的性质所决定的，要想锁住记录7之前的间隙，我们只能获取(4,7)的gap lock。所以我们没法在不"误伤"的情况下阻止幻读。

如果我们的查询最多只可能命中一行，且命中了

只可能命中一行

此时只需获取记录7的记录锁，就能防止不可重复读和幻读。

如果最多只可能命中一行记录，却没命中

只可能命中一行，却没命中

为了防止其它事务在空隙插入col=7的数据，造成幻读，我们不得不用gap lock锁住整个(4,9)的空间，这或许会造成"误伤"，但也没办法。

总地来说，就是你的查询应当只可能最多命中一条记录，且确实命中了，才会只用记录锁。否则，为了防止幻读，你不得不在记录的前后加上gap lock，阻止其它事务插入新记录，即使会造成"误伤"。

何时使用gap lock

有了上面的基础，现在我们可以讨论何时使用gap lock了。分为以下情况：

  1. 没用索引。要锁表。
  2. 用了索引，但不唯一。 那么你的查询也就可能命中连续的多行，为了防止幻读，得在可能插入新纪录的空间加上gap lock。
  3. 用了唯一索引，但却是范围查询。 依然可能命中多行。为了防止幻读，你不得不在范围涵盖的间隙内都用上gap lock。
  4. 用了唯一索引，且是等值查询。但用的是联合索引中的一部分索引。 由于没有用上联合索引的全部索引，你的查询依然可能命中多行。为了防止幻读，依然会用上gap lock。
  5. 用了唯一索引，且是等值查询，且利用了索引的全部(要么是单列索引，要么用了联合索引的全部索引)，但却没命中。 此时的查询最多可能命中一条记录，但却没命中。在可能出现记录的那个空隙上，你需要加上gap lock，防止下次查询前有其它事务在空隙插入了符合查询条件的新记录，造成幻读。
  6. 用了唯一索引，且是等值查询，且利用了索引的全部，且命中。 恭喜你，你的查询最多可能命中一条记录，而且命中了。这时，只需对该记录加锁，就能防止幻读。

加锁机制图解如下：

加锁机制

施加gap lock的范围

那么，Innodb会对多大的范围施加gap lock呢？其实，它是在会造成幻读的空隙都加上锁，以保证并发的安全性，即使可能造成误伤。之所以会造成误伤，是因为我们获取空隙锁时，只能把整个空隙锁定，没法只锁定空隙的一部分。 比如有记录4,6,8，属性为col, 有非唯一索引。 执行select * from t where col > 7 则要锁上(6,8],(8, ∞),因为要防止其他事务在空隙插入col>7的数据，比如新插入col=8的数据，将放置在记录8的前面或后面。 所以，即使可能会"误伤"，使col=7的数据无法插入，也没有办法，因为gap lock是施加在索引上的，你只能一次性获取(6,8)整个空隙的锁。 执行select * from t where col > 5 and col < 7 则要锁上(4,6]和(6,8)。

key lock record select unique

0 人点赞