InnoDB：InnoDB 锁和事务模型

关于

为了实现大规模，繁忙或高度可靠的数据库应用程序，从其他数据库系统移植大量代码，或调整 MySQL 性能，了解“InnoDB 锁”和“InnoDB事务模型”非常重要。

InnoDB 锁

见：“InnoDB：InnoDB 锁”

InnoDB 事务模型

见：“InnoDB：InnoDB 事务模型”

InnoDB 中的不同 SQL 语句设置的锁

锁定读取、更新或删除通常会对SQL语句处理过程中扫描的每个索引记录设置“记录锁”（Record Locks）。语句中是否有排除行的“WHERE”条件并不重要。InnoDB 不记得确切的“WHERE”条件，但只知道扫描了哪些索引范围。【？？？】

锁通常是“临键锁”（Next-Key Locks），它还会阻止在记录之前插入“间隙”（Gap）。但是，可以显式禁用“间隙锁”（Gap Locks），这将导致不使用“临键锁”。【？？？】

事务隔离级别也可以影响设置哪些锁。
如果在搜索中使用了辅助索引，并且要设置的索引“记录锁”是互斥的，则 InnoDB 还将检索相应的聚集索引记录并对其设置锁定。【？？？】
如果没有适合语句的索引，并且 MySQL 必须扫描整个表来处理该语句，则表的每一行都将被锁定，从而阻塞其他用户对表的所有插入。创建良好的索引很重要，这样您的查询就不必不必要地扫描很多行。
【即：只有使用索引时才会使用行级锁，否则使用表锁！！！】

InnoDB 如下设置特定类型的锁：【？？？？？？？？？？？？？？？】

“SELECT ... FROM”是“一致的读取”，读取数据库的快照并且不设置锁定，除非将事务隔离级别设置为“SERIALIZABLE”。
- 对于“SERIALIZABLE”级别，搜索在遇到的索引记录上设置共享的“临键锁”。但是，对于使用唯一索引锁定行以搜索唯一行的语句，仅需要索引“记录锁”。【？】
对于“SELECT ... FOR UPDATE”或“SELECT ... LOCK IN SHARE MODE”，将为扫描的行获取锁，并预期释放不符合包含在结果集中的行（例如，如果它们不符合“WHERE”子句中指定的条件）。
- 但是，在某些情况下，行可能不会立即解锁，因为结果行与其原始源之间的关系在查询执行期间会丢失。
  例如，在“UNION”中，表中的扫描（和锁定）行可能在评估它们是否符合结果集之前被插入到临时表中。在这种情况下，临时表中的行与原始表中的行之间的关系将丢失，并且直到查询执行结束后，后行才被解锁。
1. “SELECT ... LOCK IN SHARE MODE”在搜索遇到的所有索引记录上设置共享的“临键锁”。但是，对于使用唯一索引锁定行以搜索唯一行的语句，仅需要索引“记录锁”。
2. “SELECT ... FOR UPDATE”在搜索遇到的每条记录上设置独占的“临键锁”。但是，对于使用唯一索引锁定行以搜索唯一行的语句，仅需要索引“记录锁”。
- 对于搜索遇到的索引记录，“SELECT ... FOR UPDATE”阻止其他会话执行“SELECT ... LOCK IN SHARE MODE”或读取某些事务隔离级别。“一致的读取”将忽略读取视图中存在的记录上设置的任何锁定。【？？？】
“UPDATE ... WHERE ... ”在搜索遇到的每条记录上设置独占的“临键锁”。但是，对于使用唯一索引锁定行以搜索唯一行的语句，仅需要索引“记录锁”。
“UPDATE”修改聚集索引记录时，将对受影响的辅助索引记录进行隐式锁定。在插入新的二级索引记录之前执行重复检查扫描时，以及在插入新的二级索引记录时，“UPDATE”操作还会对受影响的二级索引记录进行共享锁定。
“DELETE FROM ... WHERE ...”在搜索遇到的每条记录上设置独占的“临键锁”。但是，对于使用唯一索引锁定行以搜索唯一行的语句，仅需要索引“记录锁”。
“INSERT”在插入的行上设置排他锁。该锁是索引“记录锁”，不是“临键锁”（即没有“间隙锁”），并且不会阻止其他会话插入到插入行之前的间隙中。
- 在插入行之前，将设置一种称为“插入意图间隙锁”的“间隙锁”。此锁表示插入的意图，如果插入到同一索引间隙中的多个事务不在间隙中的同一位置插入，则它们无需等待对方。
  假设有索引记录，其值分别为 4 和 7。尝试在插入行上获得排他锁之前，分别尝试插入 5 和 6 的值的每个事务都使用插入意图锁来锁定 4 和 7 之间的间隙，但是没有彼此阻塞，因为行没有冲突。
