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.
 |
|
Get the Complete
Oracle SQL Tuning Information
The landmark book
"Advanced Oracle
SQL Tuning The Definitive Reference" is
filled with valuable information on Oracle SQL Tuning.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
buy it
for 30% off directly from the publisher.
|
