不可重复读和幻读有什么区别? [英] What is the difference between Non-Repeatable Read and Phantom Read?

问题描述

不可重复读和幻读有什么区别?

What is the difference between non-repeatable read and phantom read?

我已经阅读了来自维基百科的隔离(数据库系统)文章，但我有几个疑问.在下面的例子中，会发生什么:不可重复读和幻读?

I have read the Isolation (database systems) article from Wikipedia, but I have a few doubts. In the below example, what will happen: the non-repeatable read and phantom read?

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;

交易 A

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

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

Another doubt is, in the above example, which isolation level should be used? And why?

推荐答案

来自维基百科(其中有很好的详细示例):

From Wikipedia (which has great and detailed examples for this):

发生不可重复读，当在事务过程中，一行被检索两次并且行内的值在读取之间不同.

A non-repeatable read occurs, when during the course of a transaction, a row is retrieved twice and the values within the row differ between reads.

和

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

A phantom read occurs when, in the course of a transaction, two identical queries are executed, and the collection of rows returned by the second query is different from the first.

简单的例子:

• 用户 A 两次运行相同的查询.
• 在此期间，用户 B 运行一个事务并提交.
• 不可重复读:用户 A 第二次查询的 A 行有不同的值.
• 幻读:查询中的所有行前后都有相同的值，但是选择了不同的行(因为B删除或插入了一些).示例:select sum(x) from table; 将返回不同的结果，即使受影响的行本身没有被更新，如果行被添加或删除.

• 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.

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

In the above example,which isolation level to be used?

您需要的隔离级别取决于您的应用程序.更好"的隔离级别(例如减少并发)需要付出高昂的代价.

What isolation level you need depends on your application. There is a high cost to a "better" isolation level (such as reduced concurrency).

在您的示例中，您不会有幻读，因为您仅从单行(由主键标识)中进行选择.您可以拥有不可重复读取，因此如果这是一个问题，您可能需要一个隔离级别来防止这种情况发生.在 Oracle 中，事务 A 也可以发出 SELECT FOR UPDATE，然后事务 B 在 A 完成之前不能更改行.

In your example, you won't have a phantom read, because you select only from a single row (identified by primary key). You can have non-repeatable reads, so if that is a problem, you may want to have an isolation level that prevents that. In Oracle, transaction A could also issue a SELECT FOR UPDATE, then transaction B cannot change the row until A is done.

这篇关于不可重复读和幻读有什么区别?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！