Database Isolation Levels, ACID & Concurrency Anomalies

1. What It Is & Why It Exists

The Core Problem: Concurrency vs. Consistency Trade-off

In high-throughput transactional systems (e.g., banking transfers, inventory reservations, ride dispatch), thousands of concurrent database transactions execute simultaneously. Without isolation controls, interleaving read and write operations creates data corruption anomalies:

• Dirty Reads: Reading uncommitted data that is subsequently rolled back.
• Non-Repeatable Reads: Reading different values for the same row within a single transaction because another transaction modified and committed it.
• Phantom Reads: A query scanning a range of rows receives different row sets because another transaction inserted or deleted matching rows.
• Write Skew: Concurrent transactions read overlapping data sets, satisfy business constraints independently, and write disjoint data sets that collectively violate a global invariant.
• Lost Updates: Two concurrent transactions read a balance ($100), add$10 and $20 respectively, and write back$110 and $120, losing one transaction's increment.

Synthesizing vector architecture diagram...

2. Concurrency Anomalies & ANSI SQL Lattice

Synthesizing vector architecture diagram...

Read each box in the "Distributed Concurrency Anomalies" panel as a short story between two transactions, then follow its dotted arrow to the weakest level that prevents it in the "Isolation Level Protections" panel. A dirty read (T2 sees T1's uncommitted change, then T1 aborts) is fixed by Read Committed. A non-repeatable read (T1 reads the same row twice and gets different data) and a lost update (two read-modify-writes where the second overwrites the first) are fixed by Repeatable Read. Phantom reads (a re-run range query finds new rows) and write skew (two transactions each keep a rule true alone, but break it together) need Serializable. The arrows only go downward: each level also prevents everything the levels above it prevent, and each step costs more locking or more aborted transactions. These are the ANSI minimums: real engines often do more at the same level (PostgreSQL's Repeatable Read also prevents phantoms), as the matrix below shows.

Comprehensive Isolation Levels Matrix