通过InnoDB中的不同SQL语句设置的锁

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

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

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

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

SELECT ... FROM是一致的读取，读取数据库的快照并且不设置锁定，除非将事务隔离级别设置为SERIALIZABLE。对于SERIALIZABLE级别，搜索会在遇到的索引记录上设置共享的下一键锁定。但是，对于使用唯一索引来搜索唯一行的行锁定的语句，仅需要索引记录锁定。
SELECT ... FOR UPDATE并SELECT ... FOR SHARE使用唯一索引获取锁扫描行，并释放锁不包含在结果集中的资格（例如，如果它们不符合中给出的条件行语句WHERE条款）。但是，在某些情况下，行可能不会立即被解锁，因为结果行与其原始源之间的关系在查询执行期间会丢失。例如，在UNION，从表中扫描（并锁定）的行可能会在评估它们是否符合结果集之前插入到临时表中。在这种情况下，临时表中的行与原始表中的行之间的关系将丢失，并且直到查询执行结束后，后行才被解锁。
对于锁定读取（SELECT使用FOR UPDATE或FOR SHARE），UPDATE和DELETE语句，所采取的锁定取决于语句是使用具有唯一搜索条件的唯一索引还是范围类型搜索条件。
- 对于具有唯一搜索条件的唯一索引，InnoDB仅锁定找到的索引记录，而不锁定其前的空白。
- 对于其他搜索条件和非唯一索引，请InnoDB使用间隙锁定或下一键锁定来锁定扫描的索引范围，以阻止其他会话插入该范围所覆盖的间隙。有关间隙锁定和下一键锁定的信息，请参见“ InnoDB锁定”。
对于索引记录，搜索遇到，SELECT ... FOR UPDATE阻止其他会话执行SELECT ... FOR SHARE或读取某些事务隔离级别。一致的读取将忽略读取视图中存在的记录上设置的任何锁定。
UPDATE ... WHERE ...在搜索遇到的每条记录上设置排他的下一键锁定。但是，对于使用唯一索引来搜索唯一行的行锁定的语句，仅需要索引记录锁定。
当UPDATE修改一个聚集索引记录，隐含的锁被采取对受影响的第二个索引记录。UPDATE在插入新的二级索引记录之前执行重复检查扫描时，以及在插入新的二级索引记录时，该操作还将对受影响的二级索引记录进行共享锁定。
DELETE FROM ... WHERE ...在搜索遇到的每条记录上设置排他的下一键锁定。但是，对于使用唯一索引来搜索唯一行的行锁定的语句，仅需要索引记录锁定。
INSERT在插入的行上设置排他锁。该锁是索引记录锁，不是下一个键锁（即没有间隙锁），并且不会阻止其他会话插入到插入行之前的间隙中。
在插入行之前，设置了一种称为插入意图间隙锁的间隙锁。此锁发出插入意图的信号是，如果多个事务未插入间隙中的相同位置，则无需等待插入到同一索引间隙中的多个事务。假设有索引记录，其值为4和7。尝试在插入行上获得排他锁之前，尝试插入值5和6的各个事务分别使用插入意图锁来锁定4和7之间的间隙，但不会彼此阻塞，因为行没有冲突。
如果发生重复键错误，则会在重复索引记录上设置一个共享锁。如果另一个会话已经具有互斥锁，则如果有多个会话试图插入同一行，则使用共享锁可能会导致死锁。如果另一个会话删除该行，则会发生这种情况。假设InnoDB表t1具有以下结构：
```
CREATE TABLE t1 (i INT, PRIMARY KEY (i)) ENGINE = InnoDB;
```
现在，假设三个会话按顺序执行以下操作：
第一场：
```
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死锁：由于另一个持有共享锁，因此两个都无法获取该行的排他锁。
如果表已经包含键值为1的行，并且三个会话按顺序执行以下操作，则会发生类似情况：
第一场：
```
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在发生重复键错误时，将排他锁而不是共享锁放在要更新的行上。对重复的主键值采用排它索引记录锁定。唯一的下一键锁定用于重复的唯一键值。
REPLACEINSERT如果唯一键上没有冲突，则完成此操作。否则，排他的下一键锁将放置在要替换的行上。
INSERT INTO T SELECT ... FROM S WHERE ...在插入的每一行上设置排他索引记录锁定（不带间隙锁定）T。如果事务隔离级别为READ COMMITTED，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 ...)InnoDBs
InnoDBAUTO_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 8.0中，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。