MySQL官方文档14.5.3 InnoDB中不同的SQL语句设置的锁

原文地址：https://dev.mysql.com/doc/refman/5.7/en/innodb-locks-set.html

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

锁定读取，UPDATE或DELETE通常会在处理SQL语句时扫描的每个索引记录上设置记录锁。在语句中WHERE条件是否将排除该行并不重要。InnoDB不记得确切的WHERE条件，但只知道扫描了哪些索引范围。这些锁通常是next-key锁，它们也会在记录之前立即阻止插入到“间隙”中。但是，可以显式禁用间隙锁，这会导致不使用next-key锁。有关更多信息，请参见第14.5.1节“InnoDB锁”。事务隔离级别也可以影响锁的设置；请参见第14.5.2.1节“事务隔离级别”。

如果在搜索中使用二级索引，并且要设置的索引记录锁是独占的，则InnoDB也会检索相应的聚簇索引记录并在其上设置锁。

第14.5.1节“InnoDB锁定”介绍了共享锁和独占锁之间的区别。

如果没有适合您的语句的索引，并且MySQL必须扫描整个表以处理语句，则表的每一行都会被锁定，从而阻止其他用户的所有插入操作到表中。创建好的索引非常重要，以便您的查询不会不必要地扫描多行。

对于SELECT … FOR UPDATE或SELECT … LOCK IN SHARE MODE，获取已扫描行的锁，并且预计将释放不符合结果集的行（例如，如果它们不符合在WHERE子句中给出的标准）。但是，在某些情况下，行可能不会立即解锁，因为在执行查询期间，结果行与其原始源之间的关系会丢失。例如，在UNION中，可以在评估之前将来自表的扫描（和锁定）行插入临时表中，以确定它们是否符合结果集。在这种情况下，临时表中的行与原始表中的行之间的关系会丢失，并且后面的行在查询执行结束之前不会解锁。

InnoDB按如下方式设置特定类型的锁。

• SELECT … FROM是一致性读取，读取数据库的快照并设置不锁定，除非事务隔离级别设置为SERIALIZABLE。对于SERIALIZABLE级别，搜索会在遇到的索引记录上设置共享的next-key锁。但是，对于使用唯一索引锁定行来搜索唯一行的语句，只需要索引记录锁。
• SELECT … FROM … LOCK IN SHARE MODE在搜索遇到的所有索引记录上设置共享的next-key锁。但是，对于使用唯一索引锁定行来搜索唯一行的语句，只需要索引记录锁。
• SELECT … FROM … FOR UPDATE为搜索遇到的每个记录设置独占的next-key锁。但是，对于使用唯一索引锁定行来搜索唯一行的语句，只需要索引记录锁。

对于搜索遇到的索引记录，SELECT … FROM … FOR UPDATE阻止其他会话执行SELECT … FROM … LOCK IN SHARE MODE或某些事务隔离级别的读取。一致性读取忽略读取视图中存在的记录上设置的任何锁定。

• UPDATE … WHERE …在搜索遇到的每条记录上设置独占的next-key锁。但是，对于使用唯一索引锁定行来搜索唯一行的语句，只需要索引记录锁。
• 当UPDATE修改聚簇索引记录时，将对受影响的二级索引记录执行隐式锁定。在插入新的二级索引记录时和插入新的二级索引记录之前，执行重复检查扫描时，UPDATE操作还会对受影响的二级索引记录执行共享锁定。
• DELETE FROM … WHERE …在搜索遇到的每个记录上设置独占的下一个键锁定。但是，对于使用唯一索引锁定行来搜索唯一行的语句，只需要索引记录锁。
• INSERT在插入的行上设置独占锁。该锁是一个索引记录锁，而不是next-key锁（即没有间隙锁），并且不会阻止其他会话插入到插入行之前的间隙中。

在插入行之前，设置一种称为插入意向间隙锁的间隙锁。这个锁意向以插入到相同索引间隙中的多个事务不需要等待对方的方式插入，如果它们没有插入间隙中的相同位置。假设有索引记录的值为4和7。尝试插入5和6的值的单独事务在插入行上获得排它锁之前用插入意向锁锁定4和7之间的间隔，但不要彼此阻塞因为行不冲突。

