时间戳

首页 > 解决方案 > 隔离级别，不跳过任何数据

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

问题描述

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

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

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

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

1. 我是否正确理解Serializable（并且没有其他级别）将提供所需的行为？

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

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

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

标签： mysqlinnodbisolation-level

解决方案

这是您可以使用 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;

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

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

现在在窗口 1 中，提交事务（确保在 50 秒后窗口 2 超时之前执行此操作）：

mysql1> commit;

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

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

现在尝试在 Window 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 技术。您应该考虑使用消息队列作为 RDBMS 的补充技术。

推荐阅读