MySQL试图通过在非索引列上执行Delete语句时锁定整个表来防止什么现象

Question

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

给定具有非索引列quantity:的表test

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

Tx1执行1st，注意它还没有提交，这意味着所有获取的锁还没有释放。

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条件是否匹配(，并在Clustered index中的最后一个索引记录之前和之后应用间隙锁，从而导致表被完全阻塞，因此tx2中的insert语句无法获取要插入的行的锁。

来自MySQL手册(针对可重复读取隔离级别(：

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

• 对于其他搜索条件，InnoDB会锁定扫描的索引范围，使用间隙锁或下一个键锁来阻止其他会话插入到该范围所覆盖的间隙中(在我的情况下就是这样(。

考虑到任何给定隔离级别的锁定都是为了防止phenomenas，我有点困惑在这种情况下阻止整个表的原因是什么，我的意思是在这种情况中阻止整个表可以防止什么类型的phenomena？

Answer 1

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

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

有关间隙锁定如何工作的更多详细信息，请查看Percona撰写的这篇文章。