mysql - 隔离级别并且不跳过任何数据

Question

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

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

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

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

<强>1。我是否正确理解 Serializable(而不是其他级别)将提供所需的行为？

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

<强>2。更具体地说，如果我们没有明确使用数据库事务，结果是否由默认隔离级别决定 - 即使是单个 SELECT 查询？

对于上下文，我们正在使用 .NET 的官方 MySQL 连接器。

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客户端，设置session事务隔离，查询数据范围。

mysql2> use test;

mysql2> set tx_isolation = serializable;

mysql2> begin;

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

选择在此阶段挂起，等待。

这是因为在可序列化级别，所有选择查询都隐式尝试获取共享锁，就好像您在中添加了LOCK IN SHARE MODE(或MySQL 8.0 语法) 子句到选择查询的末尾。这是 locking read .它正在尝试获取 gap lock在 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 仍然在 range 行 where id > 1 上持有间隙锁 — 其中包括新值 6。

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

最终，如果窗口 2 未完成其事务并释放其间隙锁，则窗口 1 将超时:

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

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

我还必须评论说，您要解决的问题本质上是一个发布/订阅模型，它更适合 message queue technology ，不是 RDBMS 技术。您应该考虑使用消息队列作为 RDBMS 的补充技术。