MySQL 锁和使用唯一索引锁定行的语句

Question

在MySQL文档中14.6.1 InnoDB Locking "Gap Locks"节，据说

Gap locking is not needed for statements that lock rows using a unique index to search for a unique row. ... For example, if the id column has a unique index, the following statement uses only an index-record lock for the row having id value 100 and ...

SELECT * FROM child WHERE id = 100;

If id is not indexed or has a nonunique index, the statement does lock the preceding gap.

然而，在后续部分，14.6.3 Locks Set by Different SQL Statements in InnoDB ,据说

SELECT ... FROM is a consistent read, reading a snapshot of the database and setting no locks unless the transaction isolation level is set to SERIALIZABLE.

似乎后者(无锁)与前者(需要记录或间隙锁)冲突。

我是不是误会了什么？

Answer 1

没有。这里没有矛盾。后面的语句提到 SELECT ... FROM 没有锁定任何东西，因为这是一个没有 WHERE 子句的 SELECT。此外，在前者中，您谈论的是一个非常具体的锁，它只会阻止插入具有特定键值的记录(“间隙锁”)。