MySQL8中文手册-InnoDB的锁

写在前面

网上充斥着各种MySQL的学习资料，有所谓的小白学习笔记也有大牛的分享文章，有免费的也有收费的，文章的质量也是鱼龙混杂，经常看到文章与文章之间相互矛盾，甚至文章前后矛盾。
至于为什么要翻译官方文档，还要源于上次学习极客时间的付费文章，其中一篇对change buffer的说明，文章中描述change buffer是用于更新数据缓存，后面又对change buffer和redo log的关系进行了说明，然而评论中有各种观点，有些说change buffer只针对索引数据进行缓存，文章前后翻了好几遍，始终有个疑问，change buffer到底缓存的是什么内容？在度娘上查找，也是各种说法都有。
为了彻底弄明白这个问题，我去查找了MySQL8的官方英文文档，发现文档中描述非常清楚，change buffer的缓存对象是非唯一二级索引，缓存的是二级索引的变更操作（insert、update、delete），前面的一切疑问都明白了。
经过这件事后，我开始翻译MySQL8的使用手册，这才是最权威的最值得参考的资料，翻译过程参考了百度和谷歌翻译，但大部分还是人工翻译，因此难免有些理解偏差，请大家及时指正。

更多内容请点击MySQL8中文手册

15.7.1 InnoDB Locking

This section describes lock types used by InnoDB.

本节主要说明InnoDB使用的各种类型的锁。

• Shared and Exclusive Locks
• Intention Locks
• Record Locks
• Gap Locks
• Next-Key Locks
• Insert Intention Locks
• AUTO-INC Locks
• Predicate Locks for Spatial Indexes
• Shared and Exclusive Locks

• 共享锁与独占锁（Shared and Exclusive Locks）
• 意向锁Intention（ Locks）
• 记录锁（Record Locks）
• 间隙锁（Gap Locks）
• 键前锁（Next-Key Locks）
• 插入意向锁（Insert Intention Locks）
• 自增锁（AUTO-INC Locks）
• 空间表的预测锁（Predicate Locks for Spatial Indexes）

InnoDB implements standard row-level locking where there are two types of locks, shared (S) locks and exclusive (X) locks.

InnoDB实现了两种行级别的锁，分别是共享锁（shared locks）和独占锁（exclusive locks）

• A shared (S) lock permits the transaction that holds the lock to read a row.
• An exclusive (X) lock permits the transaction that holds the lock to update or delete a row.

• 共享锁允许占有锁的事务读取行数据
• 独占锁允许占有锁的事务更新或删除行数据

If transaction T1 holds a shared (S) lock on row r, then requests from some distinct transaction T2 for a lock on row r are handled as follows:

如果事务T1占有了行r的共享锁，事务T2在行r上的请求会按照如下规则处理：

• A request by T2 for an S lock can be granted immediately. As a result, both T1 and T2 hold an S lock on r.
• A request by T2 for an X lock cannot be granted immediately.

• 如果T2请求的是共享锁，可以立即获取，此时T1和T2同时占有行r的共享锁。
• 如果T2请求的是独占锁，需要等待T1释放后才能获取。

If a transaction T1 holds an exclusive (X) lock on row r, a request from some distinct transaction T2 for a lock of either type on r cannot be granted immediately. Instead, transaction T2 has to wait for transaction T1 to release its lock on row r.

如果事务T1占有行r的独占锁，事务T2不论请求行r的共享锁还是独占锁都不能获取成功，T2需要等待T1释放锁。

Intention Locks

InnoDB supports multiple granularity locking which permits coexistence of row locks and table locks. For example, a statement such as LOCK TABLES … WRITE takes an exclusive lock (an X lock) on the specified table. To make locking at multiple granularity levels practical, InnoDB uses intention locks. Intention locks are table-level locks that indicate which type of lock (shared or exclusive) a transaction requires later for a row in a table. There are two types of intention locks:

InnoDB支持多种颗粒度的锁，允许行锁和表锁共存。例如，语句LOCK TABLES … WRITE将获取表的独占锁。InnoDB使用意向锁（intention locks）来实现多种颗粒度的锁。意向锁是表级锁，可以表示一个事务在后续的操作过程中，需要那种类型的行锁（共享锁或独占锁）。意向锁有如下两种类型：

