mysql中nextkey锁的使用_【翻译】MySQL的innodb中NextKey锁解析

去年的某个时候，一个朋友在微信上问我MySQL间隙锁的案例，当时正在赶一个项目，没来得及看那个CASE，后来找不到了。昨天看到这篇jahfer写的博客: https://jahfer.com/posts/innodb-locks/ 觉得在介绍Next-Key锁的这方面很有创意的使用了自制的动画(非常简陋的动画 没啥用，我换成了截图做标记了)，不管是创意还是内容都值得一看

• 作者:jahfer

• 翻译:51ak

–翻译全文如下：

最近，我在调试MySQL高并发问题时有机会深入理解了InnoDB的锁定机制，这篇文章是我学习innodb锁行为的一个总结。

0.概念介绍

InnoDB只有几个锁定概念,但是它们的使用和行为取决于当前连接正在使用的事务隔离级别

…the isolation level is the setting that fine-tunes the balance between performance and reliability, consistency, and reproducibility of results when multiple transactions are making changes and performing queries at the same time. 引自MySQL官方文档 https://dev.mysql.com/doc/refman/5.7/en/innodb-transaction-isolation-levels.html

InnoDB一共有4种隔离级别(按最严格到最宽松的顺序)

• SERIALIZABLE 序列化

• REPEATABLE READ (default) 可重复读

• READ COMMITTED 读已提交

• READ UNCOMMITTED 读未提交

每种隔离级别下的锁行为差异非常大，而我们现在只分析前两种隔离级别(SERIALIZABLE，REPEATABLE READ),首先让我们创建一个book 表。

CREATE TABLE `books` (`id` bigint(20) NOT NULL AUTO_INCREMENT,`author_id` bigint(20) NOT NULL,`title` varchar(255) NOT NULL,`borrowed` tinyint(1) DEFAULT '0',
  PRIMARY KEY (`id`),KEY `idx_books_on_author_id` (`author_id`)
);INSERT INTO `books` (`author_id`, `title`)VALUES
  (101, "The Pragmatic Programmer"),
  (102, "Clean Code"),
  (102, "The Clean Coder"),
  (104, "Ruby Under a Microscope");

这张表像这样

id	author_id	title	borrowed
1	101	The Pragmatic Programmer	FALSE
2	102	Clean Code	FALSE
3	102	The Clean Coder	FALSE
4	104	Ruby Under a Microscope	FALSE

1.InnoDB锁类型

在深入研究细节之前，我们应该从一个重要的概念开始，这个概念适用于我们将在本文中介绍的所有锁。InnoDB锁可以分为“共享”的和“排它”的

• 共享(S)锁允许持有该锁的事务select行。

• 排它(X)锁允许持有该锁的事务update或delete行。

• MySQL官文档 《14.7.1 InnoDB的锁定》https://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html#innodb-shared-exclusive-locks

就像它们的名字所暗示的那样，共享锁可以由多个并发事务持有，因为它们只能读取该行。如果想执行写操作，就必须获得一个排它锁，而且每次只能由一个事务持有。当其他事务持有共享锁时，也不能加上排它锁，这就是InnoDB保证事务持有共享锁期间数据不会被改动的方法。

共享(S)锁只需要在索引树上(不一定是主键索引)锁定用到的行数，这是因为读只要求在这个请求中找到的行数上是可以重复读的。相对的，排它(X)锁需要在受影响的记录的PrimaryKEY上加锁，这将影响所有相关的二级索引。

2.Next-Key锁

这篇文章主要是想讲一种锁类型:Next-Key锁。不多废话，一个Next-Key锁事实上是两种锁的组合：记录锁(record lock)和间隙锁(gap lock)。

2.1 记录锁 Record Locks

记录锁是InnoDB里最简单的一种锁，它锁定索引上的指定行。例如：如果我们不想事务进行时别人更新我们用到的行，我们可以请求一个共享的读锁：

-- 开启一个事务BEGIN;-- 执行一个读SELECT *FROM `books`-- 显示的声明要持有一个共享(S) 锁WHERE `id` = 2 LOCK IN SHARE MODE;

现在我们去查查performance_schema.metadata_locks 表, 将会看到一个共享读锁(SHARED_READ lock):

