不可重复读和幻影读的区别是什么?

我读过维基百科上的隔离(数据库系统)文章,但我有一些怀疑。在下面的例子中,将会发生什么:不可重复读取和幻影读取?

# # # #事务

SELECT ID, USERNAME, accountno, amount FROM USERS WHERE ID=1

# # # #输出:

1----MIKE------29019892---------5000

# # # #事务B

UPDATE USERS SET amount=amount+5000 where ID=1 AND accountno=29019892;
COMMIT;

# # # #事务

SELECT ID, USERNAME, accountno, amount FROM USERS WHERE ID=1

另一个疑问是,在上面的示例中,应该使用哪个隔离级别?,为什么?


在具有不可重复读取的系统中,事务a的第二次查询的结果将反映事务B中的更新—它将看到新的金额。

在允许幻影读取的系统中,如果事务B插入ID = 1的新行,事务a将在执行第二次查询时看到新行;即幻影读是不可重复读的一种特殊情况。


摘自维基百科(其中有非常详细的例子):

不可重复读取发生在这样的情况下:在事务过程中,一行被检索了两次,并且两次读取之间行中的值不同。

and

在事务过程中,当执行两个相同的查询,而第二个查询返回的行集合与第一个查询不同时,就会发生幻影读。

简单的例子:

User A runs the same query twice. In between, User B runs a transaction and commits. Non-repeatable read: The A row that user A has queried has a different value the second time. Phantom read: All the rows in the query have the same value before and after, but different rows are being selected (because B has deleted or inserted some). Example: select sum(x) from table; will return a different result even if none of the affected rows themselves have been updated, if rows have been added or deleted.

在上面的示例中,要使用哪个隔离级别?

您需要什么样的隔离级别取决于您的应用程序。“更好的”隔离级别(例如降低并发性)的成本很高。

在您的示例中,您不会进行幻影读取,因为您只从单行(由主键标识)进行选择。您可以使用不可重复的读取,因此如果这是一个问题,您可能希望有一个隔离级别来防止这种情况。在Oracle中,事务A也可以发出SELECT FOR UPDATE,那么事务B在A完成之前不能更改行。


我喜欢用一种简单的方式来思考:

不可重复读取和幻影读取都与来自不同事务的数据修改操作有关,这些操作是在事务开始后提交的,然后由事务读取。

不可重复读取是指您的事务从另一个事务读取已提交的update。同一行现在的值与事务开始时的值不同。

幻影读取与此类似,但是从另一个事务中读取已提交的insert和/或delete。自开始事务以来,有新行或已消失的行。

脏读类似于不可重复和幻影读,但与读取未提交的数据有关,并且发生在从另一个事务读取UPDATE、INSERT或DELETE时,而另一个事务尚未提交数据。它正在读取“正在进行”的数据,这些数据可能不完整,并且可能永远不会实际提交。


这两种隔离级别之间的实现存在差异。 对于“不可重复读”,需要行锁定。 对于“幻影读取”,需要范围锁定,甚至是表锁定。 我们可以使用两相锁协议来实现这两个级别。


公认的答案主要表明,两者之间所谓的区别实际上根本不重要。

如果“一行被检索了两次,并且行中的值在读取之间不同”,那么它们不是同一行(在正确的RDB说法中不是同一个元组),那么根据定义也确实是“第二个查询返回的行集合与第一个查询不同”的情况。

至于“应该使用哪个隔离级别”这个问题,您的数据对某人、某个地方越重要,Serializable就越有可能是惟一合理的选择。


读现象

脏读:从另一个事务读取未提交的数据 不可重复读取:从另一个事务的UPDATE查询中读取COMMITTED数据 幻影读取:从另一个事务的INSERT或DELETE查询中读取COMMITTED数据

注意:来自另一个事务的DELETE语句,在某些情况下也有非常低的概率导致不可重复读取。不幸的是,当DELETE语句删除当前事务正在查询的同一行时,就会发生这种情况。但这种情况很少见,在每个表中都有数百万行的数据库中更不可能出现这种情况。在任何生产环境中,包含事务数据的表通常都有很大的数据量。

此外,我们还可以观察到,在大多数用例中,更新作业可能比实际的INSERT或delete作业更频繁(在这种情况下,仅存在不可重复读取的危险-在这些情况下不可能出现幻影读取)。这就是为什么update与INSERT-DELETE处理方式不同,导致的异常命名也不同。

处理insert - delete(而不仅仅是处理update)还需要额外的处理成本。


不同隔离级别的好处

READ_UNCOMMITTED什么也阻止不了。这是零 隔离级别 READ_COMMITTED只阻止一种,即脏读 REPEATABLE_READ防止两种异常:脏读和 不可重复读 SERIALIZABLE可以防止所有三种异常:脏读, 不可重复读取和幻影读取

