Day 9 — ACID vs BASE, Isolation Levels

Time budget: ~60 min · Level: This is where many candidates fake it. Nail the specifics and you separate cleanly from the pack.

Every serious system design question eventually asks: "how do you keep the data correct under concurrent access?" The answer starts with understanding transactions and their guarantees. Most engineers can spell ACID; very few can articulate the trade-offs between Read Committed and Repeatable Read on a whiteboard.

Today's goal: make you one of the few.

ACID — spell it out precisely

A — Atomicity: all statements in a transaction either commit or all roll back. No partial state visible. C — Consistency: the DB moves from one valid state to another, respecting all constraints (unique, foreign key, check, custom triggers). This is the least-important letter — it's really a property of the app + DB together, not just the DB. I — Isolation: concurrent transactions appear as if they ran serially. The strength of "appear as if" is what isolation levels control. D — Durability: once committed, the write survives crashes, power loss, and (with replication) most disasters.

The "C" trap

"Consistency" in ACID ≠ "Consistency" in CAP. - ACID C: database integrity constraints hold after every transaction (FK, unique, custom rules). - CAP C: in a distributed system, all nodes see the same data at the same time (linearizability).

Confuse them in an interview and you'll be corrected. These are entirely different concepts that unfortunately share a letter.

What ACID doesn't give you

• Cross-database transactions. ACID is per-DB. Coordinating a transaction across a DB and a message queue requires patterns like Outbox or Saga (Day 20).
• Business correctness. "Don't sell 10 tickets to 8 seats" isn't automatic; you need the right isolation level or explicit locking.
• Cross-service atomicity. Microservices call each other; ACID stops at the DB boundary. This is where sagas live.
• Free performance. Higher isolation = more locking / more work = more latency.

The 4 ANSI SQL isolation levels

Ranked from weakest to strongest. Each prevents specific anomalies.

The anomalies, with concrete examples

Dirty Read — you read data another transaction wrote but hasn't committed yet. If they roll back, you saw data that never existed.

T1: UPDATE balance SET amount = 500 WHERE id = 1;   -- (not committed)
T2: SELECT amount FROM balance WHERE id = 1;         -- reads 500  ← dirty
T1: ROLLBACK;                                        -- T2's read was of a value that "never happened"

Almost no production DB allows this. Postgres and MySQL default to Read Committed or stronger.

Non-Repeatable Read — you read the same row twice in one transaction and get different values because another transaction committed in between.

T1: SELECT amount FROM balance WHERE id = 1;   -- reads 100
T2: UPDATE balance SET amount = 500 WHERE id = 1; COMMIT;
T1: SELECT amount FROM balance WHERE id = 1;   -- reads 500  ← non-repeatable

Prevented by Repeatable Read and above.

Phantom Read — you run the same query (e.g., WHERE status = 'active') twice and get different row counts because another transaction inserted matching rows.

T1: SELECT COUNT(*) FROM orders WHERE customer_id = 42;   -- 3
T2: INSERT INTO orders (customer_id, ...) VALUES (42, ...); COMMIT;
T1: SELECT COUNT(*) FROM orders WHERE customer_id = 42;   -- 4  ← phantom

Prevented (in theory) by Serializable. In practice, Postgres's Repeatable Read (Snapshot Isolation) already prevents this.

Lost Update — two transactions read a value, each modify it, each write; the last write wins and the first update is lost.

T1: SELECT amount → 100;         T2: SELECT amount → 100;
T1: amount = 100 + 10 = 110;     T2: amount = 100 + 20 = 120;
T1: UPDATE amount = 110;          T2: UPDATE amount = 120;   ← T1's +10 is lost

Prevented by Repeatable Read (in MVCC) or by explicit locking (SELECT ... FOR UPDATE).

Write Skew — two transactions read overlapping data, make disjoint writes, and violate a global constraint that both were separately checking.

Rule: at least one doctor must be on-call.