mysql> SELECT OBJECT_NAME, LOCK_TYPE, LOCK_DURATION, LOCK_STATUS
    -> FROM performance_schema.metadata_locks
    -> WHERE OBJECT_NAME="books";
+-------------+-------------+---------------+-------------+
| OBJECT_NAME | LOCK_TYPE   | LOCK_DURATION | LOCK_STATUS |
+-------------+-------------+---------------+-------------+
| books       | SHARED_READ | TRANSACTION   | GRANTED     |
+-------------+-------------+---------------+-------------+1 row in set (0.00 sec)

多个事务都可以持有这个锁，持有这个锁能保证没有其他连接更新WHERE id=2这本书。直到锁被释放(事务COMMIT 或者 ROLLBACK.)

看完了怎么为读请求申请一个锁，现在再看看怎样申请一个写请求的锁：

-- 开启一个事务BEGIN;-- 执行一个读SELECT *FROM `books`-- 显示的声明要持有一个排它(X) 锁WHERE `id` = 2 FOR UPDATE;

这个查询和上面的稍微有点不同，这里我们为 id=2这行记录申请了一个排它锁,意味着其他任务连接想获得这行记录的锁都要等我们这个事务进行完。因为我们给InnoDB发信号说我们要UPDATE这行记录(SQL中我们用了 select ... from 表 for update )，如果其他事务想更新就会产生脏数据。

mysql> SELECT OBJECT_NAME, LOCK_TYPE, LOCK_DURATION, LOCK_STATUS
    -> FROM performance_schema.metadata_locks
    -> WHERE OBJECT_NAME="books";
+-------------+--------------+---------------+-------------+
| OBJECT_NAME | LOCK_TYPE    | LOCK_DURATION | LOCK_STATUS |
+-------------+--------------+---------------+-------------+
| books       | SHARED_WRITE | TRANSACTION   | GRANTED     |
+-------------+--------------+---------------+-------------+

这里我们看到了一个SHARED_WRITE 锁，跟上面的SHARED_READ不一样。需要注意的是它们同时ShARED锁，但是实际上不同的，在这里我们获得的是一个WRITE锁，这个锁实际上是也确实是排它(X)锁。为了避免说到更多的细节，这篇文章里我们不打算详细说SHARED_WRITE (IX) vs. SHARED_READ (IS) 锁的区别了，如果你好奇的话可以看这篇官方文档：https://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html#innodb-intention-locks

2.2 间隙锁 Gap Locks

间隙锁是锁定索引树上的一段范围的锁。InnoDB使用这种锁定类型来确保一组选定的记录与周围的记录保持它们之间的关系。如果持有间隙锁，其他SQL语句不可以在此范围内INSERT，UPDATE或者DELETE，间隙锁可以加在表的聚集索引也可以加在聚集索引上。

要理解间隙锁，我们需要了解InnoDB如何存储其索引记录。使用innodb_ruby(译者注ruby大神写的一套分析INNODB存储内容的工具)，我们可以在索引idx_books_on_author_id上分析我们的记录

$ innodb_space -s ./data/ibdata1 -T test/books -I idx_books_on_author_id index-recurse
ROOT NODE #4: 4 records, 84 bytes
  RECORD: (author_id=101) → (id=1)
  RECORD: (author_id=102) → (id=2)
  RECORD: (author_id=102) → (id=3)
  RECORD: (author_id=104) → (id=4)

InnoDB能够锁定任何两个相邻记录之间的空间。现在我们看看InnoDB在事务内部可以使用的所有可能的间隙锁：

1. (-无穷大, (101 → 1)]

2. ((101 → 1), (102 → 2)]

3. ((102 → 2), (102 → 3)]

4. ((102 → 3), (104 → 4)]

5. ((104 → 4), +无穷大)

来,凑近点,我们看看第3个范围3. ((102 → 2), (102 → 3)]

这里我用了逗号分隔符号,意思是左侧的102->2不包含的右侧的102->3是包含在范围里的,(大于和小于等于).

现在我们持有了这个间隙锁,任何其他的连接尝试想在这个范围内(author_id >= 102 and an 2 < id <= 3.)执行INSERT, UPDATE or DELETE 都需要等我们.

在深入理解了这些锁概念以后,让我们看看这些锁在实际查询中的使用方式

3 事务隔离级别: 序列化 SERIALIZABLE

