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的行加排它的记录锁,以及正无穷

 

posted @ 2022-08-03 17:33  废物大师兄  阅读(538)  评论(0编辑  收藏  举报