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

locking readUPDATEDELETE通常会在处理 SQL 语句时扫描的每个索引记录上设置记录锁定。语句中是否有排除行的WHERE条件并不重要。 InnoDB不记得确切的WHERE条件,但只知道扫描了哪个索引范围。锁通常为next-key locks,也将阻止插入到紧接记录之前的“间隙”中。但是,可以显式禁用gap locking,这将导致不使用下一键锁定。有关更多信息,请参见第 14.7.1 节“ InnoDB 锁定”。事务隔离级别也可以影响设置哪些锁;参见第 14.7.2.1 节“事务隔离级别”

如果在搜索中使用了辅助索引,并且要设置的索引记录锁定是互斥的,则InnoDB还将检索相应的聚集索引记录并对其设置锁定。

如果没有适合您的语句的索引,并且 MySQL 必须扫描整个 table 以处理该语句,则 table 的每一行都将被锁定,从而阻塞其他用户对 table 的所有插入。创建良好的索引很重要,这样您的查询就不必不必要地扫描很多行。

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

  • 选择...从是一致的读取,读取数据库的快照并且不设置锁定,除非将事务隔离级别设置为SERIALIZABLE。对于SERIALIZABLE级别,搜索在遇到的索引记录上设置共享的下一键锁定。但是,对于使用唯一索引锁定行以搜索唯一行的语句,仅需要索引记录锁定。

  • 对于选择...更新选择...锁定共享模式,将为扫描的行获取锁,并预期将其释放给不符合包含在结果集中的行(例如,如果它们不符合WHERE子句中指定的条件)。但是,在某些情况下,行可能不会立即解锁,因为结果行与其原始源之间的关系在查询执行期间会丢失。例如,在UNION中,table 中的扫描(和锁定)行可能在评估它们是否符合结果集之前被插入到临时 table 中。在这种情况下,临时 table 中的行与原始 table 中的行之间的关系将丢失,并且直到查询执行结束后,后行才被解锁。

  • 选择...锁定共享模式在搜索遇到的所有索引记录上设置共享的下一键锁定。但是,对于使用唯一索引锁定行以搜索唯一行的语句,仅需要索引记录锁定。

  • 选择...更新在搜索遇到的每条记录上设置独占的下一键锁定。但是,对于使用唯一索引锁定行以搜索唯一行的语句,仅需要索引记录锁定。

对于索引记录,遇到搜索时,选择...更新阻止其他会话执行选择...锁定共享模式或读取某些事务隔离级别。一致的读取将忽略读取视图中存在的记录上设置的任何锁定。

  • 更新...在哪里...在搜索遇到的每条记录上设置独占的下一键锁定。但是,对于使用唯一索引锁定行以搜索唯一行的语句,仅需要索引记录锁定。

  • UPDATE修改聚集索引记录时,将对受影响的辅助索引记录进行隐式锁定。在插入新的二级索引记录之前执行重复检查扫描时,以及在插入新的二级索引记录时,UPDATE操作还会对受影响的二级索引记录进行共享锁定。

  • 从...删除...在搜索遇到的每条记录上设置独占的下一键锁定。但是,对于使用唯一索引锁定行以搜索唯一行的语句,仅需要索引记录锁定。

  • INSERT在插入的行上设置排他锁。该锁是索引记录锁,不是下一个键锁(即没有间隙锁),并且不会阻止其他会话插入到插入行之前的间隙中。

在插入行之前,设置了一种称为插入意图间隙锁的间隙锁。此锁发出插入意图的 signal 是,如果多个事务未插入间隙中的相同位置,则无需 await 彼此插入的多个事务。假设有索引记录,其值分别为 4 和 7.尝试在插入行上获得排他锁之前,分别尝试插入 5 和 6 的值的每个事务都使用插入意图锁来锁定 4 和 7 之间的间隙,但是没有彼此阻塞,因为行没有冲突。

如果出现重复键错误,则会在重复索引记录上设置一个共享锁。如果另一个会话已经具有互斥锁,则如果有多个会话试图插入同一行,则使用共享锁可能会导致死锁。如果另一个会话删除该行,则会发生这种情况。假设InnoDBtablet1具有以下结构:

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 排队 await 共享锁请求。此时,会话 2 和 3 死锁:由于另一个持有共享锁,因此两个都无法获取该行的排他锁。

