Concurrent Update Phenomena

Describes the problems that occur with 'lax' levels of isolation inside a transaction.

There are 3 general phenomena, they are (based on paraphrases from ThomasKyte @ asktom.oracle.com):

• Dirty read - Also known as "chaos" -- things can change underneath you while you read, giving you completely inconsistent results.

• Non-repeatable read - Read a row at time T1, and attempt to re-read that row at time T2, the row may have changed. It may have disappeared, it may have been updated, and so on. This is often the most commonly used isolation level for performance reasons (i.e. "statement-level" isolation)

• Phantom read - Execute a query at time T1, and re-execute it at time T2, additional rows may have been added to the database, which affect your results. This differs from the non-repeatable read in that in this case, data you already read has not been changed but rather that more data satisfies your query criteria than before.

The various SQL isolation levels are designed to incrementally cure the phenomena:

• READ_UNCOMMITTED - No cure.
• READ_COMMITTED - No dirty reads.
• REPEATABLE_READ - All reads are repeatable, no dirty reads.
• SERIALIZABLE - No phantoms, all reads are repeatable, no dirty reads.

There also are other phenomena such as write-skew that are particular to MultiversionConcurrencyControl databases that implement SnapshotIsolation.