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

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

一个锁定读，一个 UPDATE
或 DELETE
一般设置纪录是在SQL语句的处理扫描每个索引记录锁定。WHERE
语句中是否存在排除该行的条件并不重要 。InnoDB
不记得确切的WHERE
条件，而是只知道扫描了哪个索引范围。锁通常是 下一键锁，也可以阻止在记录之前插入“ gap ”。但是， 可以明确禁用间隙锁定，这将导致不使用下一键锁定。有关更多信息，请参见 “ InnoDB锁定”。事务隔离级别也可以影响设置哪些锁；请参见 “事务隔离级别”。

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

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

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

•SELECT ... FROM
是一致的读取，读取数据库的快照并且不设置锁定，除非将事务隔离级别设置为 SERIALIZABLE
。对于 SERIALIZABLE
级别，搜索会在遇到的索引记录上设置共享的下一键锁定。但是，对于使用唯一索引锁定行以搜索唯一行的语句，仅需要索引记录锁定。•对于SELECT ... FOR UPDATE
或 SELECT ... LOCK IN SHARE MODE
，将为扫描的行获取锁，并预期将其释放给不符合包含在结果集中的行（例如，如果它们不符合该WHERE
子句中给出的条件）。但是，在某些情况下，行可能不会立即解锁，因为结果行与其原始源之间的关系在查询执行期间会丢失。例如，在 UNION
，从表中扫描（并锁定）的行可能会在评估它们是否符合结果集之前插入到临时表中。在这种情况下，临时表中的行与原始表中的行之间的关系将丢失，并且直到查询执行结束后，后行才被解锁。•SELECT ... LOCK IN SHARE MODE
在搜索遇到的所有索引记录上设置共享的下一键锁定。但是，对于使用唯一索引锁定行以搜索唯一行的语句，仅需要索引记录锁定。•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
具有以下结构：

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

第二场：

第三节：

第一场：

会话1的第一个操作获取该行的排他锁。会话2和3的操作都导致重复键错误，并且都请求该行的共享锁。会话1回滚时，它将释放该行的排他锁，并为会话2和3排队等待共享锁请求。此时，会话2和3死锁：由于另一个持有共享锁，因此两个都无法获取该行的排他锁。如果表已经包含键值为1的行，并且三个会话按顺序执行以下操作，则会发生类似情况：第一场：

第二场：

第三节：

第一场：

会话1的第一个操作获取该行的排他锁。会话2和3的操作都导致重复键错误，并且都请求该行的共享锁。会话1提交后，它将释放该行上的排他锁，并授予会话2和3排队的共享锁请求。此时，会话2和3死锁：由于另一个持有共享锁，因此两个都无法获取该行的排他锁。•INSERT ... ON DUPLICATE KEY UPDATE
与简单的区别在于， INSERT
在发生重复键错误时，将排他锁而不是共享锁放在要更新的行上。对重复的主键值采用排它索引记录锁定。对于重复的唯一键值，将使用排他的下一键锁。•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 ...
在执行 SELECT
与共享next-key锁定或作为一致读取，作为用于 INSERT ... SELECT
。在 或中SELECT
使用 a时，对来自table的行设置共享的下一键锁。 REPLACE INTO t SELECT ... FROM s WHERE ...``UPDATE t ... WHERE col IN (SELECT ... FROM s ...)``InnoDB``s
•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处理”。InnoDB``AUTO_INCREMENT
无需设置任何锁定即可获取先前初始化的列的值。•如果FOREIGN KEY
在表上定义了约束，则任何需要检查约束条件的插入，更新或删除操作都会在它查看以检查约束的记录上设置共享记录级锁。 InnoDB
在约束失败的情况下也会设置这些锁。•LOCK TABLES
设置表锁，但是InnoDB
设置这些锁的层是MySQL的更高层 。 InnoDB
知道表锁if innodb_table_locks = 1
（默认）和 autocommit = 0
，并且上面的MySQL层InnoDB
知道行级锁。否则，InnoDB
的自动死锁检测将无法检测到涉及此类表锁的死锁。同样，由于在这种情况下，较高的MySQL层不知道行级锁，因此可以在另一个会话当前具有行级锁的表上获取表锁。但是，这并不危害事务完整性，如 “死锁检测”中所述。•LOCK TABLES
如果innodb_table_locks=1
（默认），则在每个表上获取两个锁。除了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 ... WRITE
隐式（例如，通过触发器）或by 锁定以进行读取或写入锁定的表，确实有效果 LOCK TABLES ... READ
。•InnoDB
提交或中止事务时，将释放事务持有的 所有锁。因此，它并没有多大意义，调用 LOCK TABLES
上 InnoDB
表中 autocommit=1
模式，因为所获得的InnoDB
表锁将被立即释放。•您不能在事务中间锁定其他表，因为LOCK TABLES
 执行隐式COMMIT
和 UNLOCK TABLES
。