• An intention shared lock (IS) indicates that a transaction intends to set a shared lock on individual rows in a table.
• An intention exclusive lock (IX) indicates that a transaction intends to set an exclusive lock on individual rows in a table.

• 共享意向锁（IS）表示事务会对表中的行加共享锁。
• 独占意向锁（IX）表示事务会对表中的行加独占锁。

For example, SELECT … FOR SHARE sets an IS lock, and SELECT … FOR UPDATE sets an IX lock.

例如，语句SELECT … FOR SHARE对表加IS锁，SELECT … FOR UPDATE对表加IX锁。

The intention locking protocol is as follows:

意向锁的原则如下：

• Before a transaction can acquire a shared lock on a row in a table, it must first acquire an IS lock or stronger on the table.
• Before a transaction can acquire an exclusive lock on a row in a table, it must first acquire an IX lock on the table.

• 在事务获取表中行的共享锁前，必须先获得表的IS锁或更强的锁。
• 在事务获取表中行的独占锁前，必须先获得表的IX锁。

Table-level lock type compatibility is summarized in the following matrix.

表级的锁类型的互斥关系如下：

X	IX	S	IS
X	Conflict	Conflict	Conflict
IX	Conflict	Compatible	Conflict
S	Conflict	Conflict	Compatible
IS	Conflict	Compatible	Compatible

A lock is granted to a requesting transaction if it is compatible with existing locks, but not if it conflicts with existing locks. A transaction waits until the conflicting existing lock is released. If a lock request conflicts with an existing lock and cannot be granted because it would cause deadlock, an error occurs.

如果与现有锁是共享的（compatible），新事务能够获取到锁，如果与有的锁是互斥的（conflict），不能获取到锁，则事务必须等到现有锁释放。锁请求与现有锁互斥，而且因为产生死锁无法获取，会报错。

Intention locks do not block anything except full table requests (for example, LOCK TABLES … WRITE). The main purpose of intention locks is to show that someone is locking a row, or going to lock a row in the table.

意向锁不会实际锁定任何数据除非发生全表请求（例如，LOCK TABLES … WRITE）。意向锁的主要目的是表示有请求想要或即将锁定一行数据。

Transaction data for an intention lock appears similar to the following in SHOW ENGINE INNODB STATUS and InnoDB monitor output:

事务请求意向锁的信息在SHOW ENGINE INNODB STATUS命令输出中进行查看：

TABLE LOCK table `test`.`t` trx id 10080 lock mode IX
Record Locks

记录锁

A record lock is a lock on an index record. For example, SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE; prevents any other transaction from inserting, updating, or deleting rows where the value of t.c1 is 10.

记录锁是索引记录上的一种锁，例如，SELECT c1 FROM t WHERE c1=10 FOR UPDATE;会防止其他事务对t.c1为10的这行数据的插入、更新、删除操作。

Record locks always lock index records, even if a table is defined with no indexes. For such cases, InnoDB creates a hidden clustered index and uses this index for record locking. See Section 15.6.2.1, “Clustered and Secondary Indexes”.

即使表没有定义索引的情况下，记录锁也会对索引记录加锁，虽然没有定义索引，InnoDB也会创建隐式聚簇索引，对聚簇索引进行加锁操作。具体参考：Section 15.6.2.1, “Clustered and Secondary Indexes”.

Transaction data for a record lock appears similar to the following in SHOW ENGINE INNODB STATUS and InnoDB monitor output:

事务中记录锁的信息在SHOW ENGINE INNODB STATUS命令的输出中可以查看：

RECORD LOCKS space id 58 page no 3 n bits 72 index `PRIMARY` of table `test`.`t`
trx id 10078 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 8000000a; asc     ;;
 1: len 6; hex 00000000274f; asc     'O;;
 2: len 7; hex b60000019d0110; asc        ;;

Gap Locks

