Transactions, ACID and Isolation Levels

The problem

Here's the problem. You debit account one. Then the server crashes before you credit account two. Five hundred rupees have vanished. With many users doing this at once, it gets worse: two transfers can read the same balance and both succeed, spending money that isn't there.

A transaction wraps the updates in begin and commit. Now the database promises: either both updates happen, or neither does. Crash before commit, and it's as if you never started. That promise has a name with four parts: ACID.

UPDATE accounts
  SET balance = balance - 500
  WHERE id = 1;        ← done

-- server crashes here --

UPDATE accounts
  SET balance = balance + 500
  WHERE id = 2;        ← never runs

BEGIN;
UPDATE accounts SET balance = balance - 500
  WHERE id = 1;
UPDATE accounts SET balance = balance + 500
  WHERE id = 2;
COMMIT;

crash before COMMIT → nothing happened

ACID

Atomic: all or nothing. Consistent: the rules you declared, like a balance never going negative, hold before and after. Isolated: transactions running at the same time don't see each other's half-finished work. Durable: once the database says committed, the data survives a crash. Two of these are easy. Two hide most of the interview.

How durability works

How does the database keep the durability promise without being slow? With a write-ahead log. On commit, it appends a record of the change to a log file and forces it to disk. That's one sequential write, which is fast. The actual table pages are updated later. After a crash, it replays the log, and nothing committed is lost.

Keep that log in mind. It's also what replication streams to follower databases, which is the next episode but one. Durability and replication are the same mechanism.

Isolation

Now isolation, the part that hides the fine print. Transaction A reads a balance. At the same moment, transaction B is updating it. What does A see? The old value, the new value, or a value B will roll back in a second? Perfect isolation would run them one after the other, but that is far too slow. So databases offer levels.

First, the things that can go wrong. A dirty read: you see data another transaction hasn't committed yet, and it might roll back. A non-repeatable read: you read the same row twice and get different values. A phantom: you re-run a query and new rows appear. And a lost update: two transactions read, both write, one overwrites the other.

Isolation levels

The levels trade safety for speed. Read uncommitted prevents nothing. Read committed, the default in Postgres, prevents dirty reads: you only ever see committed data. Repeatable read also freezes the rows you read for the whole transaction. Serializable prevents everything, as if transactions ran one at a time, but it costs the most and may abort your transaction and ask you to retry.

level            dirty  non-rep  phantom
READ UNCOMMITTED  ✗      ✗        ✗
READ COMMITTED    ✓      ✗        ✗
REPEATABLE READ   ✓      ✓        ✗*
SERIALIZABLE      ✓      ✓        ✓

✓ = prevented · *Postgres prevents it

MVCC

How does Postgres isolate without locking everyone out? Multi-version concurrency control. Every update writes a new version of the row instead of overwriting it. A reader sees the versions that were committed when it started, a consistent snapshot. Readers never block writers and writers never block readers. Old versions are cleaned up later.

Lost updates

Here's the trap interviewers love. Two transactions read a balance of a thousand. A writes a thousand minus five hundred. B writes a thousand minus three hundred. Final balance: seven hundred. It should be two hundred. Read committed, the default, does not prevent this. You have to.

Two fixes. One: don't read then write; let the database do the math in one statement, balance equals balance minus five hundred. Two: when you must read first, lock the row with select for update, or use optimistic locking with a version column and retry if it changed. Say either of these and the interviewer relaxes.

A: SELECT balance → 1000
B: SELECT balance → 1000
A: UPDATE … SET balance = 1000 - 500
B: UPDATE … SET balance = 1000 - 300

final: 700. Should be 200.

READ COMMITTED does NOT prevent this

1. atomic update: let the DB do the math
   UPDATE accounts
   SET balance = balance - 500 …

2. lock the row while you decide
   SELECT … FOR UPDATE;
   (or optimistic: WHERE version = 7)

Rules

Three rules for the real world. Keep transactions short: never call another service inside one, because it holds locks while it waits. Touch rows in the same order everywhere, or two transactions will deadlock waiting for each other. And a transaction covers one database. Across services there is no begin and commit; that needs sagas, episode thirty-three.

In the interview

In the interview, say it like this. The transfer is one transaction, so both rows change or neither. I'd write balance equals balance minus amount in the update itself, to avoid lost updates at read committed. I'd keep the transaction short with no external calls, and add a check constraint so a balance can never go negative. That's a complete answer.