在执行带有非索引列条件的Delete语句时，MySQL试图通过锁定整个表来防止什么现象？

Question

使用可重复读取的MySQL隔离级别。

给定表test具有非索引列quantity

id    |     quantity
--------------------
1     |      10
2     |      20
3     |      30

Tx1首先执行，注意尚未提交，这意味着所有获得的锁尚未释放。

Tx1：

START TRANSACTION;
DELETE FROM test WHERE quantity=10;

现在执行Tx2

Tx2：

START TRANSACTION;
INSERT INTO test(quantity) VALUES (40);
COMMIT;

对于Tx2，我得到以下结果：

Lock wait timeout exceeded; try restarting transaction

我了解到，由于quantity列没有索引，所以delete语句会执行全表扫描，锁定所有行(与where条件匹配与否无关)，并在聚集索引中的最后一个索引记录之前和之后应用间隙锁，从而导致一个完全阻塞的表，因此tx2的insert语句无法获得要插入的行的锁。

来自MySQL 手册(用于可重复读取隔离级别)：

• 对于具有唯一搜索条件的唯一索引，InnoDB只锁定找到的索引记录，而不锁定之前的空白。
• 对于其他搜索条件，InnoDB锁定扫描的索引范围，使用间隙锁或下键锁阻止其他会话插入范围所涵盖的间隙(在我的示例中使用)。

考虑到在任何给定的隔离级别上使用锁来防止phenomenas，我有点搞不懂在这种情况下阻塞整个表的原因是什么，我的意思是在这种情况下阻塞整个表可以防止什么样的phenomena？

Answer 1

默认情况下，InnoDB在Repeatable Read隔离级别使用一致的快照，这意味着您可以获得元组和范围的可重复读取。

即使SQL标准说Phantom Reads是由Serializable阻止的，而Repeatable Read可能不会阻止它。

有关间隙锁定工作方式的详细信息，请参阅这篇文章是Percona写的。