T1: SELECT COUNT(*) FROM oncall WHERE on_shift = true;   -- 2 doctors, OK to go off
T2: SELECT COUNT(*) FROM oncall WHERE on_shift = true;   -- 2 doctors, OK to go off
T1: UPDATE oncall SET on_shift = false WHERE doctor = 'Alice';  -- 1 left
T2: UPDATE oncall SET on_shift = false WHERE doctor = 'Bob';    -- 0 left ← violation
COMMIT both.

Only Serializable prevents write skew. This is one of the most subtle and dangerous anomalies — it's also the reason "SNAPSHOT ISOLATION IS NOT SERIALIZABLE," despite reading like it should be.

What Postgres, MySQL, Oracle actually do

The ANSI spec is a minimum. Real DBs often prevent more anomalies than the level "requires" — and each DB implements the levels differently.

Practical takeaway: - Postgres Repeatable Read gives you Snapshot Isolation — no dirty read, no non-repeatable read, no phantom read for most queries, but still allows write skew. - Postgres Serializable uses SSI (Serializable Snapshot Isolation) — it detects write skew via dependency tracking and aborts one transaction. First-class implementation, still fast for most workloads.

MVCC vs 2PL — how isolation is implemented

Two dominant strategies:

Multi-Version Concurrency Control (MVCC)

• Every write creates a new version of the row, tagged with a transaction timestamp/ID.
• Readers see the version consistent with when their transaction started.
• No reader blocks a writer. No writer blocks a reader.
• Used by Postgres, MySQL/InnoDB, Oracle, SQL Server (snapshot mode), Cassandra.
• Cost: bloat — old versions accumulate until vacuum/compaction reclaims them.

Two-Phase Locking (2PL)

• Transactions acquire locks (shared for reads, exclusive for writes).
• Locks are held until transaction commit.
• Readers can block writers and vice versa.
• Used by SQL Server (default), DB2, older systems.
• Cost: contention and deadlocks.

MVCC's win: reads never block writes and vice versa, which is huge for read-heavy workloads. Its trade-off is bloat maintenance (Postgres VACUUM, MySQL undo log purging).

Serializable — the "correct" choice and its cost

Serializable is what you conceptually want: transactions execute as if one at a time.

Three ways to achieve it: 1. Strict 2PL — hold locks until commit. High contention, deadlocks. (Old-school.) 2. Actual serialization — one thread runs all transactions. Redis is essentially this. Fast for tiny transactions, doesn't scale to complex ones. 3. Serializable Snapshot Isolation (SSI) — MVCC + track read/write dependencies + abort one transaction if a cycle forms. Postgres's implementation. Very good in practice.

When to actually use Serializable: - Financial ledgers, inventory reservations, ticket booking — anywhere business correctness demands it. - Any place where write skew is possible and dangerous.

When to explicitly NOT use Serializable: - Read-heavy analytical dashboards — you're paying overhead for nothing. - Simple KV lookups. - Idempotent reads where staleness is fine.

Rule of thumb: most Postgres apps run at Read Committed, escalate to Serializable only for critical paths.

Explicit locking — when isolation isn't enough

Sometimes you need direct control:

• SELECT ... FOR UPDATE — acquires an exclusive row lock. Held until commit. Blocks other writers of the same row. Common for "reserve seat X" logic.
• SELECT ... FOR SHARE — shared lock, blocks writers but not readers.
• Advisory locks — Postgres-specific, application-defined locks that aren't tied to any row. Great for "only one job runs this migration."
• Optimistic locking — no DB lock, but store a version number and update WHERE version = X. If update affects 0 rows, retry. Great for low-contention scenarios.

Long transactions — the silent killer

A transaction that stays open too long causes: - Lock hold times grow — other txns wait. - MVCC bloat — Postgres can't vacuum row versions newer than the oldest active transaction's snapshot. - Replication lag — replicas can't drop old row versions either. - Connection exhaustion — the connection is tied up.

Practical rules: - No transaction should be open longer than a few seconds. - Never wrap a transaction around a network call to another service. - If you need "get this data, do work, save result," fetch first (no txn), then open a short txn to save. - Monitor pg_stat_activity for transactions in idle in transaction state — those are bugs waiting to explode.