对于在SERIALIZABLE事务隔离级别执行的SQL语句，必须锁定查询到的每个记录，以确保其他连接不可以修改正在select的记录.这是最严格的隔离级别，它的锁定确保可以安全地对并发事务进行重新排序，而不会相互影响。

For SERIALIZABLE level, the search sets shared next-key locks on the index records it encounters. ~摘自My官方文档 14.7.3 Locks Set by Different SQL Statements in InnoDB

如果SELECT查询使用的是唯一索引(例如PRIMARY KEY)，则无需使用间隙锁，因为它可以保证它仅影响这一条记录。

However, only an index record lock is required for statements that lock rows using a unique index to search for a unique row. ~摘自My官方文档 14.7.3 Locks Set by Different SQL Statements in InnoDB

4 事务隔离级别: 可重复读 REPEATABLE READ

通常我们的应用程序并不需要SERIALIZABLE这么强的约束(代价太大)，因此InnoDB使用的默认事务隔离级别为REPEATABLE READ。事务在REPEATABLE READ任何时间执行select，结果都像是它们是在与此事务中的第一次读取相同的时间点(“快照”)。这样就可以在一个事务中的不同时间点对数据库进行一致的查看，而不会遇到“幻像读”：在后续读取中出现或消失的记录。

InnoDB唯一无法使用快照记录的情景是当它SELECT的数据是为了UPDATE或DELETE。在这个情况下，InnoDB必须从数据库中读取最新版本，以防止它意外地针对过时的值进行操作。尽管我们不需要锁定查询时遇到的每条记录(如SERIALIZABLE读取操作一样)，但我们必须防止其他连接修改对我们当前连接的查询产生影响。

分情况来说:

4.1 主键或唯一索引上的锁

最简单的锁是主键上的查询

-- 开启一个事务BEGIN;-- 执行一个SQLUPDATE `books`SET `borrowed` = TRUEWHERE `id` = 3;

在REPEATABLE READ隔离级别下，Innodb确保此更新可重复读取的；因为它会阻止其他任何语句影响结果。虽然对这个事务来说是开放的，没有其他连接可以INSERT，UPDATE或者DELETE这条记录的值 。

当使用唯一索引(例如表的主键)时，InnoDB不需要在所选记录的左侧或右侧加间隔锁，因为可以确保查询匹配的值与所有其他值都不相同。在此示例中，我们只需要在匹配行上有一个记录锁，以防止其他事务在此事务打开时更新或删除它。

让我们保持上面的事务一直打开，再开启一下事务执行以下SQL将会一直等待

UPDATE `books`SET `borrowed` = TRUEWHERE `id` = 3;-- this waits until the first transaction exits,-- as `id = 3` conflicts with the existing lock

作为对比，我们可以很轻松的插入或修改一条不相关的记录

INSERT INTO `books` (`author_id`, `id`, `title`)VALUES (103, 5, "Database Internals");-- success! `id = 5` doesn’t conflict with our UPDATE

InnoDB时，偏向于使用唯一值(例如上面SQL的id)。在以下情况下，InnoDB确定记录是唯一的：

1. 该纪录select的是PRIMARY KEY，或…

2. 该记录select的是UNIQUE索引，select字段时所有字段都在索引上

因为PRIMARY KEY值本质上必然是唯一的。如果用于搜索的索引符合“唯一”的条件，则无需使用间隙锁定。这是因为InnoDB可以确定不会插入任何可能冲突的值

4.2 非唯一聚集索引上的锁

如果该语句正在select不能保证唯一的记录，则它必须使用间隙锁来确保可以重复执行这个SQL都返回相同结果(于是就可以REPEATABLE READ)。这是 next-key 锁定的作用

If one session has a shared or exclusive lock on record R in an index, another session cannot insert a new index record in the gap immediately before R in the index order. ~摘自My官方文档 14.7.1 InnoDB Locking

我们再回过头来看看我们一开始创建的表。现在的样子

id	author_id	title	borrowed
1	101	The Pragmatic Programmer	FALSE
2	102	Clean Code	FALSE
3	102	The Clean Coder	FALSE
4	104	Ruby Under a Microscope	FALSE
5	103	Database Internals	FALSE

