• InnoDB 中不同 SQL 语句设置的锁
  • 查询
    • 快照读（一致性读）
    • 当前读
  • 更新
  • 删除
  • 插入
  • AUTO_INCREMENT 锁定
  • 外键约束
  • 表锁

InnoDB 中不同 SQL 语句设置的锁

加锁读（locking read）、UPDATE 语句或者 DELETE 语句通常会对在 SQL 语句处理过程中扫描的每个索引记录设置记录锁，即索引记录锁（index record lock）。

SQL 语句中是否存在排除该行的 WHERE 条件并不重要，InnoDB 不会关注精确的 WHERE 条件，InnoDB 只知道扫描了哪些索引范围。这些锁通常是下一个键锁（next-key locks），它们会阻止其它会话插入到紧邻记录之前的“间隙”中。

我们可以显式禁用间隙锁定，这样就可以不使用 next-key lock；另外，事务的隔离级别也会影响设置哪些锁。

如果搜索时使用二级索引，并且要设置的 索引记录锁（index record locks） 是排它的，InnoDB 也会检索相应的聚集索引记录并对其设置锁。

如果查询没有使用索引，MySQL 就会执行全表扫描，此时，表中的每一行都会被锁定，从而阻止其他用户对该表的所有插入。因此，创建一个合适的索引非常重要，这样查询就不会扫描不必要的行。

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

查询

快照读（一致性读）

在 Repeatable Read 隔离级别下，SELECT ... FROM 操作是快照读，读取的是数据库的快照，并且不会加锁。

快照读：读写不冲突，每次读取的都是快照数据。

快照读的前提：是隔离级别不是串行隔离级别，串行隔离级别下的快照读会退化成当前读。

在 SERIALIZABLE 隔离级别下，InnoDB 会对在搜索过程中遇到的索引记录上设置共享的 next-key lock，但是，对于使用唯一索引锁定行来搜索唯一行的语句，只需要索引记录锁。

因为它是基于多版本实现，因此，快照读可能读到的并不一定是数据的最新版本，而有可能是之前的历史版本，简而言之，MVCC 就是为了实现读-写冲突不加锁，而这个读指的就是快照读。

当前读

当前读：每次读取的都是数据库记录的最新版本，会对当前读取的数据进行加锁，防止其他事务修改数据，是悲观锁的一种操作。

像 SELECT ... FOR SHARE、SELECT ... FOR UPDATE、UPDATE、INSERT、DELETE（排他锁）这些操作都是一种当前读。因为，它读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，因此，会对读取的记录进行加锁。

以下几种情况都是当前读：

• SELECT 语句加锁

  # 共享锁
  SELECT name FROM t_user WHERE id = 1 FOR SHARE;
  # 排他锁
  SELECT name FROM t_user WHERE id = 1 FOR UPDATE;
  

• UPDATE、INSERT、DELETE 语句

  # 排他锁
  UPDATE t_user set a = a + 1 WHERE id = 1;
  

如果对扫描的行使用唯一索引获取锁，SELECT ... FOR UPDATE 和 SELECT ... FOR SHARE 语句通常会释放不符合包含在结果集中的行的锁，例如，如果它们不满足 WHERE 子句中给出的条件，就会释放这些不满足查询条件的行。在某些情况下，行锁可能不会立即释放，因为结果行与其原始源之间的关系在查询执行期间丢失，例如，使用 UNION 合并两个查询结果集时，在评估表中扫描的行（已经被锁定）是否符合结果集之前，可能会将这些行插入到一个新的临时表中，在这种情况下，临时表中的行与原始表中的行的关系将丢失，并且原始表中的行直到查询执行结束才解锁。

对于锁定读取（SELECT ... FOR UPDATE、SELECT ... FOR SHARE）、 UPDATE 和 DELETE 语句，加锁类型取决于该语句是使用具有唯一搜索条件的唯一索引，还是范围类型搜索条件：

• 对于具有唯一搜索条件的唯一索引， InnoDB 只对找到的索引记录加锁，而不锁定其之前的间隙；

• 对于其他搜索条件以及非唯一索引， InnoDB 会对扫描的索引范围加锁，它会使用间隙锁或 next-key lock，来阻止其他会话插入该范围所覆盖的间隙。

