Isolation
Definition: Execution of one txn is isolated from that of other txns.
- Uses Optimistic Concurrency Control or MVCC
| 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 READ: 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).