如何产生“幻读”在可重复读取中？ （MySQL）

Question

使用“可重复读”，应该可以产生幻读，但是如何产生呢？我需要它作为教授计算机科学学生的示例。

我认为我必须在非索引字段 x 上创建一个“SELECT ... WHERE x<=888”，上限 888 不存在，然后在另一个连接上插入一个值略低于 888 的新行

。它不起作用。我需要一张非常大的桌子吗？或者其他什么？

Answer 1

MySQL RR 隔离级别上的“幻读”隐藏得很深，但仍然可以重现。步骤如下：

1. 创建表 ab(a int 主键, b int);

2. Tx1：
   开始;
   从ab中选择*； // 空集

3. Tx2:
   开始;
   插入 ab 值(1,1);
   犯罪;
4. Tx1：
   从ab中选择*； // 空集，预期的幻像读取丢失。
   更新 ab 集 b = 2，其中 a = 1； // 1 行受影响。
   从ab中选择*； // 1 行。 幻象读到这里！！！
   提交；

Answer 2

埃里克，

我刚刚用大量的行进行了测试。

您永远不会在 InnoDB mysql 上发现具有读提交或更受限制的隔离级别的幻象。文档中对此进行了解释：

REPEATABLE READ：对于一致读取，与 READ COMMITTED 隔离级别有一个重要区别：同一事务中的所有一致读取读取由第一次读取建立的快照。此约定意味着，如果您在同一事务中发出多个普通（非锁定）SELECT 语句，则这些 SELECT 语句彼此之间也是一致的。请参见第 13.6.8.2 节“一致的非锁定读取”。

但您也无法在读提交隔离级别中找到幻像：这是必要的，因为必须阻止“幻像行”才能使 MySQL 复制和恢复正常工作。

更详细的信息： http://dev.mysql.com/doc /refman/5.1/en/set-transaction.html

我认为您需要转向另一个数据库品牌才能向学生展示幻影。我同时使用 MSSQLSERVER 和 Oracle。

嗯……你的第一个问题很遗憾。

Answer 3

正如其他人所写的那样，InnoDB 应该防止幻读。

但 InnoDB 有一个与锁定相关的不同奇怪行为。当查询获取锁时，它始终获取该行的最新版本的锁。因此，请在两个并发会话中尝试以下“

CREATE TABLE foo (i INT PRIMARY KEY, val INT);
INSERT INTO foo (i, val) VALUES (1, 10), (2, 20), (3, 30);

Then”（打开两个终端窗口）：

-- window 1                               -- window 2
START TRANSACTION;
                                          START TRANSACTION;

                                           SELECT * FROM foo;

 UPDATE foo SET val=35 WHERE i=3;

                                           SELECT * FROM foo;

这应该在两个 SELECT 中显示 val = 10, 20, 30，因为 REPEATABLE-READ 意味着第二个窗口只能看到事务启动时存在的数据。

但是：

                                           SELECT * FROM foo FOR UPDATE;

第二个窗口等待获取第 3 行上的锁。

COMMIT;

现在第二个窗口中的 SELECT 完成，并显示 val = 10, 20, 35 的行，因为锁定该行会导致 SELECT 看到最新提交的版本。 InnoDB 中的锁定操作就像在 READ-COMMITTED 下运行一样，无论事务的隔离级别如何。

您甚至可以来回切换：

                                           SELECT * FROM foo;

                                           SELECT * FROM foo FOR UPDATE;

                                           SELECT * FROM foo;

                                           SELECT * FROM foo FOR UPDATE;

Answer 4

对于隔离级别 REPEATABLE READ 的 InnoDB 引擎重现幻读的可能性是值得怀疑的，因为 InnoDB 使用 多版本并发控制 - 对于每一行，MVCC 引擎都知道插入和删除行时的事务号，并且可以重现行更新的历史记录。

因此，所有后续的 SELECT 语句都将显示事务开始时表的状态，但由同一事务插入、删除或更新的行除外。不会出现其他事务提交的新行，因为它们的插入事务编号会大于该事务的插入事务编号，并且行的范围在这里无关紧要。

我能够为 Apache Derby 数据库重现隔离级别 REPEATABLE READ 的 PHANTOM READS ，因为它不使用多版本并发控制（在撰写本答案时版本为 10.8.2.2）。

要重现，请设置适当的事务级别（在 ij - Derby 的 SQL 客户端中）：

-- Set autocommit off
autocommit off;
-- Set isolation level corresponding to ANSI REPEATABLE READ
set isolation rs;

T1:

SELECT * FROM TableN;

T2:

INSERT INTO TableN VALUES(55, 1);
COMMIT;

T1 再次：

SELECT * FROM TableN;

现在 T1 应该看​​到多一行；

Answer 5

您可以在MySQL中的REPEATABLE READ中产生幻读。

首先，设置REPEATABLE READ：

SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

然后，使用idperson表> 和 名称 如下所示。

person 表：

id	name
1	John
2	David

然后，取 以下步骤使用 MySQL 查询。 *我使用了2个命令提示符：

Flow	Transaction 1 (T1)	Transaction 2 (T2)	Explanation
Step 1	BEGIN;		T1 开始。
第 2 步		BEGIN;	T2 开始。
第 3 步	SELECT * FROM person; 约翰一书 大卫二书		T1 读取 2 行。
第 4 步		INSERT INTO person VALUES (3, 'Tom');	T2 插入包含 3 和 Tom 到 person 表。
第 5 步		COMMIT;	T2 提交。
第 6 步	SELECT * FROM person; 约翰一书 大卫二书		T2 提交后，T1 读取了 2 行。 *暂时不会发生幻读！！
第 7 步	更新人员集 name = 'Lisa'，其中 id = 3;		现在让您惊讶的是，T1 可以将 T2 刚刚插入的新行从 Tom 更新为 Lisa。
第 8 步	从人中选择*； 1 约翰 2 大卫 3丽莎		现在让你惊讶的是，T1 在 T2 提交后读取了 3 行。 *发生幻像读取！！
第 7 步	提交；		T1 提交。

此外，我在 Postgresql 中的 REPEATABLE READ 中执行了上述步骤，但幻读 没有发生。

Answer 6

由于不存在范围锁，可能会发生幻读，示例如下（伪代码）：

Thread1

Transaction 1

Update TableN set X=2 where X=1

wait(s1)
Select TableN where X=1

Commit 

thread2

Transaction 2:

insert into tableN(id, X) values(55,1)
commit;
notify(s1)

在维基百科中，还有另一个幻读示例： Phantom Reads|wikipedia

这里重要的是事务同步，您可以使用同步点。

编辑使用 mysql sleep 函数的示例（未测试）：

--on thread 1
Create TableN(id int, x int);
insert into TableN(id, X) values(1,1);
insert into TableN(id, X) values(2,1);
insert into TableN(id, X) values(3,1);


开始交易；
更新 TableN 设置 X=2，其中 X=1
从 DUAL 中选择 SLEEP(30)；
从 X=1 处选择 TableN；
犯罪;

 --在其他线程中，20秒之前；

开始交易；
插入 TableN(id, X) 值(55,1)；

犯罪;




Answer 7

为了补充 Dani 的良好答案，您可以使用 Microsoft Sql Server 向学生展示该行为。

Sql Server 在可重复读隔离级别中显示幻读，如文档此处。

Postgres 遵循与 InnoDb 相同的概念，如此处所述。对于 Postgres 来说，可重复读取中也不会发生幻读，因此也不适合您的教学目的。

Sql Server 提供了另一种隔离级别——快照，它的作用类似于 MySql InnoDb 和 Postgres 在可重复读方面的作用（这是一种无锁、基于版本的可重复读实现，没有幻读，但不可序列化）。

尽管您确实需要 Windows 计算机，但 Sql Server Express 是免费的。您还可以为自己获取一个 Windows Azure 帐户，并通过 Sql Azure 在线展示该行为。

Answer 8

mysql使用一致性非锁定读取来避免幻读，这意味着如果您运行查询，mysql将使用快照来呈现您的查询。基于撤消日志。
但有一个例外，如果您在此事务中更新一行，您将看到该行的最新版本，因此您可以使用@ColinBinWang提供的方法产生幻读。
如果您想要更详细的解释，请参阅 https:// /dev.mysql.com/doc/refman/8.0/en/innodb-concient-read.html

Answer 9

尝试了解最高评分答案的根本原因。
为了防止幻读，InnoDB中的RR级别实际上是通过MVCC代表undo日志来实现的，该机制也称为一致性读或快照读。

撤消日志的工作方式就像一条链，用于更新其事务 ID。因此，当另一个事务成功插入记录 A（已提交）时，然后可以执行对 A（在其他事务中）的更新，因为撤消日志现在可以找到该条目。此外，由于记录 A 上没有获取锁，因此更新不会被阻止。总的来说，这在 RR 隔离语义中是完全可以接受的。

似乎只有通过锁定读取来防止光子读取才是安全的，例如使用涉及下一个键锁定的“SELECT ... FOR UPDATE”。