BASE — the other paradigm

B — Basically Available: the system responds to every request (though maybe with stale or partial data). S — Soft state: state may change over time even without new writes (background convergence). E — Eventual consistency: given no new writes, all replicas will eventually agree.

BASE is not the opposite of ACID — it's what many NoSQL systems offer when they prioritize availability and partition tolerance over strong consistency. Cassandra, DynamoDB (default), MongoDB (secondary reads), Riak all lean this way.

When BASE is fine

• Social media feeds (a 200ms stale like count is fine)
• Product recommendations
• Analytics dashboards
• Comment counts

When BASE will burn you

• Anything with money
• Anything with limited inventory
• Anything with a "did this action succeed?" affirmative confirmation
• Anything where duplicate execution causes harm (double-charge, double-send)

Real systems use both. User accounts + payments in ACID (Postgres); news feed + likes in BASE (Cassandra + eventual consistency). Match the paradigm to the domain, not the entire company.

NoSQL and transactions — quick reference

Key insight: "NoSQL doesn't do transactions" is outdated. But NoSQL transactions have caveats (single partition, or slow, or with restrictions). Always check the specific store.

Common isolation-related bugs (interview traps)

1. Double-charge / double-order. Two API requests hit at the same time, both check "does this order exist?", both create it. Fix: unique constraint + INSERT ... ON CONFLICT, OR idempotency key.

2. Overselling inventory. Two requests both read stock = 1, both decrement, both succeed. Fix: UPDATE stock SET count = count - 1 WHERE count > 0 (atomic), or SELECT FOR UPDATE.

3. Lost user update. UI shows old value, user submits new value, UPDATE ... SET x = 'new' overwrites another user's concurrent update. Fix: optimistic locking with version column.

4. Wrong answer in a "check-and-act" transaction. "If balance > 0, subtract 100." At Read Committed, another transaction can change balance between the SELECT and UPDATE. Fix: do it in one atomic statement or use Serializable.

5. Deadlocks under load. Two transactions grab locks in different orders. Fix: consistent lock ordering across code paths; monitor and retry deadlock victims.

Interview angles

"Design an inventory reservation service for an e-commerce site during a flash sale."

Answer skeleton: 1. Correctness requirement: no overselling. This is table stakes. 2. Approach A — Serializable transaction with SELECT ... FOR UPDATE on the SKU row. Correct, but contention on hot SKUs kills throughput. 3. Approach B — atomic decrement in one statement: UPDATE inventory SET stock = stock - 1 WHERE sku = X AND stock > 0 RETURNING id. If 0 rows affected, out of stock. Much faster. 4. Approach C — pre-reserve stock via Redis with DECR; commit to Postgres asynchronously. Trade-off: Redis is source of truth for stock during the flash sale. 5. Capacity math: 100k requests/s flash sale on 10k SKUs. Postgres FOR UPDATE on 10k rows → contention nightmare. Redis DECR → 1M ops/s, trivial. 6. Post-flash reconciliation: reconcile Redis counts to Postgres.

"You see a bug where users report their comments were sometimes deleted after posting. What went wrong?" - Almost certainly a lost-update or last-writer-wins race condition. - Debug: check isolation level, check whether the code is doing read-modify-write outside a transaction, check for optimistic locking absence.

Spaced-repetition callback in today's quiz

• DB selection by access pattern (from Day 8) — reappears in Q8 (transaction design for a specific workload).
• Post-mortem structure you've now consistently nailed (Days 3/5/6/7/8 Q9) — Q9 today extends this pattern to a subtle isolation-level bug.
• Capacity math (though the gap is resolved) — Q8 asks for numbers on lock contention. Keep the habit.

Also new: this is your first "deep-technical-vocabulary" test — anomalies have precise names (dirty read, non-repeatable read, phantom, write skew, lost update). Using the right name in an interview is a Strong Hire signal.