通过索引记录搜索时，SELECT ... FOR UPDATE 会阻止其他会话在特定的事务隔离级别中执行 SELECT ... FOR SHARE 操作或读取操作。快照读取会忽略对读取视图中存在的记录设置的任何锁定。

更新

• UPDATE ... WHERE ... 语句

  • 对于具有唯一搜索条件的唯一索引，只使用索引记录锁来锁定该行；

  • 对于其他搜索条件以及非唯一索引，会对搜索遇到的每个记录设置排它的 next-key lock。

• 当 UPDATE 语句修改聚集索引记录时，将会对受影响的二级索引记录进行隐式锁定。

  在插入新的二级索引记录之前执行重复检查扫描时，以及插入新的二级索引记录时，UPDATE 操作还会对受影响的二级索引记录设置共享锁。

删除

• DELETE FROM ... WHERE ... 语句

  • 对于具有唯一搜索条件的唯一索引，只需要索引记录锁来锁定该行；

  • 对于其他搜索条件以及非唯一索引，会对搜索遇到的每个记录设置排它的 next-key lock。

• INSERT 会在插入的行上设置排它的索引记录锁，而不是 next-key lock（即没有间隙锁），并且不会阻止其他会话插入到插入行之前的间隙中。

  在插入行之前，会设置一种称为插入意向间隙锁的间隙锁。此锁表明插入的意图是，插入同一索引间隙的多个事务如果没有插入间隙内的同一位置，则无需互相等待。假设存在值为 4 和 7 的索引记录。尝试插入值 5 和 6 的单独事务在获得插入行上的排他锁之前，每个事务都使用插入意向锁锁定 4 和 7 之间的间隙，但不这样做相互阻塞，因为行不冲突。

  如果记录插入时，发生重复键错误，InnoDB 会在重复索引记录上设置共享锁。

  例如，如果一个会话已经拥有排它锁，此时，其他多个会话尝试插入同一行，然后，当前会话尝试删除该行，其他会话上的共享锁就可能会导致死锁。

  我们以如下示例来介绍这个过程，假设一个 InnoDB 表 t1 具有以下结构：

  CREATE TABLE t1 (i INT, PRIMARY KEY (i)) ENGINE = InnoDB;
  

  现在假设三个会话按顺序执行以下操作：

  Session 1	Session 2	Session 3
  START TRANSACTION;
  INSERT INTO t1 VALUES(1);
  	START TRANSACTION;
  	INSERT INTO t1 VALUES(1);
  		START TRANSACTION;
  		INSERT INTO t1 VALUES(1);
  ROLLBACK;

  会话 1 的第一个操作获取该行的排他锁；会话 2 和 3 的操作都会导致重复键错误，并且它们都请求该行的共享锁。当会话 1 回滚时，它会释放其对该行的独占锁，并且会话 2 和 3 的排队共享锁请求将被授予。此时，会话 2 和会话 3 发生死锁：由于对方持有共享锁，双方都无法获取该行的排他锁。

  如果表 t1 已包含键值为 1 的行，并且三个会话按顺序执行以下操作，则会出现类似的情况：

  Session 1	Session 2	Session 3
  START TRANSACTION;
  DELETE FROM t1 WHERE i = 1;
  	START TRANSACTION;
  	INSERT INTO t1 VALUES(1);
  		START TRANSACTION;
  		INSERT INTO t1 VALUES(1);
  COMMIT;

  会话 1 的第一个操作获取该行的排他锁；会话 2 和 3 的操作都会导致重复键错误，并且它们都请求该行的共享锁。当会话 1 提交时，它会释放其对该行的独占锁，并且会话 2 和 3 的排队共享锁请求将被授予。此时，会话 2 和会话 3 发生死锁：由于对方持有共享锁，双方都无法获取该行的排他锁。

插入

• INSERT ... ON DUPLICATE KEY UPDATE 与简单的 INSERT 不同之处在于，当发生重复键错误时，将在要更新的行上设置排它锁，而不是共享锁。

  • 对重复的主键值，它会使用排它的索引记录锁加锁；

  • 对重复的唯一键值，它会使用排它的 next-key lock 加锁。

