Transactions & ACID

A transaction bundles several reads and writes into one unit of work that either fully happens or has no effect at all. It is the database's answer to a simple but brutal question: what happens if the power fails, or two users touch the same row, halfway through a multi-step change? The four guarantees that answer it are ACID — Atomicity, Consistency, Isolation, Durability.

The classic example — a bank transfer

Moving $100 from account A to account B is really *two* writes: debit A, then credit B. If the system crashes between them, A has lost $100 that never reached B. Wrapping both in one transaction makes that impossible.

BEGIN;
  UPDATE accounts SET balance = balance - 100 WHERE id = 'A';
  UPDATE accounts SET balance = balance + 100 WHERE id = 'B';
COMMIT;   -- both writes become permanent together

What each ACID letter promises

1. 1Atomicity — all or nothing. If the credit to B fails, the debit from A is rolled back too. The transfer never leaves money in limbo.
2. 2Consistency — the transaction moves the database from one valid state to another, respecting every rule (here: total money across accounts is unchanged).
3. 3Isolation — concurrent transactions don't see each other's half-finished work; the result is as if they ran one at a time.
4. 4Durability — once COMMIT returns, the change survives any later crash, because it was recorded to permanent storage first.

Isolation without blocking — MVCC

To let many transactions run at once, modern databases use MVCC (Multi-Version Concurrency Control): a writer creates a *new version* of a row while readers keep seeing the version their snapshot froze — so reads never block writes and writes never block reads. The isolation level decides which version a re-read sees: under Read Committed each read sees the latest committed value, while under Repeatable Read every read sees the snapshot taken when the transaction began.