如果发生duplicate-key错误，则会设置重复索引记录上的共享锁。如果另一个会话已具有排他锁，则如果有多个会话尝试插入同一行，则此共享锁的使用可能导致死锁。如果另一个会话删除该行，则会发生这种情况。假设一个InnoDB表t1具有以下结构：

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

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

会话1：

START TRANSACTION;
INSERT INTO t1 VALUES(1);

会话2：

START TRANSACTION;
INSERT INTO t1 VALUES(1);

会话3：

START TRANSACTION;
INSERT INTO t1 VALUES(1);

会话1：

ROLLBACK;

会话1的第一个操作获取该行的排它锁。会话2和3的操作都会导致duplicate-key错误，并且它们都会为该行请求共享锁。当会话1回滚时，它将释放对该行的排它锁，并为会话2和3授予排队的共享锁请求。此时，会话2和3发生死锁：因为另一方持有共享锁，所以都不能获得该行的排它锁。

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

会话1：

START TRANSACTION;
DELETE FROM t1 WHERE i = 1;

会话2：

START TRANSACTION;
INSERT INTO t1 VALUES(1);

会话3：

START TRANSACTION;
INSERT INTO t1 VALUES(1);

会话1：

COMMIT;

会话1的第一个操作获取该行的排它锁。会话2和3的操作都会导致duplicate-key错误，并且它们都会为该行请求共享锁。当会话1提交时，它将释放对该行的排它锁，并为会话2和3授予排队的共享锁请求。此时，会话2和3发生死锁：因为另一方持有共享锁，所以都不能获得该行的排它锁。

• INSERT … ON DUPLICATE KEY UPDATE与简单的INSERT不同之处在于，当发生重复键错误时，将在要更新的行上放置独占锁而不是共享锁。对重复的主键值采取独占索引记录锁。独占的next-key锁用于重复的唯一键值。
• 如果在唯一键上没有碰撞，则REPLACE就像INSERT一样完成。否则，独占的next-key锁被放置在要被替换的行上。
• INSERT INTO T SELECT … FROM S WHERE …为插入T的每一行设置排它索引记录锁（不带间隙锁）。如果事务隔离级别为READ COMMITTED，或启用innodb_locks_unsafe_for_binlog并且事务隔离级别不是SERIALIZABLE，InnoDB在S上执行搜索作为一致性读取（无锁）。否则，InnoDB在来自S的行上设置共享的next-key锁。InnoDB必须在后一种情况下设置锁：在使用基于语句的二进制日志进行前滚恢复期间，每个SQL语句都必须以与原来完全相同的方式执行。

CREATE TABLE … SELECT …使用共享的next-key锁或作为一致性读取执行SELECT，如同INSERT … SELECT。

当在构造中使用SELECT时，InnoDB将设置共享的next-key锁在SELECT … FROM s WHERE …或UPDATE t … WHERE col IN（SELECT … FROM s …） 表s中的行。

• 在初始化一个表之前指定的AUTO_INCREMENT列时，InnoDB在与AUTO_INCREMENT列关联的索引的末尾设置排它锁。在访问自动增量计数器时，InnoDB使用特定的AUTO-INC表锁定模式，其中锁只持续到当前SQL语句的末尾，而不是整个事务的末尾。当AUTO-INC表锁被持有时，其他会话不能插入到表中；请参见第14.5.2节“InnoDB事务模型”。

InnoDB获取先前初始化的AUTO_INCREMENT列的值，而不设置任何锁。

• 如果在表上定义了FOREIGN KEY约束，则需要检查约束条件的任何插入，更新或删除都会在它检查约束的记录上设置共享记录级锁。在约束失败的情况下，InnoDB也会设置这些锁。
• LOCK TABLES设置表锁，但它是在InnoDB层之上更高MySQL层设置这些锁。如果innodb_table_locks = 1（缺省值）和autocommit = 0，InnoDB知道表锁，InnoDB上面的MySQL层知道行级锁。

否则，InnoDB的自动死锁检测无法检测涉及这种表锁的死锁。另外，因为在这种情况下，较高的MySQL层不知道行级别锁定，所以可以在另一个会话当前具有行级别锁定的表上获得表锁定。但是，这不会危及事务完整性，如第14.5.5.2节“死锁检测和回滚”中所述。另请参见第14.8.1.7节“InnoDB表的限制”。