Isolation
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 Level | Dirty Read | Nonrepeatable Read | Phantom Read | Serialization Anomaly |
|---|---|---|---|---|
| Read uncommitted | Allowed, but not in PG | Possible | Possible | Possible |
| Read committed (PG default) | Not possible | Possible | Possible | Possible |
| Repeatable read (MySQL default) | Not possible | Not possible | Allowed, but not in PG | Possible |
| Serializable | Not possible | Not possible | Not possible | Not 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).