ACID, Demystified: What Each Letter Actually Costs

Almost every engineer can recite ACID. Far fewer can tell you what each letter costs on a production database under load. Let's fix that.

A — Atomicity

A transaction either fully commits or fully aborts. No half-way. The database makes this work via a write-ahead log (WAL): every change is appended to the log before it touches the page. On crash recovery, committed transactions are rolled forward, uncommitted ones are rolled back.

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

Cost: every write is effectively written twice (WAL + page). Good databases amortise this with group commit.

C — Consistency

Constraints (FKs, CHECKs, triggers) hold true before and after the transaction. The confusing part: the database enforces the constraints you wrote. It does not guarantee your business logic is correct — that's on you.

I — Isolation

This is the interesting one. The SQL standard defines four levels:

• READ UNCOMMITTED — allows dirty reads. Almost never used.
• READ COMMITTED — default in Postgres/Oracle. Prevents dirty reads but allows non-repeatable reads.
• REPEATABLE READ — default in MySQL. Same row reads the same value twice; phantom rows still possible (in standard SQL — Postgres actually prevents phantoms at this level too).
• SERIALIZABLE — as if transactions ran one at a time. Slowest; may force retries.

Most apps run on READ COMMITTED and handle the edge cases with SELECT … FOR UPDATE where it matters. Bumping the whole database to SERIALIZABLE "for safety" is a classic premature generalization.

D — Durability

Once COMMIT returns, the change survives a crash. Implementation: fsync the WAL before acknowledging. This is what makes commits feel slow. It's also where "acks=all"-style settings live in distributed databases.

The hidden cost of each letter

• Atomicity → write amplification (WAL).
• Isolation → locks, MVCC versions, longer transactions.
• Durability → disk latency on commit.

When people "go NoSQL for performance," what they're really doing is picking and choosing among these. That's fine, as long as you're explicit about what you just gave up.