The Anatomy of a Database Transaction

"Transactions are ACID" is one of the most confidently wrong sentences in software. ACID is not a mechanism — it's a list of promises. The mechanism that keeps those promises in a real database is a file called the write-ahead log.

What "committed" means, physically

Here is the contract most people think a transaction is:

All of my changes happen, or none of them do.

Here is the contract the database actually guarantees, and it's stronger:

Once I tell you COMMIT has succeeded, my changes will be visible or recoverable — even if the machine loses power in the same millisecond.

The trick is the ordering. The database appends to the log first, and reorders the data pages second:

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

What the database does, from the inside:

1. Buffer pages in memory. The new balances exist only in the buffer pool — fast, but not durable.
2. Append a log record. A single sequential write: "transaction 4242 changed row (1, balance= old→new) and (2, …)". The log is append-only and checksummed, so it can survive torn writes.
3. Reply "committed" — only now. The client's promise is written and durable (fdatasync), even though the data pages haven't been touched.
4. Eventually, write the pages. A background checkpoint flushes dirty pages; the log records are what make it safe to do this lazily.

That ordering — log before page, durable log = committed — is the write-ahead protocol and it is the entire anatomy of durability. If the machine crashes anywhere between step 2 and step 4, recovery replays the log from the last checkpoint and reconstructs exactly the set of committed transactions.

The guarantee is atomic — the mechanism is a group operation

You cannot actually make "two row updates" atomic on disk; disk writes are physical and a crash can interrupt anywhere. Atomicity is emulated with the log:

• A transaction's log records end with a commit record. Recovery replays only transactions whose commit record exists; everything else is rolled back.
• Rollback is possible because each log record carries the before-image ("old balance"), so undo can be constructed by replaying backwards.

Atomicity therefore costs exactly one thing: the log. It is the trick that lets a database batch an entire transaction into "a few sequential log appends," which is why a 1,000-row multi-statement transaction is not 1,000× slower than a single-row one.

Isolation: the part you actually configure

Durability is physics; atomicity is a log file. Isolation is policy — and every database makes you choose it:

level                 guarantees about concurrent transactions
READ UNCOMMITTED      you may see other transactions' uncommitted writes (dirty reads)
READ COMMITTED        only committed data; two reads of the same row can differ (non-repeatable)
REPEATABLE READ       a row read twice looks the same, even if others commit in between
SERIALIZABLE          concurrent transactions behave as if run one after another

The mechanism behind all of them is locking plus multiversion concurrency control (MVCC). Under MVCC, an update doesn't overwrite the old row — it creates a new version, tagged with the transaction ID that made it. Every reader sees the version that was current for its snapshot, so readers never block writers and writers never block readers.

The price of MVCC is invisible space: every UPDATE leaves dead row versions behind, and the background vacuum/compaction process is the janitor that eventually removes them. The "bloat" metric in every PostgreSQL monitoring dashboard is literally the ratio of dead versions to live rows — you are looking at MVCC in the mirror.

A single-node transaction is easy. Don't romanticize it

Every mechanism above is local: one machine, one log, one set of locks. That is why single-node transactions are fast and reliable — the log and the lock manager are one process and one disk away.

Distributed transactions are a different species: now the log is on multiple machines, and "atomicity" requires a protocol (two-phase commit, or agreement-driven approaches) whose failure modes are real — a coordinator crash after telling one node to commit and before telling the other. The engineering decision behind "we can't do transactions across services" is not a limitation; it's the honest price of the log being per-machine.

The runtime view

• COMMIT latency = one log fsync. Tune disks, batch commits, and watch commit appear in slow-query logs.
• Bloat = MVCC's rent. Schedule compaction around traffic shape, not "whenever."
• The isolation level is the strongest guarantee you're paying for — and most applications are running on the weakest one that passes their tests.
• Distributed = the log stops being shared, and everything interesting starts.