Search notes:

SQL: Isolation level

SQL-92 standard

Depending on a transaction's current isolation level, some phenomena when reading data are possible or not possible:
Dirty read A transaction (T1) reads data that was inserted or modified by another transaction (T2), but is not yet committed by T2. This read is dirty because if T2 decides to rollback a change, T1 works with data that must be considered inexisting.
Non-repeatable read A transaction reads some twice. When reading it the second time, the reader discovers that the data was modified or deleted (and committed) in the meantime by another transaction. Such a read is called non-repeatable (and somtimes also «fuzzy») because the same select statement is not guaranteed to return the same data in the same transaction.
Phantom read A transaction formulates a criteria to read data and uses this criteria to read data twice. With a phantom read, more data is returned the second time (but unlike in a fuzzy read, the existing records didn't change). This phenomenon is called phantom read because the new records seem to have a phantom origin. A phantom read is thus a special case of a non-repeatable read.
The SQL-92 standard mentions 4 transaction isolation levels (ordered from lowest to highest amount of consistency and protection) which define which of these phenomenons are possible:
Isolation level Dirty read non-repeatable read Phantom read
Read uncommitted Possible Possible Possible
Read comitted Prevented Possible Possible
Repeatable read Prevented Prevented Possible
Serializable Prevented Prevented Prevented
Read committed is the default isolation level.

Thanks

Christian Merz notified me of a factual error on this page and helped me correct it.

See also

The ACID properties of a database.
Isolation levels in SQL Server.
Isolation levels in Oracle
Oracle never permits dirty reads.

Index