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 如下设置特定类型的锁：【？？？？？？？？？？？？？？？】

1. “SELECT ... FROM”是“一致的读取”，读取数据库的快照并且不设置锁定，除非将事务隔离级别设置为“SERIALIZABLE”。
   • 对于“SERIALIZABLE”级别，搜索在遇到的索引记录上设置共享的“下一键锁”。但是，对于使用唯一索引锁定行以搜索唯一行的语句，仅需要索引“记录锁”。【？】
2. 对于“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”或读取某些事务隔离级别。“一致的读取”将忽略读取视图中存在的记录上设置的任何锁定。【？？？】
3. “UPDATE ... WHERE ... ”在搜索遇到的每条记录上设置独占的“下一键锁”。但是，对于使用唯一索引锁定行以搜索唯一行的语句，仅需要索引“记录锁”。
4. “UPDATE”修改聚集索引记录时，将对受影响的辅助索引记录进行隐式锁定。在插入新的二级索引记录之前执行重复检查扫描时，以及在插入新的二级索引记录时，“UPDATE”操作还会对受影响的二级索引记录进行共享锁定。
5. “DELETE FROM ... WHERE ...”在搜索遇到的每条记录上设置独占的“下一键锁”。但是，对于使用唯一索引锁定行以搜索唯一行的语句，仅需要索引“记录锁”。
6. “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 死锁：由于另一个持有共享锁，因此两个都无法获取该行的排他锁。

7. “INSERT ... ON DUPLICATE KEY UPDATE”与简单的“INSERT”的不同之处在于，当发生重复键错误时，将排他锁而不是共享锁放在要更新的行上：
   1. 对重复的主键值采用排它的索引“记录锁”。
   2. 对于重复的唯一键值，将使用排他的“下一键锁”。
8. 如果唯一键上没有冲突，则“REPLACE”就像“INSERT”一样完成。否则，将在要替换的行上放置一个排他的下一个键锁。
9. “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 在表的行上设置共享的“下一键锁”。
10. 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”列的值。
11. 如果在表上定义了“FOREIGN KEY”约束，那么任何需要检查约束条件的插入，更新或删除操作都会在它查看该约束的记录上设置共享记录级锁。在约束失败的情况下，InnoDB 还会设置这些锁。
12. “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”。

InnoDB 中的死锁

• 见：“InnoDB：InnoDB 中的死锁”