A gap lock is a lock on a gap between index records, or a lock on the gap before the first or after the last index record. For example, SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE; prevents other transactions from inserting a value of 15 into column t.c1, whether or not there was already any such value in the column, because the gaps between all existing values in the range are locked.

间隙锁是用来锁定索引记录之间的间隙或索引第一个记录之前的间隙或索引最后一个记录之后的间隙。例如，SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE;防止其他事务向t.c1列中插入15，不论在列中是否已经有这个值，因为在这个范围内的所有间隙都被锁住了。

A gap might span a single index value, multiple index values, or even be empty.

一个间隙可能包含一个索引记录值，多个索引记录值，也可能是空的。

Gap locks are part of the tradeoff between performance and concurrency, and are used in some transaction isolation levels and not others.

间隙锁是在系统性能和并发性之间的一种选择，在某些隔离级别下存在，另外一些隔离级别下不存在。

Gap locking is not needed for statements that lock rows using a unique index to search for a unique row. (This does not include the case that the search condition includes only some columns of a multiple-column unique index; in that case, gap locking does occur.) For example, if the id column has a unique index, the following statement uses only an index-record lock for the row having id value 100 and it does not matter whether other sessions insert rows in the preceding gap:

对于使用唯一索引来查询确定某一行数据时，不需要间隙锁。（当查询条件只包括多列唯一索引的某些列时，会需要间隙锁）例如，id有唯一索引，下面的语句只会对id是100的记录加索引记录锁，此时其他语句可以向这行数据的前后间隙中插入数据。

SELECT * FROM child WHERE id = 100;

If id is not indexed or has a nonunique index, the statement does lock the preceding gap.
It is also worth noting here that conflicting locks can be held on a gap by different transactions. For example, transaction A can hold a shared gap lock (gap S-lock) on a gap while transaction B holds an exclusive gap lock (gap X-lock) on the same gap. The reason conflicting gap locks are allowed is that if a record is purged from an index, the gap locks held on the record by different transactions must be merged.

如果id没有索引或者不是唯一索引，这条语句会锁住行前面的间隙。这里需要特别注意的是，两个事务可能会因为同时持有同一个间隙的互斥锁。例如，事务A可以持有一个间隙的共享间隙锁（gap S-lock）而同时事务B持有用一个间隙的互斥间隙锁（gap X-lock）。允许不同间隙锁同时锁定一个间隙的原因是如果一条记录从索引上刷到磁盘上时，其他事务在记录上的间隙锁必须进行合并。

Gap locks in InnoDB are “purely inhibitive”, which means that their only purpose is to prevent other transactions from inserting to the gap. Gap locks can co-exist. A gap lock taken by one transaction does not prevent another transaction from taking a gap lock on the same gap. There is no difference between shared and exclusive gap locks. They do not conflict with each other, and they perform the same function.

InnoDB中的间隙锁是纯粹的阻止类型的锁，意思是锁的主要目标就是阻止其他事务向间隙中插入数据。间隙锁是允许同时锁定的，一个事务获取到一个间隙的锁后，其他事务也可以获取该间隙的间隙锁。共享间隙锁和独占间隙锁没有什么区别，他们之间不会冲突，起到同样的作用。

Gap locking can be disabled explicitly. This occurs if you change the transaction isolation level to READ COMMITTED. Under these circumstances, gap locking is disabled for searches and index scans and is used only for foreign-key constraint checking and duplicate-key checking.

间隙锁可以禁用，在事务隔离级别改为READ COMMITTE的时候会被禁用。在这种情况下，搜索和扫描索不用间隙锁，间隙锁只用于外键约束检查和重复键检查。

There are also other effects of using the READ COMMITTED isolation level. Record locks for nonmatching rows are released after MySQL has evaluated the WHERE condition. For UPDATE statements, InnoDB does a “semi-consistent” read, such that it returns the latest committed version to MySQL so that MySQL can determine whether the row matches the WHERE condition of the UPDATE.

在READ COMMITTED隔离级别下还有其他的影响，在MySQL对WHERE条件检查完成后，会释放没有匹配到记录的记录锁。对于UPDATE语句，InnoDB会进行“半一致”（semi-consistent）读，会返回给MySQL最新的数据版本，让MySQL判断读取的行是否匹配WHERE条件进行UPDATE操作。

