InnoDB 中不同SQL语句设置的锁

锁定读、UPDATE 或 DELETE 通常会给在SQL语句处理过程扫描到的每个索引记录上设置记录锁。语句中是否存在排除该行的WHERE条件并不重要。InnoDB不记得确切的WHERE条件，但只知道哪些索引范围被扫描了。锁通常是next-key锁，它也阻止插入到紧挨着记录之前的“间隙”中。然而，间隙锁定可以显式禁用，这会导致next-key锁无法使用。事务隔离级别也会影响到锁的设置。

如果在搜索中使用了二级索引，并且要设置的索引记录锁是互斥的，InnoDB也会检索相应的聚集索引记录并对它们设置锁。

如果没有适合语句的索引，MySQL必须扫描整个表来处理该语句，那么表的每一行都将被锁定，从而阻止其他用户对表的所有插入。创建良好的索引非常重要，这样查询就不会扫描不必要的行。

InnoDB设置的锁的具体类型如下：

1、SELECT ... FROM 是一个一致读，读取数据库的快照并且不设置锁，除非将事务隔离级别设置为SERIALIZABLE。

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

3、对于锁定读（带有 FOR UPDATE 或 FOR SHARE 的 SELECT）、UPDATE 和 DELETE 语句，所采用的锁取决于语句是使用具有唯一搜索条件的唯一索引，还是使用范围类型的搜索条件。

• 对于具有唯一搜索条件的唯一索引，InnoDB 只锁定找到的索引记录，而不锁定它之前的间隙。
• 对于其他搜索条件和非唯一索引，InnoDB锁定所扫描的索引范围，使用间隙锁或next-key锁阻止其他会话对该范围覆盖的间隙进行插入。 

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

5、UPDATE ... WHERE ... 在搜索遇到的每条记录上设置一个排它的next-key锁。但是，对于使用唯一索引来搜索唯一行的语句，只需要一个索引记录锁。

6、DELETE FROM ... WHERE ... 在搜索遇到的每条记录上设置一个排它的next-key锁。但是，对于使用唯一索引来搜索唯一行的语句，只需要一个索引记录锁。

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

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

9、在插入行之前，设置了一种称为插入意向间隙锁的间隙锁。该锁表示要以这样一种方式插入，即插入到同一索引间隙的多个事务如果不在间隙内的同一位置插入，则不需要相互等待。假设有值为4和7的索引记录。尝试插入值5和6的独立事务在获得插入行上的排它锁之前，每个事务都用插入意向锁锁定4和7之间的间隙，但不会阻塞彼此，因为行是不冲突的。

10、INSERT ... ON DUPLICATE KEY UPDATE 与简单的 INSERT 不同之处在于，当发生重复键错误时，将在要更新的行上放置排他锁而不是共享锁。对重复的主键值采用排它索引记录锁。对重复的唯一键值采用排它的next-key锁。

11、INSERT INTO T SELECT ... FROM S WHERE ... 在插入到 T 的每一行上设置一个排它索引记录锁（没有间隙锁）。如果事务隔离级别为 READ COMMITTED，InnoDB 将 S 上的搜索作为一致 读（无锁）。 否则，InnoDB 在 S 的行上设置共享的 next-key 锁。

12、InnoDB在初始化表上先前指定的AUTO_INCREMENT列时，会在与AUTO_INCREMENT列相关的索引的末尾设置一个排它锁。

13、如果在表上定义了一个FOREIGN KEY约束，那么任何需要检查约束条件的插入、更新或删除都会在它查看的记录上设置共享记录级别的锁，以检查约束。InnoDB会在约束失败的情况下也设置这些锁。

14、LOCK TABLES设置表锁，但是设置这些锁的是InnoDB层之上的MySQL层。如果innodb_table_locks = 1(默认值)和autocommit = 0, InnoDB就知道表锁，而且InnoDB上面的MySQL层知道行级锁。

 

https://dev.mysql.com/doc/refman/8.0/en/innodb-locks-set.html

https://dev.mysql.com/doc/refman/8.0/en/innodb-transaction-isolation-levels.html

https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html

 

给表现加一个意向排它锁，然后给行id=4的行加排它的记录锁，以及正无穷