Transactions and ACID — DBMS · Engineering Playbook

Summary#

A transaction is a unit of work the DBMS treats as a single logical operation. ACID is the four-letter promise the DBMS makes about that unit: Atomicity (all-or-nothing), Consistency (every transaction takes the database from one valid state to another), Isolation (concurrent transactions don’t observe each other’s intermediate state), Durability (committed work survives crashes). The acronym is from a 1983 Reuter and Härder paper and has held up because each letter names a property you can lose independently — and each loss has a recognisable failure mode in production.

The interesting part is the gap between the letter-perfect definition and what databases actually deliver by default. Most relational DBMSs ship at READ COMMITTED, which is technically a weaker isolation than the I in ACID strictly requires. Most disk durability depends on fsync semantics that have famously betrayed users (the “PostgreSQL fsync bug” of 2018 is the canonical example). ACID is the contract; the implementation has nuance.

Why it matters#

Without transactions, every multi-step write becomes a research project in failure handling. Transfer money from account A to account B: debit A, credit B. If the process crashes between those two writes — and processes always eventually crash between any two writes — A is poorer by $100 and B is no richer. With ACID, the transaction either commits both writes or neither, full stop. The application code is a debit then a credit; the DBMS handles every interleaving and every crash.

In interviews, ACID is a vocabulary check and a starting point. The vocabulary check is “name the four letters and what each means” — table-stakes. The starting point is everything that follows: isolation levels (the I), WAL and replication (the D), constraints and CHECK (the C), and the deeper question of what happens during a partition (where ACID and CAP collide).

How it works#

Atomicity#

All operations in the transaction commit, or none do. Implementation usually rests on the write-ahead log (WAL): before any data page is modified, an entry describing the change is appended to the log on disk. On crash, the recovery process replays committed transactions (redo) and rolls back the rest (undo). The transaction boundary is marked by BEGIN, COMMIT, and ROLLBACK records in the log.

Practical consequence: ROLLBACK is cheap on most systems (it’s just choosing not to apply pending changes), but a long-running transaction holds undo state and locks the whole time. Long transactions are an operational smell.

Consistency#

The transaction takes the database from one valid state to another, where “valid” means every declared constraint holds. The DBMS enforces this via primary keys, foreign keys, NOT NULL, UNIQUE, CHECK constraints, and triggers. If a transaction’s writes would violate any of those, the DBMS aborts the transaction.

Consistency in ACID is not the same as consistency in CAP. ACID-consistency is “constraints are not violated”; CAP-consistency is “all replicas agree on the value”. The letters collide in conversation. Be explicit when you mean which.

Isolation#

Concurrent transactions don’t observe each other’s intermediate state. The strongest form, serializability, requires that the outcome of running the transactions concurrently is equivalent to running them in some sequential order. Most databases offer weaker levels by default for performance:

Level	Prevents
READ UNCOMMITTED	(nothing — rarely used in production)
READ COMMITTED	Dirty reads
REPEATABLE READ	Dirty reads, non-repeatable reads
SNAPSHOT	Dirty reads, non-repeatable reads, phantom reads (with MVCC)
SERIALIZABLE	All of the above plus write skew

PostgreSQL’s REPEATABLE READ is implemented as snapshot isolation. SERIALIZABLE in Postgres uses Serializable Snapshot Isolation (SSI), which is an MVCC variant rather than 2PL. SQL Server’s SERIALIZABLE uses lock-based 2PL. The level name is portable across engines; the implementation cost is not.

Details and anomalies live in Isolation Levels.

Durability#

Committed writes survive crashes. On a single node, durability rests on flushing the WAL to non-volatile storage before acknowledging COMMIT. Across nodes, durability needs replication — usually sync replication to at least one replica before ack.

client: COMMIT
   │
   ▼
WAL write to buffer
   │
   ▼
fsync (flush to disk platters / SSD non-volatile cell)
   │
   ▼ (optional, depending on sync mode)
replicate WAL record to standby; standby fsyncs
   │
   ▼
ack to client

The slowest step here is fsync. On consumer SSDs it can take a few hundred microseconds; on enterprise NVMe with capacitor-backed write buffers, tens of microseconds. Group commit (batching many transactions into one fsync) is how high-throughput systems amortise the cost.

Variants and trade-offs#

Strong ACID, single node (Postgres, MySQL InnoDB default) — full atomicity and durability per node, snapshot isolation by default. Single-shard transactions are cheap; the only knob most apps need to tune is isolation level. The trade-off: write throughput is bounded by one node’s capacity.

Distributed ACID (Spanner, CockroachDB, YugabyteDB) — atomicity and serializable isolation across shards via Paxos / Raft + TrueTime or HLC. Buys horizontal write scale at the cost of cross-shard latency (tens of milliseconds for the consensus round). Worth it when you genuinely need single-shard-equivalent ACID across regions.

Other places ACID bends in real systems:

Eventual-consistency stores (Dynamo-style) — drop the I and weaken the C across replicas; keep atomicity per single-key write. Wins on availability under partition (the AP side of CAP) but pushes consistency reasoning into the application layer.
Sagas instead of distributed transactions — break a logical multi-service operation into a sequence of local ACID transactions, each with a compensating action. No global atomicity, but no 2PC either. The pattern for microservice orchestration where 2PC is impossible.
Async replication trade-off — sync replication preserves the D under failover; async replication offers lower commit latency but loses a small window of transactions if the primary dies before replication catches up. Most production systems run sync to one replica and async to the rest.

The four classic isolation anomalies in one sentence each

Dirty read — you read another transaction’s uncommitted write. Non-repeatable read — you read the same row twice in one transaction and get different values because someone updated it in between. Phantom read — your range query returns different rows on a second read because someone inserted a row matching the predicate. Write skew — two transactions each read the same set, each decide their write doesn’t violate a constraint, but together they do (the classic example: two on-call doctors both go off-shift because each sees the other is still on).

When this is asked in interviews#

Pretty much every database round. The opening is usually “what does ACID mean?” — answer in one sentence per letter, then volunteer the gradations. The follow-up is one of:

“Walk me through how a transaction commits.” — should cover WAL, fsync, optionally replication.
“What’s the default isolation level in your favourite database, and why?” — Postgres is READ COMMITTED, MySQL InnoDB is REPEATABLE READ. Each picks the level that minimises lock contention while preventing dirty reads.
“Give an example of write skew.” — the doctors-on-shift or the two-account-overdraft example.
“How do distributed transactions differ from local ones?” — 2PC, sagas, consensus-based (Spanner), and what each trades off.

System-design loops use the same vocabulary when the candidate proposes a database choice. “You said Postgres for the payments table — does that give you the right ACID guarantees across regions?” is a question that wants the candidate to say no, not without sync replication or a global system like Spanner.