Transactions and ACID — Concurrency Hell and the Isolation Levels

Why a balance never evaporates when the power dies mid-transfer, and why the bug that only shows up in production is almost always a concurrency bug. Atomicity, isolation levels, MVCC and deadlocks — from zero assumed knowledge down to the actual PostgreSQL and MySQL parameter names.

The metaphor: a power cut in the middle of a transfer

Take $100 out of Alice's account and put $100 into Bob's. A transfer is those two moves.

If the power fails right after the first move, Alice is down $100 and Bob is up nothing. A hundred dollars has left the universe. Nobody made a mistake — only the books are broken.

Real accounting prevents this with a promise: there is no halfway. Either both moves happen or neither does. The name for that promise in a database is a transaction.

BEGIN;
  UPDATE accounts SET balance = balance - 100 WHERE id = 'A';
  UPDATE accounts SET balance = balance + 100 WHERE id = 'B';
COMMIT;

Everything between BEGIN and COMMIT is one lump. From the outside the world is either "before" or "after", never in between. That guarantee is the single biggest thing a database sells you.

The four letters of ACID

The properties that guarantee has to satisfy are known by the acronym ACID — the phrasing that stuck after Härder and Reuter tidied up the terminology in 1983.

A — Atomicity. "Cannot be cut further." The two moves count as one, and if the second fails, the first is rolled back as well. Nobody outside ever observes the intermediate state.

C — Consistency. The rules you declared still hold afterwards. This is the odd one out, because the database cannot enforce it alone. CHECK constraints and foreign keys are the database's job, but a business rule like "the total across both accounts is unchanged" only holds if whoever wrote the code got it right. C is shared custody with your application.

D — Durability. Once COMMIT returns success, the data survives even if the machine loses power a millisecond later. This is mostly the write-ahead log (WAL): before touching the data pages, append a record of what is about to happen, and force that log to disk. Appending to the end of a log is far cheaper than flushing pages scattered all over the disk. On restart the log is replayed — unfinished work is undone, finished work is redone.

I — Isolation. You never see the half-finished work of transactions running alongside yours. The ideal is that the result looks as if everyone had queued up and run one at a time.

A and D are defenses against things breaking — power cuts, dead processes — and once the mechanism is chosen the discussion ends. I is the hard one. Under contention, when many people touch the same rows at once, isolation is the only one of the four that collides head-on with performance. Serialize everyone and the anomalies drop to zero, but you can also only serve one person at a time. Safety trades directly against speed, so I is never "on or off" — it is a dial labelled how much are you willing to give up.

And what you gave up stays invisible on a normal day. On your laptop you are the only user, so the code is always right and the tests always pass. It surfaces only under load: inventory that doesn't add up, the same seat sold twice, a balance that drifts. The bug that "only happens in production, and only sometimes" usually starts here. The rest of this article is about I.

How concurrency actually breaks things

Dirty read. You read someone else's uncommitted work. If they then ROLLBACK, the value you read never existed at all.

Non-repeatable read. You read the same row twice inside one transaction and get different values, because someone committed in between.

Phantom read. You run the same range query twice and the number of rows changes. It isn't a value that moved — it's the outline of the set.

Lost update. Two people read, compute, and write back. The second write silently erases the first.

# decrementing stock in application code (the dangerous shape)
stock = db.query("SELECT stock FROM items WHERE id=1")            # both read 10
db.execute("UPDATE items SET stock = %s WHERE id=1", stock - 1)   # both write 9
# two units sold, stock says 9. One unit vanished.

Read, think, write back — treat that shape as guilty until proven innocent. The same failure mode exists between ordinary threads in memory; in a database it simply happens across a disk and a network.