Next-Key Locks

A next-key lock is a combination of a record lock on the index record and a gap lock on the gap before the index record.

next-key锁是索引上的记录锁和记录之前间隙上的间隙锁组成的。

InnoDB performs row-level locking in such a way that when it searches or scans a table index, it sets shared or exclusive locks on the index records it encounters. Thus, the row-level locks are actually index-record locks. A next-key lock on an index record also affects the “gap” before that index record. That is, a next-key lock is an index-record lock plus a gap lock on the gap preceding the index record. 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.

InnoDB就是用这种方式来实现行级锁的，当索索或扫描索引时，会对扫描到的索引记录加共享或独占锁。因此，行级锁实际上是索引记录锁。索引记录上的next-key锁也会影响这条记录前面的间隙。所以，next-key锁是索引记录锁加上记录前面间隙上的间隙锁。如果一个session对索引记录R加了共享或独占锁，另外一个session就不能向索引记录前的间隙中插入数据。

Suppose that an index contains the values 10, 11, 13, and 20. The possible next-key locks for this index cover the following intervals, where a round bracket denotes exclusion of the interval endpoint and a square bracket denotes inclusion of the endpoint:

假设一个索引包含了10，11，13和20，索引上可能的next-key锁会锁住如下区间，左边开区间，右边闭区间：
(negative infinity, 10]
(10, 11]
(11, 13]
(13, 20]
(20, positive infinity)

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. The supremum is not a real index record, so, in effect, this next-key lock locks only the gap following the largest index value.

对于最后的区间，next-key锁会锁住索引记录最大值到无穷大值的区间，无穷大值表示的是比索引中的所有可能值都大。无穷大值不是一个具体的索引记录值，所以这个next-key锁只锁住后面到无穷大的间隙。

By default, InnoDB operates in REPEATABLE READ transaction isolation level. In this case, InnoDB uses next-key locks for searches and index scans, which prevents phantom rows (see Section 15.7.4, “Phantom Rows”).

InnoDB的默认事务隔离级别是REPEATABLE_READ，在这种情况下，InnoDB在查找和扫描索引时会使用next-key锁，这样可以防止幻读。

Transaction data for a next-key lock appears similar to the following in SHOW ENGINE INNODB STATUS and InnoDB monitor output:

事务中的next-key锁信息可以在SHOW ENGINE INNODB STATUS命令输出中查看：

RECORD LOCKS space id 58 page no 3 n bits 72 index `PRIMARY` of table `test`.`t`
trx id 10080 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 8000000a; asc     ;;
 1: len 6; hex 00000000274f; asc     'O;;
 2: len 7; hex b60000019d0110; asc        ;;

Insert Intention Locks

An insert intention lock is a type of gap lock set by INSERT operations prior to row insertion. This lock signals the intent to insert in such a way that multiple transactions inserting into the same index gap need not wait for each other if they are not inserting at the same position within the gap. Suppose that there are index records with values of 4 and 7. Separate transactions that attempt to insert values of 5 and 6, respectively, each lock the gap between 4 and 7 with insert intention locks prior to obtaining the exclusive lock on the inserted row, but do not block each other because the rows are nonconflicting.

插入意向锁是向表中插入行数据前由INSERT操作加的一种间隙锁。插入意向锁表示一种插入意向，如果多个事务不向同一个间隙中的同一位置插入数据，事务是不需要等待的。假设索引记录中有4和7两个值，两个不同的事务分别要向索引中插入5和6，他们分别对（4，7]这个间隙上请求插入意向锁，在获取各自插入行的独占锁之前，但是两个事务并不会阻塞，因为他们锁的行是不一样的。

The following example demonstrates a transaction taking an insert intention lock prior to obtaining an exclusive lock on the inserted record. The example involves two clients, A and B.
Client A creates a table containing two index records (90 and 102) and then starts a transaction that places an exclusive lock on index records with an ID greater than 100. The exclusive lock includes a gap lock before record 102:

下面的例子演示事务在获取插入行的独占锁前，先请求插入意向锁。示例中有两个客户端A和B，客户端A创建了一张包含2个索引（90,102）的表，然后开始一个事务，请求索引ID大于100的独占锁。独占锁包括了102记录之前的间隙锁。

mysql> CREATE TABLE child (id int(11) NOT NULL, PRIMARY KEY(id)) ENGINE=InnoDB;
mysql> INSERT INTO child (id) values (90),(102);

mysql> START TRANSACTION;
mysql> SELECT * FROM child WHERE id > 100 FOR UPDATE;
+-----+
| id  |
+-----+
| 102 |
+-----+

Client B begins a transaction to insert a record into the gap. The transaction takes an insert intention lock while it waits to obtain an exclusive lock.

客户端B开始了一个事务向间隙中插入一条记录，事务在等待获取独占锁时请求插入意向锁。

mysql> START TRANSACTION;
mysql> INSERT INTO child (id) VALUES (101);

Transaction data for an insert intention lock appears similar to the following in SHOW ENGINE INNODB STATUS and InnoDB monitor output:

事务中插入意向锁的信息在SHOW ENGINE INNODB STATUS命令的输出中，显示如下：

RECORD LOCKS space id 31 page no 3 n bits 72 index `PRIMARY` of table `test`.`child`
trx id 8731 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 80000066; asc    f;;
 1: len 6; hex 000000002215; asc     " ;;
 2: len 7; hex 9000000172011c; asc     r  ;;...

AUTO-INC Locks

An AUTO-INC lock is a special table-level lock taken by transactions inserting into tables with AUTO_INCREMENT columns. In the simplest case, if one transaction is inserting values into the table, any other transactions must wait to do their own inserts into that table, so that rows inserted by the first transaction receive consecutive primary key values.

自增锁时一种特殊的表级锁，在事务向AUTO_INCREMENT（自增）列中插入数据时使用。最简单的情况下，如果一个事务向一个表中插入一条数据，其他事务向表中插入时必须等待，使第一个请求插入数据的事务能获取到连续的主键值。

The innodb_autoinc_lock_mode configuration option controls the algorithm used for auto-increment locking. It allows you to choose how to trade off between predictable sequences of auto-increment values and maximum concurrency for insert operations.

innodb_autoinc_lock_mode配置项可以对自增锁使用的算法进行配置。这个配置项可以让你在可预测的自增序列值和插入操作的最大并发性之间进行权衡。

For more information, see Section 15.6.1.6, “AUTO_INCREMENT Handling in InnoDB”.

详情参考：Section 15.6.1.6, “AUTO_INCREMENT Handling in InnoDB”.

Predicate Locks for Spatial Indexes

InnoDB supports SPATIAL indexing of columns containing spatial columns (see Section 11.4.9, “Optimizing Spatial Analysis”).

InnoDB支持对SPATIAL列的SPATIAL索引（见：Section 11.4.9, “Optimizing Spatial Analysis”）

To handle locking for operations involving SPATIAL indexes, next-key locking does not work well to support REPEATABLE READ or SERIALIZABLE transaction isolation levels. There is no absolute ordering concept in multidimensional data, so it is not clear which is the “next” key.

为了处理SPACIAL索引，在REPEATABLE READ和SERIALIZABLE隔离等级下不启用next-key锁。在多维数据中没有绝对的排序，因此也就没有明确的“next”键。

To enable support of isolation levels for tables with SPATIAL indexes, InnoDB uses predicate locks. A SPATIAL index contains minimum bounding rectangle (MBR) values, so InnoDB enforces consistent read on the index by setting a predicate lock on the MBR value used for a query. Other transactions cannot insert or modify a row that would match the query condition.

为在SPACIAL索引上启用事务隔离等级的支持，InnoDB使用预测锁。一个SPATIAL索引包含最小边界矩阵（MBR）值，所以InnoDB在查询的MBR值上设置预测锁来保证在索引上的一致性读。其他事务不能插入或修改行与查询匹配的行数据。

原文链接：https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html