• 如果唯一键上没有冲突，则 REPLACE 的执行方式与 INSERT 类似。否则，将在要替换的行上设置独占的 next-key lock。

• INSERT INTO T SELECT ... FROM S WHERE ... 会在插入表 T 的每一行上设置排它的索引记录锁（没有间隙锁）。

  如果事务隔离级别为 READ COMMITTED，InnoDB 会搜索表 S 作为一致性读（无锁）进行。否则，InnoDB 在表 S 中的行上设置共享下一键锁。

  InnoDB 在后一种情况下必须设置锁：在使用基于语句的二进制日志进行前滚恢复期间，每个 SQL 语句必须以与最初执行的方式完全相同的方式执行。

  CREATE TABLE ... SELECT ... 会使用共享的 next-key lock 或作为一致性读来执行 SELECT，如，INSERT ... SELECT。

  当 SELECT 用于构造 REPLACE INTO t SELECT ... FROM s WHERE ... 或 UPDATE t ... WHERE col IN (SELECT ... FROM s ...) 时，InnoDB 会对表 s 中的行设置共享的 next-key lock。

AUTO_INCREMENT 锁定

• InnoDB 在对带有 AUTO_INCRMENT 属性的列初始化时，会在与 AUTO_INCRMENT 列关联的索引末尾设置排他锁。

  AUTO_INCREMENT 用于生成自动增量值的锁定模式，自增锁定模式是在启动时使用 innodb_autoinc_lock_mode 变量配置的：

  • 当 innodb_autoinc_lock_mode = 0 时，表示传统模式，InnoDB 使用特殊的 AUTO-INC 表锁模式，在访问自动增量计数器时，会获取锁并在当前 SQL 语句结束后释放锁（而不是在整个事务结束后释放）。当会话持有 AUTO-INC 表锁时，其他会话无法在表中执行插入。

  • 当 innodb_autoinc_lock_mode = 1 时，表示连续模式，“批量插入”也会发生相同的行为。

  • 当 innodb_autoinc_lock_mode = 2 时，表示交错模式，不使用 AUTO-INC 表级锁。

外键约束

• 如果在表上定义了外键约束，则任何需要检查约束条件的插入、更新或删除操作，都会在记录上设置共享行级锁。即使在约束失败的情况下，InnoDB 也会设置这些锁。

表锁

• LOCK TABLES 会设置表锁，但设置这些锁的是 InnoDB 层之上的 MySQL Server 层。

  如果 innodb_table_locks = 1 且 autocommit = 0，则 InnoDB 可以感知到表锁，并且 MySQL 层也可以感知到行级锁。

  否则，InnoDB 的自动死锁检测将无法检测到涉及此类表级锁的死锁。另外，在这种情况下，由于 MySQL 层不知道 InnoDB 的行级锁，所以，有可能当前会话获得了该表的表锁，然而另一个会话获取了该表的行级锁。然而，这并不会影响事务的完整性。

• 如果 innodb_table_locks = 1（默认），LOCK TABLES 会在每个表上获取两个锁：MySQL Server 层的表锁、InnoDB 的表锁。

  为了避免 InnoDB 锁表，可以设置 innodb_table_locks = 0，即使表的某些记录被其他事务锁定，导致事务没有获取 InnoDB 表锁，LOCK TABLES 也会执行成功。

  在 MySQL 8.1 中， innodb_table_locks = 0 对于使用 LOCK TABLES ... WRITE 显式锁定的表没有影响，但是，却对通过 LOCK TABLES ... WRITE 或 LOCK TABLES ... READ 语句锁表进行读或写有影响。

• 当事务提交或中止时，事务持有的所有 InnoDB 锁都会被释放。

  因此，当 autocommit = 1 时，在 InnoDB 表上，调用 LOCK TABLES 没有多大意义，因为获取的 InnoDB 表锁将立即释放。

• 我们不能在事务中间锁定其他的表，因为 LOCK TABLES 会隐式地执行 COMMIT 和 UNLOCK TABLES。

参考：

• Locks Set by Different SQL Statements in InnoDB