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.