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&