René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback -
 

ANSI isolation levels

Read phenomenas

The ANSI/ISO standard SQL 92 speaks of three different phenomenas when a session (S1) reads data that another session (S2) might have changed:
  • dirty read,
  • Non-repeatable read and
  • Phantom read

dirty read

If S1 can read data that S2 has written but not yet commited, this is called a dirty read. It is dirty because S2 might later decide to rollback the transaction which leads to the situation that S1 works with data that actually must be considered as non-existant.
Oracle doesn't allow dirty reads.

Non-repeatable read

S1 reads data which is later changed and commited by S2. If S1 reads the same data again (after S2's commit) and finds it to have changed or to be deleted (according to S2's changes), this is called a non-repeatable read. It is called non-repeatable because the same select statement doesn't return the same data (within the same transaction).

Phantom read

S1 reads data (select) with a specific where condition. After this read, S2 inserts some data that meets the S1's where condition and commits the inserted data. When S1 issues a select statement with the same where condition, it finds new records. It is called phantom read because the new records seem to be of phantom origin.
A phantom read is thus a special case of a non-repeatable read.

Isolation levels

Said SQL 92 standard also speaks of four different isolation levels:
  • read uncommitted
  • read commited
  • repeatable read
  • serializable
According to SQL 92 (but not to Oracle), a transaction is always in exactly one of these isolation levels. Further: the isolation level cannot change within a transaction.
These isolation levels define which read phenomenas can be expected.
Isolation level Read phenomena
Dirty read Non-repeatable read Phantom read
read uncommitted yes yes yes
read committed no yes yes
repeatable read no no y
serializable no no no
Oracle can "only" set the isolation levels to either read commited or serializable using set transaction.