Does oracle allow the uncommitted read option?
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.
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).
Tom provides a great answer to this: 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.
...
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.
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.
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.
(ps. Just set up my account so I am not able to comment on other responses.)