Does oracle allow the uncommited read option?

Question

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

Answer 1

Tom provides a great answer to this:

http://www.oracle.com/technology/oramag/oracle/05-nov/o65asktom.html

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.

...

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.

Answer 2

Tom Kyte's answer is correct WRT oracle, there is no such thing as a dirty read due to its MVCC architecture.

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

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.

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.

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

• List of all products

Here's an example of a situation where reads need consistency:

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

p.s. MVCC = Multi-Version Concurrency Control (wikipedia is your friend)