ACID and Isolation Levels Explained

Atomicity means a transaction's writes are all-or-nothing — if any part fails, every part rolls back, leaving no partial write visible to anyone. Durability means once a transaction commits, its writes survive a crash — a write-ahead log flushed to disk before the commit acknowledgment returns is the usual mechanism. Isolation governs what one in-progress transaction can see of another concurrent transaction's uncommitted or recently committed work — and it's the one with the most real variation, covered in depth below.

Consistency is the one worth pausing on, because it means something different here than the C in the CAP theorem. ACID's consistency means a transaction takes the database from one state that satisfies its declared invariants (foreign keys, unique constraints, check constraints) to another state that also satisfies them — it is a property the application's schema defines and the database enforces mechanically, not a guarantee about concurrent visibility. CAP's consistency (more precisely, linearizability) is about whether concurrent reads across replicas observe operations in a single global order — a completely different axis, and conflating the two is a common source of confused conversations about "consistency."

Running transactions one at a time would remove every concurrency bug, at the cost of throughput no real system accepts. Isolation levels are the calibrated trade-off: each one forbids some anomalies and tolerates others in exchange for more concurrency. A dirty read sees another transaction's uncommitted write — if that transaction rolls back, you read data that never actually existed. A non-repeatable read re-reads the same row within one transaction and gets a different value because another transaction committed a change in between. A phantom read re-runs the same range query and gets a different set of rows because another transaction inserted or deleted a matching row. A lost update happens when two transactions each read the same row, compute a new value from that stale read, and write it back — one write silently overwrites the other with no error from either side.

The SQL standard defines four isolation levels — Read Uncommitted, Read Committed, Repeatable Read, Serializable — as a matrix of which anomalies each forbids. In practice, almost no production database implements Read Uncommitted's dirty reads (Postgres's Read Uncommitted behaves identically to Read Committed), so the three levels that actually matter day to day are Read Committed, Repeatable Read, and Serializable.

Read Committed is Postgres's default: each individual statement within a transaction sees a fresh snapshot taken at that statement's start, so one statement never sees another's uncommitted data, but two statements in the same transaction can see different committed states if something changed in between. This is fast and matches what most CRUD request handlers actually need.

Repeatable Read takes one snapshot for the entire transaction, not per statement — every statement sees the same consistent view regardless of what commits elsewhere in the meantime. Because Postgres implements this via MVCC snapshot isolation rather than range-locking, it happens to prevent phantom reads too, which is stronger than the SQL standard's minimum requirement for the name "Repeatable Read" — a genuine case where the same isolation-level name means different guarantees on different engines (MySQL's InnoDB Repeatable Read has its own distinct locking behavior). What Postgres's Repeatable Read still permits is write skew: two transactions can each read a value, each conclude their own write is safe based on that read, and both commit — because neither one individually violated the constraint each was checking. Serializable (Postgres uses Serializable Snapshot Isolation, SSI) detects this pattern and aborts one of the transactions with a serialization failure, which the application must catch and retry.

Multi-Version Concurrency Control is the mechanism that makes Postgres's isolation levels affordable. Instead of a reader waiting for a writer's lock to release, every row carries hidden version metadata (xmin/xmax transaction ids in Postgres), and an UPDATE doesn't overwrite a row in place — it inserts a new row version and marks the old one as superseded for transactions that started after the update. A transaction's snapshot determines which row versions it can see: any version created by a transaction that committed after your snapshot was taken is invisible to you, as if it hadn't happened yet from your point of view.

This is why, under Postgres's default Read Committed, a long-running report query never blocks a concurrent UPDATE, and vice versa — they're each working from their own consistent view of row versions, not fighting over the same lock. The cost is that old row versions accumulate until VACUUM reclaims them; a long-running transaction holding an old snapshot open can prevent cleanup and bloat the table.

Trigger. Application code reads balance = 100 for an account, computes 100 - 50 = 50 in the application layer, and issues UPDATE accounts SET balance = 50. A second concurrent request does the identical read-compute-write for the same account at nearly the same time, also computing 50 from the same stale 100. Symptom. Both updates commit under Read Committed — neither statement conflicted with the other, because each was a plain UPDATE ... SET balance = 50, not a conditional check against the value it read. The account should be at 0 after two $50 withdrawals; it's at 50. One withdrawal is silently gone.

Root mechanism. The bug isn't the isolation level failing — it's that the read and the write are two separate statements with application logic in between, so nothing ties the write to the specific value that was read. Recovery: the fix is doing the arithmetic inside the SQL statement — UPDATE accounts SET balance = balance - 50 is a single, row-locked, atomic operation regardless of isolation level, because the database computes the new value from whatever the current row actually holds at write time, not from a value the application cached earlier. Verification: run both withdrawals concurrently against a test account seeded at 100, and confirm the final balance is exactly 0, not 50. Prevention: for any read-then-conditionally-write pattern that can't be expressed as one atomic statement, use SELECT ... FOR UPDATE to lock the row at read time, or raise the transaction to Serializable and handle the resulting retry.