ANSI isolation levels

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

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.

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

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.

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.

Oracle can "only" set the isolation levels to either read commited or serializable using set transaction.