Isolation is the ACID property that limits interference between concurrent transactions and gives each transaction a consistent view of the database. Isolation levels trade correctness guarantees against concurrency and latency. section 9.4 section 9.0
The levels range from read uncommitted, which permits dirty reads, through read committed and repeatable read to serializable, which prevents the listed anomalies. Weaker isolation can improve concurrency but may expose applications to anomalies. section 9.4 section 9.4.3
Common anomalies include:
- A dirty read uses a value another transaction has written but not committed. section 9.4.3
- A non-repeatable read returns a different value later in the same transaction after another transaction commits an update. section 9.4.3
- A lost update occurs when concurrent updates to the same value result in one update disappearing. section 9.4.3
Table 9.1 Summary of isolation levels and the data anomalies they allow and disallow. The asterisks identify situations where the anomaly is sometimes possible, depending on the implementation of the database transaction processing system (specifically, how the database engine manages concurrency and enforces isolation levels).
| Isolation level |
Dirty write (P0) |
Dirty read (P1) |
Cursor lost update (P4C) |
Lost update (P4) |
Fuzzy read (P2) |
Phantom (P3) |
Read skew (A5A) |
Write skew (A5B) |
|---|---|---|---|---|---|---|---|---|
| Read uncommitted |
No |
Yes |
Yes |
Yes |
Yes |
Yes |
Yes |
Yes |
| Read committed |
No |
No |
Yes |
Yes |
Yes |
Yes |
Yes |
Yes |
| Cursor stability |
No |
No |
No |
Yes* |
Yes* |
Yes |
Yes |
Yes* |
| Repeatable read |
No |
No |
No |
No |
No |
Yes |
No |
No |
| Snapshot isolation |
No |
No |
No |
No |
No |
Yes* |
No |
Yes |
| Serializable |
No |
No |
No |
No |
No |
No |
No |
No |
Compare which anomalies each isolation level allows or prevents; starred entries depend on database implementation. section 9.4.3
Snapshot isolation can provide a practical middle ground through multiversion concurrency control (MVCC), balancing concurrent execution with correctness guarantees. section 9.7
