Day84【概念解析】Locks Set by Statement

type

status

date

slug

summary

整理定义

不同语句的的加锁机制（Locks Set by Statement）

本章主要总结下之前学习的行锁，间隙锁，临键锁在不同语句下的表现。

【MySQL :: MySQL 8.0 Reference Manual :: 15.7.3 Locks Set by Different SQL Statements in InnoDB】

复述展开

锁定读取、更新或删除通常会在 SQL 语句处理过程中扫描的每个索引记录上设置记录锁。语句中是否存在排除该行的 WHERE 条件并不重要。 InnoDB不记得确切的WHERE条件，而只知道扫描了哪些索引范围。这些锁通常是下一个键锁，它们也会阻止插入到紧邻记录之前的“间隙”中。但是，可以显式禁用Gap Lock，这会导致不使用Next-key Lock。

如果搜索时使用二级索引，并且要设置的索引记录锁是排它的，InnoDB也会检索相应的聚集索引记录并对其设置锁。

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

在InnoDB中，不同的SQL语句会设置不同的锁定机制：

SELECT ... FROM：这是一种一致性读取，它读取数据库的快照并且不设置锁，除非事务隔离级别设置为SERIALIZABLE。对于SERIALIZABLE级别，搜索会在遇到的索引记录上设置共享的next-key锁。

SELECT ... FOR UPDATE 和 SELECT ... FOR SHARE：这些语句会为扫描到的行加锁，并释放不符合结果集包含条件的行的锁。然而，在某些情况下，行可能不会立即解锁，因为在查询执行过程中，结果行与其原始来源之间的关系可能会丢失。

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

对于具有唯一搜索条件的唯一索引，InnoDB仅锁定找到的索引记录，而不是它之前的间隙。

对于其他搜索条件和非唯一索引，InnoDB使用间隙锁或next-key锁锁定扫描的索引范围，以阻止其他会话插入范围覆盖的间隙。

UPDATE ... WHERE ...：在搜索遇到的每个记录上设置独占的next-key锁。然而，对于使用唯一索引搜索唯一行的语句，只需要索引记录锁。

当UPDATE修改聚簇索引记录时，会在受影响的二级索引记录上隐式地加锁。在插入新的二级索引记录之前执行重复检查扫描时，以及在插入新的二级索引记录时，UPDATE操作还会在受影响的二级索引记录上加共享锁。

"DELETE FROM ... WHERE ..."在搜索遇到的每个记录上设置独占的next-key锁。然而，对于使用唯一索引搜索唯一行的语句，只需要索引记录锁。

INSERT在插入的行上设置独占锁。这个锁是索引记录锁，而不是next-key锁（也就是说，没有间隙锁），并且不阻止其他会话在插入的行之前插入到间隙中。

在插入行之前，会设置一种称为插入意图间隙锁的间隙锁。这种锁表示插入的意图，以便多个事务在同一索引间隙中插入时，如果它们不在间隙内的同一位置插入，就不需要等待彼此。假设有索引记录，其值为4和7。分别尝试插入值5和6的单独事务在获取插入行的独占锁之前，都会用插入意图锁锁定4和7之间的间隙，但是不会阻塞彼此，因为行是非冲突的。

"INSERT ... ON DUPLICATE KEY UPDATE"与简单的"INSERT"不同，因为当出现重复键错误时，会在要更新的行上放置独占锁，而不是共享锁。对于重复的主键值，会取得独占的索引记录锁。对于重复的唯一键值，会取得独占的next-key锁。

如果在唯一键上没有冲突，那么"REPLACE"就像"INSERT"一样进行。否则，会在要替换的行上放置独占的next-key锁。

"INSERT INTO T SELECT ... FROM S WHERE ..."在每个插入到T的行上设置独占的索引记录锁（没有间隙锁）。如果事务隔离级别是READ COMMITTED，InnoDB将S上的搜索作为一致性读取（无锁）。否则，InnoDB在S的行上设置共享的next-key锁。在后一种情况下，InnoDB必须设置锁：在使用基于语句的二进制日志进行向前滚动恢复时，每个SQL语句必须以与原来完全相同的方式执行。

"CREATE TABLE ... SELECT ..."以共享的next-key锁或作为一致性读取执行SELECT，就像"INSERT ... SELECT"一样。

当SELECT用于"REPLACE INTO t SELECT ... FROM s WHERE ..."或"UPDATE t ... WHERE col IN (SELECT ... FROM s ...)"的构造中时，InnoDB在s表的行上设置共享的next-key锁。

在初始化表上之前指定的AUTO_INCREMENT列时，InnoDB在与AUTO_INCREMENT列关联的索引的末尾设置独占锁。

理解体会

总的来说，InnoDB的锁定机制取决于SQL语句的类型和使用的索引类型，以确保数据的一致性和事务的隔离性。

📌

快速跳转链接

【概念解析】启动

【活动打卡】写100个行业概念 | 易浅小站

为什么要参加这个活动呢？起源于S老师的”写100个行业概念“活动。之前一直有关注Scalers老师的公众号，也阅读完了《学习的学问》这本书，感叹于S老师学习成长会的魅力，决定先让自己跟着这个活动坚持个一百天先。

整理定义

复述展开

理解体会

eachenkuang