Transactions & ACID: The Correctness Guarantee

We can now shape, index, and join data. But real operations rarely touch one row. Transferring money debits one account and credits another; placing an order writes an order row and decrements stock. If half of that happens, your data lies. The transaction — and the four guarantees summarized as ACID — is the database's answer, and the deepest reason relational systems dominate anywhere correctness matters.

The problem: partial updates corrupt reality

Consider a transfer of $100 from account A to account B — two writes:

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

If the process crashes between these two statements, $100 has vanished: debited from A, never credited to B. No amount of careful application code fully closes this gap on its own — the crash can happen at any instant. You need the database to treat both writes as one indivisible unit.

The transaction: one unit of work

A transaction wraps operations so they all commit or all roll back:

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

Until COMMIT, nothing is permanent and no other transaction sees the half-finished state. If anything fails — an error, a constraint violation, a crash — the database performs a ROLLBACK, erasing every change as if the transaction never began.

ACID, one letter at a time

ACID names the four properties a proper transaction guarantees. They're worth understanding individually, not as a slogan.

A — Atomicity. All operations happen or none do. There is no "half a transaction." This is what saves the $100 transfer: a crash after the debit but before the credit rolls the debit back on recovery.

C — Consistency. Every transaction moves the database from one valid state to another, respecting all constraints — foreign keys, uniqueness, checks. A transaction that would leave an invalid state (a negative balance under a CHECK (balance >= 0)) is rejected outright. The database's invariants are never violated, not even briefly.

I — Isolation. Concurrent transactions don't step on each other; each runs as if it were alone, even when hundreds execute at once. This one has degrees — how strictly the database enforces it is a genuine tradeoff, important enough that the next chapter is devoted entirely to it.

D — Durability. Once COMMIT returns, the changes are permanent — they survive a crash or power loss the instant later. The database does not report success until the data is safely recorded.

How durability actually works: the write-ahead log

Durability sounds like it should be slow — surely writing every change straight to its final place on disk before confirming would crawl? The trick is the write-ahead log (WAL). Before a change touches the main data files, the database appends a compact record of it to a sequential log and flushes that to disk. Sequential appends are fast, and the log is enough to reconstruct or finish the change after a crash.

On restart after a crash, the database replays the WAL: committed transactions are reapplied, incomplete ones are discarded. That's how you get both speed and the promise that "committed" means permanent.

Why this is the whole game

Strong transactional guarantees are the single biggest reason relational databases own the domains where being wrong is unacceptable — banking, payments, inventory, ticketing, orders. You can build correctness on top of a system with weaker guarantees, but you'll re-implement pieces of ACID by hand, usually with bugs. When your data has invariants that must never break, a database that enforces ACID for you is not a luxury; it's the foundation.

Recap

• A transaction bundles operations so they all commit or all roll back — no partial state is ever visible.
• Atomicity — all-or-nothing; a crash mid-transaction is undone on recovery.
• Consistency — every transaction respects all constraints, moving from one valid state to another.
• Isolation — concurrent transactions don't interfere; this one has degrees (next chapter).
• Durability — once committed, changes survive crashes, made fast by the write-ahead log.
• ACID is why relational databases dominate anywhere correctness matters.

Isolation is the ACID property with a dial — and turning it has real consequences. Continue to Isolation Levels & Locking.