那么为什么不始终设置事务SERIALIZABLE呢?好吧,上述问题的答案是:SERIALIZABLE设置使事务非常慢,这也是我们不希望看到的。

实际上,事务时间消耗的速率如下:

Serializable > repeatable_read > read_committed > read_uncommitted

所以READ_UNCOMMITTED设置是最快的。


总结

实际上,我们需要分析用例并确定隔离级别,以便优化事务时间并防止大多数异常。

注意,数据库在默认情况下可能有REPEATABLE_READ设置。管理员和架构师可能倾向于将此设置作为默认设置,以展示更好的平台性能。


我认为非重复读取和幻影读取之间有一些区别。

不可重复是指有两个事务A和B,如果B能注意到A的修改,那么可能会发生脏读,所以我们让B在A提交后注意到A的修改。

有一个新的问题:我们让B在A提交后注意到A的修改,这意味着A修改了B所持有的行的值,有时B会再次读取该行,因此B将获得与我们第一次获得的不同的新值,我们称之为不可重复的,为了解决这个问题,我们让B在B启动时记住一些东西(因为我还不知道会记住什么)。

让我们考虑一下新的解决方案,我们可以注意到也有新的问题,因为我们让B记住了一些东西,所以不管A发生了什么,B都不会受到影响,但是如果B想要向表中插入一些数据,B检查表,确保没有记录,但是这个数据已经被A插入,所以可能会发生一些错误。我们称之为幻读。


不可重复读异常如下图所示:

Alice和Bob启动两个数据库事务。 Bob读取的post记录和标题列值是Transactions。 Alice将给定post记录的标题修改为ACID的值。 Alice提交她的数据库事务。 如果Bob重读post记录,他将观察到该表行的不同版本。

Phantom Read异常有以下三种情况:

Alice和Bob启动两个数据库事务。 Bob的读取与标识符值为1的post行相关的所有post_comment记录。 Alice添加了一条新的post_comment记录,该记录与标识符值为1的post行相关联。 Alice提交她的数据库事务。 如果Bob重读post_id列值为1的post_comment记录,他将观察到该结果集的不同版本。

因此,虽然非可重复读取适用于单行,但Phantom Read是关于满足给定查询过滤条件的记录范围。


不可重复读是一个隔离级别,幻影读(通过其他事务读取已提交的值)是一个概念(读的类型,例如脏读或快照读)。不可重复读隔离级别允许幻影读,但不允许脏读或快照读。


不可重复读取和幻影读取都是由于一个事务T1看到了在T1完成之前提交的另一个事务T2的更改。不同之处在于,对于同一个逻辑行,不可重复读操作返回不同的值。(例如,如果主键是employee_id,那么在两个结果中,某个员工的工资可能不同。)幻影读取返回两组不同的行,但是对于出现在这两组中的每一行,列值都是相同的。


Non-repeatable read(fuzzy read) is that a transaction reads the same row at least twice but the same row's data is different between the 1st and 2nd reads because other transactions update the same row's data and commit at the same time(concurrently). Phantom read is that a transaction reads the same table at least twice but the number of the same table's rows is different between the 1st and 2nd reads because other transactions insert or delete rows and commit at the same time(concurrently).

我用MySQL和2个命令提示符尝试了不可重复读取和幻影读取。

对于不可重复读和幻像读的实验,我设置read COMMITTED隔离级别发生不可重复读和幻像读:

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

并且,我用id和名称创建了person表,如下所示。

人表:

id name
1 John
2 David

首先,对于不可重复的读取,我在MySQL查询中执行了以下步骤:

Flow Transaction 1 (T1) Transaction 2 (T2) Explanation
Step 1 BEGIN; T1 starts.
Step 2 BEGIN; T2 starts.
Step 3 SELECT * FROM person WHERE id = 2;

2 David
T1 reads David.
Step 4 UPDATE person SET name = 'Tom' WHERE id = 2; T2 updates David to Tom.
Step 5 COMMIT; T2 commits.
Step 6 SELECT * FROM person WHERE id = 2;

2 Tom
T1 reads Tom instead of David after T2 commits.

*Non-repeatable read occurs!!

Step 7 COMMIT; T1 commits.

第二,对于幻影读取,我用MySQL查询执行了以下步骤:

Flow Transaction 1 (T1) Transaction 2 (T2) Explanation
Step 1 BEGIN; T1 starts.
Step 2 BEGIN; T2 starts.
Step 3 SELECT * FROM person;

1 John
2 David
T1 reads 2 rows.
Step 4 INSERT INTO person VALUES (3, 'Tom'); T2 inserts the row with 3 and Tom to person table.
Step 5 COMMIT; T2 commits.
Step 6 SELECT * FROM person;

1 John
2 David
3 Tom
T1 reads 3 rows instead of 2 rows after T2 commits.

*Phantom read occurs!!

Step 7 COMMIT; T1 commits.