oracle 是否允许未提交的读取选项？

Does oracle allow the uncommitted read option?

提问by sarego

In db2 a query with a 'with ur' clause allows the query to be an uncommitted read and so does the 'with nolock' clause in mysql. Is there such an option in oracle too... If not why??

在 db2 中，带有“with ur”子句的查询允许查询成为未提交的读取，mysql 中的“with nolock”子句也是如此。oracle中也有这样的选项......如果没有为什么？

回答by Nick Pierpoint

Tom provides a great answer to this: On Transaction Isolation Levels

Tom 对此提供了一个很好的答案：On Transaction Isolation Levels

He says:

他说：

The READ UNCOMMITTED isolation level allows dirty reads. Oracle Database doesn't use dirty reads, nor does it even allow them. The basic goal of a READ UNCOMMITTED isolation level is to provide a standards-based definition that allows for nonblocking reads.

READ UNCOMMITTED 隔离级别允许脏读。Oracle 数据库不使用脏读，甚至也不允许。READ UNCOMMITTED 隔离级别的基本目标是提供一个基于标准的定义，允许非阻塞读取。

...

...

Now, a database that allowed a dirty read ... not only does it return the wrong answer, but also it returns ... [an answer] ... that never existed in the table. In a multiuser database, a dirty read can be a dangerous feature. Personally, I've never seen the usefulness of it...

The point here is that dirty read is not a feature; rather, it's a liability. In Oracle Database, it's just not needed. You get all of the advantages of a dirty read—no blocking—without any of the incorrect results.

现在，一个允许脏读的数据库......不仅返回错误的答案，而且它返回...... [一个答案] ...在表中从未存在过。在多用户数据库中，脏读可能是一个危险的特性。就我个人而言，我从未见过它的用处......

这里的重点是脏读不是一个特性；相反，这是一种责任。在 Oracle 数据库中，它只是不需要。您可以获得脏读的所有优点——无阻塞——而不会产生任何不正确的结果。

回答by Mike S

Tom Kyte's answer is correct WRT oracle, there is no such thing as a dirty read due to its Multi-Version Concurrency Control (MVCC) architecture.

Tom Kyte 的答案是正确的 WRT oracle，由于其多版本并发控制 (MVCC) 架构，没有脏读之类的东西。

From the perspective of application functionality, I completely agree with Tom; there is no good reason or dirty reads.

从应用功能的角度，我完全同意Tom；没有充分的理由或脏读。

Why ever use it outside of Oracle?Where there is no MVCC (e.g. MySQL, Ingres) it is a trick to get around locking problems that can slow performance or cause the locking system to "run out of locks" if not properly tuned. In the same way that you need to tune rollback/undo in Oracle, you need to manage the locking system in non-MVCC databases.

为什么要在 Oracle 之外使用它？在没有 MVCC（例如 MySQL、Ingres）的情况下，这是一种解决锁定问题的技巧，如果没有正确调整，锁定问题可能会降低性能或导致锁定系统“用完锁”。与您需要在 Oracle 中调整回滚/撤消的方式相同，您需要管理非 MVCC 数据库中的锁定系统。

So why might it be useful with Oracle-- as a performance boost for read-only functions where "wrong data" is highly unlikely and highly inconsequential. In MySQL/DB2/Ingres/Informix (not sure about SQL Server/Sybase) it can be used to bypass the lock management facility for performance.

那么为什么它可能对 Oracle 有用——作为只读函数的性能提升，其中“错误数据”极不可能且非常无关紧要。在 MySQL/DB2/Ingres/Informix（不确定 SQL Server/Sybase）中，它可用于绕过锁管理工具以提高性能。

Here's an example of a situation where reads do not needconsistency:

这是读取不需要一致性的情况的示例：

• List of all products

• 所有产品一览

Here's an example of a situation where reads needconsistency:

这是读取需要一致性的情况的示例：

• List of products in stock

• 库存产品清单

Oracle just doesn't even conceive of dirty reads, nor could it be "added as a feature" without actually loosing the benefit of performance (i.e. too many tricks would be required to get the dirty data in Oracle's true MVCC architecture).

Oracle 甚至没有想到脏读，也不能在不实际失去性能优势的情况下将其“添加为一个特性”（即在 Oracle 的真正 MVCC 架构中获取脏数据需要太多技巧）。

回答by David Smith

WITH UR rationale: When it comes to a SELECT ONLY (report) query, it makes no sense to wait for a commit. If you are reporting on a table that is getting updated, whether you get that update or not is inconsequential. The dirty read is just as valid as the data after the commit. Consider if the query were to have hit that locked record a second earlier.

WITH UR 基本原理：当涉及 SELECT ONLY（报告）查询时，等待提交是没有意义的。如果您正在报告正在更新的表，那么您是否获得该更新是无关紧要的。脏读与提交后的数据一样有效。考虑查询是否会提前一秒命中该锁定记录。

If you run a query against a changing table, you are not getting any set point in time. The data accessed at the beginning of the query is at an earlier point in time then the data accessed at the end of the query. There could be numerous updates to the table that may or may not be included in the query results.

如果您对更改的表运行查询，则不会获得任何设定的时间点。在查询开始时访问的数据比在查询结束时访问的数据更早。查询结果中可能包含也可能不包含对表的大量更新。

Using WITH UR and other DBMS equivalents, provides for a performance gain as queries do not wait for commits and it doesn't introduce any loss in data integrity.

使用 WITH UR 和其他 DBMS 等价物可以提高性能，因为查询不会等待提交，并且不会造成数据完整性的任何损失。

(ps. Just set up my account so I am not able to comment on other responses.)

（ps。刚刚设置了我的帐户，所以我无法对其他回复发表评论。）

回答by Christian Miksch

well, UR means uncommitted read across commit boarders of other long running transactions. Dirty read is a old term for uncommitted read. A read into incomplete physical pages isn't possible in DB2 and I hope in Oracle, too. I locate SET ISOLATION LEVEL READ UNCOMMITTED, It seems to be possible in Oracle, too. From point of getting consistence I prefer READ UNCOMMITTED, because CS might generate serialized different returns of one table because of not reaching values. That might wasn't request by an report or dump. Eg. If you want to know - theoretical - values of transactions that will committed later, too..

好吧，UR 意味着跨其他长时间运行事务的提交边界的未提交读取。脏读是未提交读的旧术语。在 DB2 中无法读取不完整的物理页面，我希望在 Oracle 中也是如此。我找到了 SET ISOLATION LEVEL READ UNCOMMITTED，在 Oracle 中似乎也可以。从获得一致性的角度来看，我更喜欢 READ UNCOMMITTED，因为 CS 可能会因为未达到值而生成一张表的序列化不同返回值。这可能不是报告或转储所要求的。例如。如果你想知道 - 理论 - 稍后将提交的交易的价值，也......

https://docs.oracle.com/javadb/10.6.2.1/ref/rrefsqlj41180.html

https://docs.oracle.com/javadb/10.6.2.1/ref/rrefsqlj41180.html

回答by Mohammed Salman Shaikh

While the above answers are truly correct, you can take a look at Autonomous Transactions but keep in mind that they are not recommended and you can take a look at here Autonomous Transactionsand the disadvantages here Autonomous Transactions a Poor Mis-Understood Feature.

虽然上述答案确实正确，但您可以查看 Autonomous Transactions，但请记住，不推荐使用它们，您可以查看此处的Autonomous Transactions和此处Autonomous Transactions a Poor Mis-Understood Feature的缺点 。