- 如果出现重复键错误，则会在重复索引记录上设置一个共享锁。
  - 如果有多个会话试图插入同一行，而另一个会话已经具有独占锁，则使用共享锁可能会导致死锁。如果另一个会话删除该行，则可能发生这种情况。
    假设InnoDB 表 t1 具有以下结构：
    CREATE TABLE t1 (i INT, PRIMARY KEY (i)) ENGINE = InnoDB;
    现在，假设三个会话按 Sequences 执行以下操作：
    
    Session 1：
    START TRANSACTION; INSERT INTO t1 VALUES(1);
    Session 2：
    START TRANSACTION; INSERT INTO t1 VALUES(1);
    Session 3：
    START TRANSACTION; INSERT INTO t1 VALUES(1);
    Session 1：
    ROLLBACK;
    会话 1 的第一个操作获取该行的排他锁。会话 2 和 3 的操作都导致重复键错误，并且都请求该行的共享锁【？？？】。会话 1 回滚时，它将释放该行的排他锁，并为会话 2 和 3 排队请求共享锁。此时，会话 2 和 3 死锁：由于另一个持有共享锁，因此两个都无法获取该行的排他锁。
    
    如果表已经包含键值为 1 的行，并且三个会话按 Sequences 执行以下操作，则会发生类似情况：
    
    Session 1：
    START TRANSACTION; DELETE FROM t1 WHERE i = 1;
    Session 2：
    START TRANSACTION; INSERT INTO t1 VALUES(1);
    Session 3：
    START TRANSACTION; INSERT INTO t1 VALUES(1);
    Session 1：
    COMMIT;
    会话 1 的第一个操作获取该行的排他锁。会话 2 和 3 的操作都导致重复键错误，并且都请求该行的共享锁。会话 1 提交后，它将释放该行上的排他锁，并授予会话 2 和 3 排队的共享锁请求。此时，会话 2 和 3 死锁：由于另一个持有共享锁，因此两个都无法获取该行的排他锁。
“INSERT ... ON DUPLICATE KEY UPDATE”与简单的“INSERT”的不同之处在于，当发生重复键错误时，将排他锁而不是共享锁放在要更新的行上：
1. 对重复的主键值采用排它的索引“记录锁”。
2. 对于重复的唯一键值，将使用排他的“临键锁”。
如果唯一键上没有冲突，则“REPLACE”就像“INSERT”一样完成。否则，将在要替换的行上放置一个排他的下一个键锁。
“INSERT INTO T SELECT ... FROM S WHERE ...”在插入 T 的每一行上设置排他索引“记录锁”（无“间隙锁”）。如果事务隔离级别为“READ COMMITTED”，或者启用了“innodb_locks_unsafe_for_binlog”而事务隔离级别不是“SERIALIZABLE”，则 InnoDB 在 S 上进行搜索，作为“一致的读取”（无锁）。否则，InnoDB 在 S 的行上设置共享的“临键锁”。 InnoDB 在后一种情况下必须设置锁：在使用基于语句的二进制日志进行前滚恢复期间，必须以与原始操作完全相同的方式执行每个 SQL 语句。
- “CREATE TABLE ... SELECT ... ”与“INSERT ... SELECT”一样使用共享的“临键锁”执行“SELECT”或作为一致读取来执行。
- 在结构“REPLACE INTO t SELECT ... FROM s WHERE ...”或“UPDATE t ... WHERE col IN (SELECT ... FROM s ...)”中使用“SELECT”时，InnoDB 在表的行上设置共享的“临键锁”。
InnoDB 在初始化表上预先指定的“AUTO_INCREMENT”列时，在与“AUTO_INCREMENT”列关联的索引的末尾设置排他锁。
- 当“innodb_autoinc_lock_mode=0”时，InnoDB 使用特殊的“AUTO-INC”表锁定（自动增量锁）模式，该模式将在访问自动增量计数器的同时获取并保持锁到当前 SQL 语句的末尾（而不是整个事务的末尾）。当“AUTO-INC”表锁被持有时，其他客户端无法插入到表中。对于带有“innodb_autoinc_lock_mode=1”的“批量插入”，也会发生相同的行为。表级“AUTO-INC”锁不与“innodb_autoinc_lock_mode=2”一起使用。
- InnoDB 无需设置任何锁定即可获取先前初始化的“AUTO_INCREMENT”列的值。
如果在表上定义了“FOREIGN KEY”约束，那么任何需要检查约束条件的插入，更新或删除操作都会在它查看该约束的记录上设置共享记录级锁。在约束失败的情况下，InnoDB 还会设置这些锁。
“LOCK TABLES”设置表锁，但设置这些锁的是 InnoDB 层上方较高的 MySQL 层。如果“innodb_table_locks = 1”（默认值）和“autocommit = 0”，InnoDB 知道表锁，并且 InnoDB 之上的 MySQL 层知道行级锁。【？？？】
- 否则，InnoDB 的自动死锁检测将无法检测到涉及此类表锁的死锁。同样，由于在这种情况下，较高的 MySQL 层不知道行级锁，因此可以在另一个会话当前具有行级锁的表上获取表锁。
- 如果“innodb_table_locks = 1”（默认值），“LOCK TABLES”将在每个表上获取两个锁。除了 MySQL 层上的表锁外，它还获得 InnoDB 表锁。 4.1.2 之前的 MySQL 版本未获得 InnoDB 表锁；可以通过设置“innodb_table_locks=0”来选择旧行为。如果未获取 InnoDB 表锁，则即使表的某些记录被其他事务锁定，“LOCK TABLES”也将完成。
- 在 MySQL 5.7 中，“innodb_table_locks=0”对于用“LOCK TABLES ... WRITE”明确锁定的表无效。对于被“LOCK TABLES ... READ”隐式锁定（例如，通过触发器）或被“LOCK TABLES ... WRITE”锁定，以进行读取或写入的表，它确实具有作用。
- 提交或中止事务时，将释放事务持有的所有 InnoDB 锁。因此，以“autocommit=1”模式在 InnoDB 表上调用“LOCK TABLES”并没有多大意义，因为获取的 InnoDB 表锁将立即释放。
- 您不能在事务中间锁定其他表，因为“LOCK TABLES”执行隐式“COMMIT”和“UNLOCK TABLES”。

