|René Nyffenegger's collection of things on the web|
René Nyffenegger on Oracle - Most wanted - Feedback
- Follow @renenyffenegger
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:
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
A phantom read is thus a special case of a non-repeatable read.
Said SQL 92 standard also speaks of four different isolation levels:
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.