Oracle Dirty Reads

Oracle Dirty Reads

There are a few basics on how transactions operate within the relational database model.   In terms of how database isolation levels function, the ANSI/ISO SQL standard (SQL92) breaks these down into four levels of transaction isolation. Each isolation level contains various degrees of impact on transaction processing. The fourth level of transaction isolation, read uncommitted, is not supported by Oracle.  Dirty reads, fuzzy or non-repeatable reads and phantom reads are three phenomena of concern to Oracle database performance.

The isolation levels are explained in terms of dirty reads, fuzzy or non-repeatable reads, or phantom reads, that must be prevented between concurrently executing transactions.  

• Dirty reads:  Dirty reads occur when a transaction reads data that has been written by another transaction that has not yet been committed.

• Fuzzy or non-repeatable reads:  Fuzzy reads occur when a database transaction re-reads data it has already read and then finds that another committed transaction has modified or deleted the same data.

• Phantom reads:  Phantom reads occur when a transaction in Oracle re-executes a query that returns the rows that satisfy a particular search and discovers that another committed transaction has already inserted additional rows that meet the condition.

The database standard for SQL92 expounds these four levels of isolation and whether or not dirty reads, non-repeatable reads or phantom reads are possible within the level of isolation, as shown in the following table: 

 

Isolation Level	Dirty Read	Nonrepeatable Read	Phantom Read
Read uncommitted	Possible	Possible	Possible
Read committed	Not possible	Possible	Possible
Repeatable read	Not possible	Not possible	Possible
Serializable	Not possible	Not possible	Not possible

The Oracle database uses the read committed and serializable isolation levels in addition to a read-only mode. By default, Oracle uses the read committed isolation level for database transactions.

Thus, according to the information in the table, dirty reads are only possible at the read uncommitted isolation level; however, the serializable isolation level is sufficient to prevent dirty reads as well as non-repeatable and phantom reads.

At the Oracle default level of read committed, dirty reads are not possible, but fuzzy or phantom reads are.

Portions of this article were excerpted from Exploring Oracle Internals: Tips and Tricks for the Oracle DBA by Ben Prusinski.

Guarantee your Success! Oracle is the world's most complex, robust and flexible database, considered impossible to master without a mentor. That's why all BC Oracle trainers are working professionals, experts in Oracle who share their tips and secrets.

Burleson is the American Team Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum. Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications. Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  and include the URL for the page.                      Burleson Consulting The Oracle of Database Support Oracle Performance Tuning Remote DBA Services   Copyright ? 1996 -  2012  All rights reserved. Oracle ? is the registered trademark of Oracle Corporation.