ACID & Database Transactions
What a Transaction Is
A transaction is a group of reads/writes treated as one indivisible unit. ACID is the four guarantees a transactional database (Postgres, MySQL/InnoDB, Oracle) makes about that unit. This is the vocabulary behind "SQL gives you atomic multi-row writes" in [[SQL vs NoSQL]] — this page is what that sentence actually means.
ACID — the Four Properties
| Property | Promise | Concrete failure it prevents |
|---|---|---|
| Atomicity | All ops commit, or none do — no partial writes. | Transfer: debit A succeeds, credit B crashes → the debit is rolled back. Money never half-moves. |
| Consistency | Every commit leaves the DB in a valid state — all constraints/FKs/triggers hold. | An orders row can't reference a user_id that doesn't exist (FK enforced at commit). |
| Isolation | Concurrent transactions don't see each other's uncommitted state. Result is as-if they ran serially. | Two buyers grab the last seat at once → isolation stops both from succeeding. |
| Durability | Once committed, survives crash/power loss. | Commit returns OK, power dies 1ms later → data is still there on restart. |
Trap — ACID's "C" ≠ CAP's "C". ACID-Consistency = this transaction preserves the invariants you declared (app-defined validity). CAP-Consistency (see [[CAP Theorem & Consistency Models]]) = linearizability across replicas — every read sees the latest write globally. Same word, unrelated concepts. Interviewers probe this deliberately.
How it's actually done (one-liner each):
- Atomicity + Durability → Write-Ahead Log (WAL): write the intent to an append-only log before touching data pages; on crash, replay/undo from the log.
- Isolation → locking (pessimistic) or MVCC (Postgres keeps multiple row versions so readers never block writers).
Isolation Levels — the Knob Most People Miss
Isolation isn't on/off. SQL defines 4 levels; weaker levels allow more concurrency but permit read anomalies:
| Anomaly | What happens |
|---|---|
| Dirty read | T1 reads a row T2 wrote but hasn't committed; T2 rolls back → T1 read garbage. |
| Non-repeatable read | T1 reads a row twice; T2 commits an update in between → two different values. |
| Phantom read | T1 runs the same WHERE twice; T2 inserts a matching row in between → rows "appear." |
| Level | Dirty | Non-repeatable | Phantom |
|---|---|---|---|
| Read Uncommitted | ✅ | ✅ | ✅ |
| Read Committed (Postgres default) | ❌ | ✅ | ✅ |
| Repeatable Read (InnoDB default) | ❌ | ❌ | ✅* |
| Serializable | ❌ | ❌ | ❌ |
| * InnoDB blocks most phantoms via next-key locking; the strict SQL spec allows them at this level. |
The move: "Default Read Committed. For a read-then-write on a contended row (inventory decrement, balance check) I take a row lock with
SELECT ... FOR UPDATE, or raise that one transaction to Serializable — not the whole DB, since Serializable throttles throughput."
Concurrency Control — Optimistic vs Pessimistic
Two ways to stop the "two buyers, one seat" race:
- Pessimistic — lock first, work, release.
SELECT ... FOR UPDATE. Safe; serializes contenders → throughput drops under contention; risk of deadlock (two txns lock in opposite order). - Optimistic (OCC) — no lock. Read a
version, work, thenUPDATE ... WHERE version = <read>. 0 rows updated = someone else won → retry. Great when conflicts are rare; wasteful when contention is high.
-- Optimistic: succeeds only if nobody changed the row since we read version 7
UPDATE seats SET taken = true, version = 8
WHERE id = 42 AND version = 7; -- rows affected = 0 → lost the race → retry
Rule: low contention → optimistic; high contention → pessimistic. Name which and why.
When You DON'T Get ACID
- Across shards / services — a transfer spanning two shards or two microservices can't use one local transaction (see [[Database Replication & Sharding]]). Options: 2PC (correct but blocks if the coordinator dies) or Saga (local steps + compensating undo actions, eventual consistency, no global lock).
- Most NoSQL by default — key-value/column stores trade ACID for scale and availability, giving eventual consistency instead (see [[CAP Theorem & Consistency Models]]). Many now offer single-item atomicity or opt-in transactions, but not free cross-partition ACID.
Interview Drills
- Difference between ACID's C and CAP's C? — ACID-C: one transaction preserves declared invariants on one DB. CAP-C: linearizability across replicas (every read sees the latest write globally). Unrelated despite the shared letter.
- Payment service — how do you prevent a double charge on retry? — ACID transaction with
SELECT ... FOR UPDATEon the balance row (pessimistic, since money rows are contended and must be exact) + an idempotency key so a client retry returns the original result instead of charging twice. - Why is Serializable slow, and what do you use instead? — It forces transactions to behave as if run one-at-a-time (heavy locking / serialization failures under load). Instead: default Read Committed, and escalate to row locks or Serializable only on the specific transactions that read-then-write contended data.
- You need an atomic transfer between accounts on two different shards. What breaks and what do you do? — A single local ACID transaction can't span shards. Either co-locate both accounts on one shard via the shard key, or accept a Saga: debit + credit as separate local transactions with a compensating refund if the second fails.
Cross-links
[[SQL vs NoSQL]] · [[CAP Theorem & Consistency Models]] · [[Database Replication & Sharding]] · [[Distributed Key-Value Store]] · [[Caching & Redis]]