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

问题描述

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

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

我已经阅读了Wikipedia中的关于隔离(数据库系统)的文章，但是我有一些疑问.在下面的示例中，将发生什么:不可重复读取和幻像读取?

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?

推荐答案

摘自Wikipedia (为此提供了详尽的示例):

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屋！