ACID
The four guarantees a transactional database promises: atomicity, consistency, isolation, durability.
What it is
ACID is the contract a single transaction signs with the database. Atomicity means all writes commit or none do; consistency means the transaction leaves the database in a state that satisfies all declared constraints (foreign keys, checks, triggers); isolation means concurrent transactions don't see each other's partial work (the exact strength depends on the isolation level); durability means once the commit returns success, the data survives a crash. Note that the 'C' in ACID is NOT the 'C' in CAP (Consistency, Availability, Partition-tolerance) — ACID consistency is about constraint preservation inside a transaction, CAP consistency is about all nodes seeing the same value.
Why senior interviewers ask
Senior interviewers use ACID to filter candidates who can't distinguish 'consistency' across two famous acronyms. Getting this wrong signals you've memorized buzzwords without understanding the semantics.
Key points
- Atomicity: enforced via write-ahead log (WAL) + rollback; commit is a single durable flip.
- Consistency (ACID flavor): constraints hold before and after; the DB will reject a commit that violates them.
- Isolation: governed by the isolation level — read committed, repeatable read, snapshot, serializable.
- Durability: fsync on commit (or group commit) — without it, durability is a lie even if the docs say otherwise.
- Most engines achieve I via MVCC (multi-version concurrency control), not locks.
- Distributed ACID across nodes requires consensus (Raft, Paxos) or 2PC (Two-Phase Commit) — both expensive.
- ACID consistency != CAP consistency != cache consistency — qualify the word before using it.
Real systems
Interview probe
'What does the C in ACID mean, and how is it different from the C in CAP?' Answer: ACID-C is constraint preservation within a transaction (the DB rejects commits that break FKs or checks); CAP-C is linearizability across replicas. Different acronyms, different concerns.
Dive deeper
Atomicity and durability are typically implemented by the same mechanism: a write-ahead log that's fsync'd on commit. Replay the log on crash and you recover both. Isolation is the expensive one — true serializable isolation requires either pessimistic locking (which kills concurrency) or optimistic concurrency control with retry on conflict (which kills throughput under contention). This is why every production database defaults to a weaker isolation level than serializable and makes you opt in.