30-Second Glance
- ACID = Atomicity, Consistency, Isolation, Durability: the contract that multi-statement/multi-object updates behave like one indivisible step that survives crashes.
- Atomicity needs undo (abort) — including for crashed clients, not just errors; durability needs fsync'd redo, not just a write() call.
- "Consistency" in ACID means app-level invariants (balances ≥ 0), not replica convergence — the app + DB share the job.
- Isolation is the spectrum (read committed → snapshot → serializable) covered by the next notes; single-object writes are already atomic in most stores.
The Four Properties, Precisely
Atomicity — all-or-nothing across multiple writes, even across retries and crashes. Transfer $100 from A to B = debit(A) + credit(B) as one unit; a crash between them must leave neither visible. Implementation: undo log / shadow pages + abort path; the client holding the transaction may itself crash, so the DB needs timeout-based abort, not just explicit ROLLBACK.
Consistency (DDIA sense) — the database starts from a valid state and the transaction leaves it valid assuming the app's logic is correct. Foreign keys, unique constraints, and CHECKs are the DB's share; "account never negative" with concurrent debits is the app's share plus the isolation level's share. Do not confuse with replica consistency (linearizability, Linearizability) or Quorums — different chapter, different guarantees.
Isolation — concurrent transactions behave as if serialized, to a degree chosen by the isolation level. Read committed blocks dirty reads/writes only; snapshot isolation adds consistent snapshots but permits write skew; serializable (via 2PL, SSI, or true serial execution) closes the remaining anomalies. Each level is a full note: Read Committed Isolation, Snapshot Isolation, Write Skew and Phantom Writes, Serial Execution and Achieving ACID.
Durability — once COMMIT returns, the data survives power loss, crash, and (with replication) disk/node loss. Requires the redo/WAL record to reach durable media (fsync / group commit / quorum ack), not merely the page cache. "fsync per commit ≈ 1–10K TPS; group commit / async ≈ 100K+ with a loss window" is the number to quote.
Single-Object vs Multi-Object
Single-key atomicity is nearly free and nearly universal: Dynamo-style stores, Cassandra lightweight transactions aside, give you atomic single-row writes via LSM MemTable + WAL or B-Tree latch + WAL. The hard part — and the reason transactions exist — is multi-object: debit + credit, booking + payment, index + row, partition A + partition B. That needs:
- Grouping (BEGIN/COMMIT framing),
- Abortability (undo path + deadlock/timeout resolution),
- Crash recovery (redo committed, undo uncommitted at restart),
- Cross-partition coordination (2PC or single-shard routing) when data spans nodes — see Partitioning and the 2PC note.
NoSQL systems that "lack transactions" almost always mean multi-object transactions; single-row atomicity plus idempotent retries covers a surprising number of HLD designs (and is exactly what Stream Processing exactly-once debates hinge on).
Abort, Retry, and the Client's Share
Anything can abort: constraint violation, deadlock, serialization failure (SSI), or client crash. Correct client behavior is: catch abort → exponential backoff with jitter → retry the whole transaction (not the failed statement), keeping the body side-effect-free or idempotent. Classic bug: sending the confirmation email inside the transaction body, then retrying — the email sends twice. Do external effects after commit, or gate them on idempotency keys.
Trade-offs
| Approach | Pros | Cons | Use when |
|---|---|---|---|
| Full ACID (single-node RDBMS) | Strongest reasoning; constraints enforced; simplest app code | Throughput ceiling; cross-shard needs 2PC; latency of fsync/quorum | Money, inventory, bookings — invariants matter |
| ACID-lite (snapshot isolation default) | Good concurrency + no dirty reads; low abort rates | Write skew/phantoms possible; app must handle edge cases | Postgres/MySQL defaults for general OLTP |
| BASE / single-key atomic + sagas | Scales horizontally; available under partition | App owns rollback/compensation; eventual consistency | High-scale flows where partial progress + compensation is acceptable |
| True serial execution (VoltDB/Redis-style) | Simplest correctness story, no anomalies | Partition-bound throughput; stored-procedure discipline | Partitionable workloads needing serializability cheaply |
Interview One-Liners
- Q: Define ACID without hand-waving? A: Atomic multi-write all-or-nothing incl. crash-abort; app invariants preserved; concurrent txns isolated per chosen level; committed data survives crashes via fsync'd redo.
- Q: ACID "C" vs replica "consistency"? A: ACID-C is app invariants (no negative balances); replica consistency is convergence/ordering of copies (linearizability/quorums) — orthogonal guarantees.
- Q: What breaks if durability is just write() without fsync? A: An OS crash loses "committed" data sitting in the page cache — COMMIT lied. Durability requires the WAL on stable media or a quorum ack.
- Q: Why must clients retry whole transactions, not statements? A: Aborts (deadlock, SSI, constraint) invalidate the transaction's snapshot/predicate set; resuming mid-transaction replays decisions made on stale state.
- Q: Do NoSQL stores have transactions? A: Single-key atomicity: yes, almost always. Multi-object ACID: only some (FoundationDB, Spanner, MongoDB multi-doc since 4.0) — otherwise use idempotency + sagas.
- Q: Where do cross-shard transactions hurt? A: Coordination (2PC) adds latency + blocking on coordinator failure; prefer single-shard routing by entity key, else accept saga compensation.