隔离级别，不跳过任何数据。

Question

假设我们想从MySQL (InnoDB)表event中读取新事件。我们记得我们看到的最后一个(自动增量) ID，并查询WHERE id > @LastSeenId。

如果ID 1到10可用，除了ID 4尚未提交外，怎么办？

显然，重要的是我们永远不要跳过任何可能存在的行。如果我们要跳过ID 4，我们将永远看不到它，并且将永远错过它，这是必须防止的。

我认为这取决于查询运行的隔离级别。

Serializable 1.我是否正确理解(以及其他级别)将提供所需的行为？

也就是说，它将等待任何影响与条件匹配的行(id > @LastSeenId)的未提交事务的提交/回滚？

更具体地说，如果我们没有显式地使用数据库事务，结果是否由默认的隔离级别决定?即使对于单个SELECT 查询？。

对于上下文，我们将使用官方的MySQL连接器来实现.NET。

Answer 1

这是您可以用mysql客户端和两个窗口测试自己的东西。

打开窗口1，进入mysql客户端，创建一个表，并将提交的值填充它。

mysql1> use test;

mysql1> create table event (id serial primary key);

mysql1> insert into event values (1), (2), (3), (5);

现在开始一个超越。插入值4，如您的示例所示。

mysql1> begin;

mysql1> insert into event values (4);

请不要提交最后一次插入。

打开窗口2，进入mysql客户端，为会话设置事务隔离，并查询数据范围。

mysql2> use test;

mysql2> set tx_isolation = serializable;

mysql2> begin;

mysql2> select * from event where id >= 1;

选择在这个阶段挂起，等待。

这是因为在可序列化级别上，所有select查询都会隐式地尝试获取共享锁，就好像您在select查询的末尾添加了LOCK IN SHARE MODE (或FOR SHARE in MySQL 8.0语法)子句一样。这是一个锁读。它试图在id值的范围内获取一个间隙锁，但它还无法获得该锁，因为窗口1创建了一个未提交的行，该行位于该范围内。

现在，在窗口1中，提交事务(请确保在50秒后在窗口之前执行2次此操作)：

mysql1> commit;

窗口2立即返回，现在它将看到完整的数据值集。

mysql2> select * from event where id >= 1;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
+----+

现在，尝试在窗口1中插入一个新行：

mysql1> begin;

mysql1> insert into event values (6);

现在这扇窗户挂起来了。为什么？因为它试图锁定正在插入的行，但是窗口2仍然在where id > 1行范围内持有一个间隙锁--其中包括新的值6。

这就是MySQL如何确保可重复读取的方式。它使用间隙锁来防止插入将进入其锁定范围的新行，因为新行将影响当前事务试图保留的行集。

最后，如果窗口2不完成其事务并释放其间隙锁，窗口1将超时：

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

所有这些都与自动增量无关。正如您在我的示例插入中所看到的，无论如何，我正在重写自动增量。它只与行和间隙上的锁有关，而不管这些行中的值是如何生成的。

我还必须指出，您要解决的问题本质上是一个发布/订阅模型，它更适合于消息队列技术，而不是关系数据库技术。您应该考虑使用消息队列作为RDBMS的补充技术。