ACID Transactions

A transaction is a group of one or more operations that a database executes as a single unit: either every operation in the group succeeds, or none of them do. ACID is the acronym for the four guarantees a database makes about how that unit behaves — Atomicity, Consistency, Isolation, Durability — and it's the vocabulary that lets you say precisely what a database promises instead of vaguely gesturing at "it's reliable."

The four letters

• Atomicity — a transaction is all-or-nothing. If a bank transfer debits one account and credits another, and the process crashes after the debit but before the credit, atomicity guarantees the whole transaction rolls back — the debit never happened either, as far as anyone can tell.
• Consistency — a transaction takes the database from one valid state to another, never leaving it in a state that violates its own rules (constraints, foreign keys, uniqueness). This is a narrower, database-specific idea than the "C" in the CAP Theorem — worth being explicit about the distinction in an interview, since conflating the two is a common and easy-to-catch mistake.
• Isolation — concurrent transactions don't see each other's uncommitted, in-progress changes. Without isolation, one transaction reading data mid-write by another could see a state that never actually existed as a stable snapshot.
• Durability — once a transaction commits, it survives a crash. The database has to have actually persisted it (typically to a write-ahead log flushed to disk) before it tells the client "done."

System Design Lab

Isolation levels: a tunable tradeoff

Full isolation — behaving as if every transaction ran one at a time, with no overlap at all — is expensive, because it means transactions frequently have to wait on each other. Real databases offer several isolation levels, each allowing a specific, well-defined category of anomaly in exchange for more concurrency:

Level	Allows	Typical use
Read Uncommitted	Dirty reads (seeing another transaction's uncommitted writes)	Rarely used in practice
Read Committed	Non-repeatable reads (same query, different results, within one transaction)	Common default (e.g. PostgreSQL)
Repeatable Read	Phantom reads (new rows matching a query appear on re-read)	Common stricter default (e.g. MySQL/InnoDB)
Serializable	Nothing — behaves as if transactions ran one at a time	Financial and other correctness-critical workloads

The general rule: stricter isolation gives stronger correctness guarantees and costs more throughput, because more transactions end up blocking or retrying instead of running concurrently. Naming which isolation level a design assumes — and why the workload can tolerate what that level allows — is a much stronger answer than saying "the database handles it."

ACID vs. the CAP theorem

It's worth connecting this explicitly to CAP Theorem: ACID transactions are naturally easiest to guarantee on a single node, or across nodes that are strongly consistent — which pushes a system toward the CP side of CAP. Once data is sharded or replicated across multiple machines, guaranteeing full ACID semantics across those machines requires expensive coordination (distributed transactions, two-phase commit), which is exactly why many horizontally-scaled and NoSQL systems relax one or more of these guarantees — see SQL vs. NoSQL for how that tradeoff plays out in practice.

Why this matters in an interview

Any design involving money, inventory, or anything where "partially applied" is unacceptable should explicitly invoke ACID transactions and name the isolation level assumed. It's also a good moment to flag the tradeoff honestly: a design that needs strict ACID guarantees across a sharded, horizontally-scaled system is signing up for real complexity and latency cost — recognizing that tension is more valuable than reciting the acronym.

Strict isolation (Serializable) vs. relaxed isolation: pros and cons

Pros

• ✓Eliminates dirty reads, non-repeatable reads, and phantom reads entirely
• ✓Application code doesn't need to defensively handle concurrency anomalies
• ✓Correctness is guaranteed by the database, not by careful application logic

Cons

• ✕Lower throughput — more transactions block or need to retry
• ✕Higher latency under contention, since concurrent access to the same rows serializes
• ✕Often unnecessary overhead for workloads that can tolerate a weaker, cheaper isolation level

Further Reading

• PostgreSQL Docs — Transaction Isolation — a precise, implementation-grounded explanation of isolation levels and the anomalies each one permits.
• Jepsen — Consistency Models — a rigorous reference distinguishing the many related-but-different meanings of "consistency" across databases.