Transactions and Isolation Levels, Demystified

Everyone can recite ACID. Far fewer can tell you what happens when two requests update the same row at the same instant — which is exactly the moment ACID stops being trivia and starts deciding whether your data survives. Isolation levels are the dial that controls it, and most bugs I've seen in "correct-looking" code trace back to not understanding that dial.

A transaction is an all-or-nothing bundle

A transaction groups statements so they commit together or not at all. The canonical example never gets old:

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;  -- debit
UPDATE accounts SET balance = balance + 100 WHERE id = 2;  -- credit
COMMIT;

If the process dies between the two updates, the transaction rolls back — you never leave money debited but not credited. That's the A (atomicity) and it's the reason transactions exist. C (consistency), I (isolation), and D (durability) round it out, but isolation is the one with a knob you can turn and get wrong.

The problem: concurrency creates anomalies

Isolation would be trivial if transactions ran one at a time. They don't — they overlap, and overlap creates specific, named anomalies:

• Dirty read — you read a row another transaction changed but hasn't committed; it might roll back, and you acted on a value that never really existed.
• Non-repeatable read — you read a row twice in one transaction and get different values because someone committed a change in between.
• Phantom read — you run the same query twice and get different rows, because someone inserted or deleted matching rows in between.

Isolation levels are defined by which of these they let happen.

The four levels, from loose to strict

Level              Dirty read   Non-repeatable   Phantom
READ UNCOMMITTED   possible*    possible         possible
READ COMMITTED     no           possible         possible
REPEATABLE READ    no           no               possible*
SERIALIZABLE       no           no               no

Higher isolation means fewer anomalies but more locking/aborts and less concurrency. It's a correctness-versus-throughput trade.

MVCC: how Postgres avoids readers blocking writers

Postgres uses Multi-Version Concurrency Control. Instead of locking a row so nobody else can read it while you write, it keeps multiple versions of the row. Readers see a consistent snapshot as of when their statement (or transaction) started; writers create a new version. The upshot: readers don't block writers and writers don't block readers. That's why Postgres stays fast under concurrent load where a lock-everything approach would grind.

The bug you'll actually write: lost updates

Here's the one that bites real code. Read a value, change it in the app, write it back:

-- Transaction A and B both run this concurrently
SELECT balance FROM accounts WHERE id = 1;   -- both read 100
-- app computes 100 - 50 = 50
UPDATE accounts SET balance = 50 WHERE id = 1;  -- both write 50

Two withdrawals of 50 each, and the balance ends at 50 instead of 0 — one update silently overwrote the other. This lost update passes every test that runs one request at a time. The fixes:

-- Option 1: let the database do the math atomically
UPDATE accounts SET balance = balance - 50 WHERE id = 1;
 
-- Option 2: lock the row for the duration of the transaction
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;

FOR UPDATE locks the selected rows so a concurrent transaction must wait — turning the race into a queue. Or raise isolation to SERIALIZABLE and let Postgres abort one transaction, which you then retry.

The practical takeaway

You don't need to memorize the anomaly table. You need three habits: keep transactions short (long ones hold resources and increase conflict), do arithmetic in the database (balance = balance - x, not read-modify-write in the app), and reach for FOR UPDATE or SERIALIZABLE when two requests can genuinely fight over the same row. Get those right and the concurrency bugs — the ones that never show up until production traffic — mostly disappear.