如果 table 已经包含键值为 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的不同之处在于,当发生重复键错误时,将排他锁而不是共享锁放在要更新的行上。对重复的主键值采用排它索引记录锁定。对于重复的唯一键值,将使用排他的下一键锁。

  • 如果唯一键上没有冲突,则REPLACE就像INSERT一样完成。否则,排他的下一键锁将放置在要替换的行上。

  • INSERT INTO T SELECT ... FROM S WHERE ...在插入T的每一行上设置排他索引记录锁定(无间隙锁定)。如果事务隔离级别为READ COMMITTED,或者启用了innodb_locks_unsafe_for_binlog,而事务隔离级别不是SERIALIZABLE,则InnoDBS上进行搜索,作为一致的读取(无锁)。否则,InnoDBS的行上设置共享的下一键锁。 InnoDB在后一种情况下必须设置锁:在使用基于语句的二进制日志进行前滚恢复期间,必须以与原始操作完全相同的方式执行每个 SQL 语句。

创建 table...选择...插入...选择一样使用共享的下一键锁执行SELECT或作为一致读取来执行。

在结构REPLACE INTO t SELECT ... FROM s WHERE ...UPDATE t ... WHERE col IN (SELECT ... FROM s ...)中使用SELECT时,InnoDB在 tables的行上设置共享的下一键锁定。

  • InnoDB在初始化 table 上先前指定的AUTO_INCREMENT列的同时,在与AUTO_INCREMENT列关联的索引的末尾设置排他锁。

对于innodb_autoinc_lock_mode=0InnoDB使用特殊的AUTO-INCtable 锁定模式,该模式将在访问自动增量计数器的同时获取并保持锁定到当前 SQL 语句的末尾(而不是整个事务的末尾)。按住AUTO-INCtable 锁时,其他 Client 端无法插入该 table。对于带有innodb_autoinc_lock_mode=1的“批量插入”,也会发生相同的行为。table 级AUTO-INC锁不与innodb_autoinc_lock_mode=2一起使用。有关更多信息,请参见第 14.6.1.6 节“ InnoDB 中的 AUTO_INCREMENT 处理”

InnoDB无需设置任何锁定即可获取先前初始化的AUTO_INCREMENT列的值。

  • 如果在 table 上定义了FOREIGN KEY约束,那么任何需要检查约束条件的插入,更新或删除操作都会在它查看该约束的记录上设置共享记录级锁。在约束失败的情况下,InnoDB还会设置这些锁。

  • LOCK TABLES设置 table 锁,但设置这些锁的是InnoDB层上方的 MySQL 较高层。如果innodb_table_locks = 1(默认值)和自动提交= 0InnoDB知道 table 锁,并且InnoDB之上的 MySQL 层知道行级锁。

否则,InnoDB的自动死锁检测将无法检测到涉及此类 table 锁的死锁。同样,由于在这种情况下,较高的 MySQL 层不知道行级锁,因此可以在另一个会话当前具有行级锁的 table 上获取 table 锁。但是,这并不危及第 14.7.5.2 节“死锁检测和回滚”中讨论的事务完整性。

  • 如果innodb_table_locks=1(默认值),LOCK TABLES将在每个 table 上获取两个锁。除了 MySQL 层上的 table 锁外,它还获得InnoDBtable 锁。 4.1.2 之前的 MySQL 版本未获得InnoDBtable 锁;可以通过设置innodb_table_locks=0来选择旧行为。如果未获取InnoDBtable 锁,则即使 table 的某些记录被其他事务锁定,LOCK TABLES也将完成。

在 MySQL 5.7 中,innodb_table_locks=0对于用锁 table...写明确锁定的 table 无效。对于通过锁 table...写隐式(例如,通过触发器)或通过锁 table...阅读锁定以进行读取或写入的 table,它确实具有作用。

  • 提交或中止事务时,将释放事务持有的所有InnoDB锁。因此,以autocommit=1模式在InnoDBtable 上调用LOCK TABLES并没有多大意义,因为获取的InnoDBtable 锁将立即释放。

  • 您不能在事务中间锁定其他 table,因为LOCK TABLES执行隐式COMMITUNLOCK TABLES