$ innodb_space -s ./data/ibdata1 -T test/books -I idx_books_on_author_id index-recurse
ROOT NODE #4: 5 records, 105 bytes
  RECORD: (author_id=101) → (id=1)
  RECORD: (author_id=102) → (id=2)
  RECORD: (author_id=102) → (id=3)
  RECORD: (author_id=103) → (id=5)
  RECORD: (author_id=104) → (id=4)

现在，我们可以执行一条UPDATE语句，我们观察该语句在idx_books_on_author_id索引上的锁定行为

-- open transactionBEGIN;-- issue statementUPDATE `books`SET `borrowed` = TRUE-- select record by a non-unique value as-- idx_books_on_author_id is not a UNIQUE indexWHERE `author_id` = 103;

由于使用了非唯一值(WHERE author_id = 103)select了一条记录，因此InnoDB必须获得两个锁：

1. 聚集索引上的记录锁，其中id = 5(这是记录的主键，因为它的author_id = 103)

2. idx_books_on_author_id索引树上的 next-key锁。where author_id = 103 如下图所示 

InnoDB必须确保在打开此事务时不会插入任何其他可能满足此搜索结果的记录。为此，InnoDB在记录的两边加上间隙锁。如果您还记得有关“Next-key”的部分，您可能会记得Next-key锁实际上由两种锁类型组成：记录锁(record locks)和间隙锁(Gap locks)。

在这个示例中，将使用3个锁idx_books_on_author_id来产生next-key锁：

1. 一个记录锁：(author_id=103) → (id=5)

2. 一个间隙锁：当前记录103|5 到最近的最小值102|3 这个范围

3. 一个间隙锁：当前记录103|5 到最近的最大值104|4 这个范围

非常重要的是要理解 ，InnoDB只需要锁定锁定行周围的间隙，下面这样的SQL就被阻塞住了

INSERT INTO `books` (`author_id`, `id`, `title`)VALUES (102, 6, "Clean Architecture");-- (author_id=102) → (id=6) 触发了我们上面提的3个锁的第2个

然后这个SQL不会和我们的锁产生冲突。因为它是104|6

INSERT INTO `books` (`author_id`, `id`, `title`)VALUES (104, 6, "Clean Architecture");-- (author_id=104) → (id=6) falls outside-- of our gap lock range

在这个案例中，MySQL并没有严格锁定author_id值的范围(本来是102– 104)，而是索引中author_id和的PRIMARY KEY的组合范围。

使用索引两端的值时，会出现特别令人惊讶的情况。当在索引的第一条或最后一条记录上获取next-key锁且搜索不符合唯一性标准时，InnoDB必须将所有值锁定为正或负无穷大，以确保没有其他记录与该记录在表中的位置重叠。

For the last interval, the next-key lock locks the gap above the largest value in the index and the “supremum” pseudo-record having a value higher than any value actually in the index. ~摘自My官方文档 14.7.1 InnoDB Locking

让我们回到我们的表,打开一个新的事务看看实际效果：

-- 开启一个事务BEGIN;-- 执行一个SQLUPDATE `books`SET `borrowed` = TRUEWHERE `author_id` = 104;--注意这里用到了最大的author_id 104

在这种情况下，next-key锁拥有独占的写访问idx_books_on_author_id从(author_id=103) → (id=5)一路到无穷大！

因为author_id索引中没有>104的记录，InnoDB需要将GAP加到无穷大上，顼在这个事务会阻止所有包含author_id>104的INSERT.我的天啊 OOPS!

INSERT INTO `books` (`author_id`, `title`)VALUES (153, "A Series of Unfortunate Events");-- oh no! 这个SQL被上面的事务 104 < 153 < +Infinity GAP锁阻塞了

这可能是一个特别令人讨厌的问题，因为在索引的尾部进行插入并不罕见。只要您确定要通过唯一索引或主键进行SELECT，就不必担心这种类型的锁争用。

结论

希望您发现对InnoDB锁定机制的这种探索很有趣！如果您正在寻找InnoDB锁定的更广泛或更详尽的概述，我强烈建议您看看 Bill Karwin撰写的《InnoDB Locking with Stick Figures》。

如果您喜欢这些文字，那么我有一个更新很慢的博客系列，我试图在MySQL之前构建一个无锁的，忽略缓存的B树作为快速缓存。我不能保证您会对这系列的文章都满意，但是您可能只是和我一起学习了一些东西。

Next-Key锁存在于

** 51ak 翻译于 2021年01月05日**