Definition: Execution of one txn is isolated from that of other txns.

dirty read

A transaction reads data written by a concurrent uncommitted transaction.
Since it is uncommitted it may rollback (and leave to a wrong data being read), leading to a dirty read.

nonrepeatable read

A transaction re-reads data it has previously read and finds that data has been modified by another transaction (that committed since the initial read).
Leading to an unmatched record.

phantom read

A transaction re-executes a query returning a set of rows that satisfy a search condition and finds that the set of rows satisfying the condition has changed due to another recently-committed transaction.

  • Non-repeatable reads concern a single row being updated or deleted by another transaction
  • Phantom reads involve new rows being added or existing rows being removed

serialization anomaly

The result of successfully committing a group of transactions is inconsistent with running those transactions one at a time.

Isolation LevelDirty ReadNonrepeatable ReadPhantom ReadSerialization Anomaly
Read uncommittedAllowed, but not in PGPossiblePossiblePossible
Read committed (PG default)Not possiblePossiblePossiblePossible
Repeatable read (MySQL default)Not possibleNot possibleAllowed, but not in PGPossible
SerializableNot possibleNot possibleNot possibleNot possible
  • SERIALIZABLE: Obtain all locks first; plus index locks, plus strong strict 2PL
  • REPEATABLE READS: Same as above, but no index locks.
  • READ COMMITTED: Same as above, but S locks are released immediately.
  • READ UNCOMMITTED: Same as above but allows dirty reads (no S locks).