Phantom Rows（幻影行、幻读）

当同一查询在不同时间产生不同的行集时，在事务内就会发生所谓的幻象问题。例如，如果“SELECT”被执行两次，但是第二次返回的行而不是第一次返回，则该行是“幻像”行。

假设 child 表的 id 列上有一个索引，并且您想要读取和锁定该表中标识符值大于 100 的所有行，目的是稍后更新所选行中的某些列：

SELECT * FROM child WHERE id > 100 FOR UPDATE;

该查询从 id 大于 100 的第一条记录开始扫描索引。让表包含 id 值分别为 90 和 102 的行。如果在扫描范围内对索引记录设置的锁不锁定在间隙（在本例中为 90 和 102 之间的间隙），则另一个会话可以在表中插入 id 为 101 的新行。如果您要在同一事务中执行相同的“SELECT”，则会看到新行查询返回的结果集中的 id 为 101（“幻影”）。如果我们将一组行视为数据项，则新的幻影子级将违反事务应能够运行的事务隔离原则，以使已读取的数据在事务期间不会更改。

为了防止产生幻像，InnoDB 使用了称为“临键锁”的算法，该算法将索引行锁定与间隙锁结合在一起。 InnoDB 执行行级锁定的方式是，当它搜索或扫描表索引时，会在遇到的索引记录上设置共享或互斥锁。因此，行级锁实际上是“索引记录锁”。此外，索引记录上的“临键锁”也会影响该索引记录之前的“间隙”。即，“临键锁”是“索引记录锁”加上索引记录之前的间隙上的“间隙锁”。如果一个会话在索引中的记录 R 上具有共享或排他锁，则另一会话不能按索引顺序在 R 之前的间隙中插入新的索引记录。【？？？】

InnoDB 扫描索引时，它也可以锁定索引中最后一条记录之后的间隔。前面的示例中正是这种情况：为了防止在 id 大于 100 的表中插入任何内容，InnoDB 设置的锁在 id 值 102 之后的间隙上包含一个锁。【！】

您可以使用“临键锁”在应用程序中实施唯一性检查：如果您以共享模式读取数据，但没有看到要插入的行的重复项，则可以安全地插入行并知道在读取期间在行的后继上设置的“临键锁”可防止任何人同时为行插入副本。因此，“临键锁”使您可以“锁定”表中不存在的内容。【！】

如“ InnoDB 锁”中所述，可以禁用间隙锁定。这可能会导致幻影问题，因为在禁用间隙锁定时，其他会话可以在间隙中插入新行。【！】

InnoDB 中的死锁

见：“InnoDB：InnoDB 中的死锁”