Day 11: ACID vs BASE, Isolation Levels, and MVCC

Phase 4 · Aug 1, 2026

Agenda (2–3 hours)

• Read (45 min): Helland "Life Beyond Distributed Transactions" (2007); Adya et al. "Generalized Isolation Level Definitions" (2000) §2
• Study (45 min): Work through each isolation level's anomalies: dirty read, non-repeatable read, phantom read, write skew
• Practice (45 min): Demonstrate write skew in PostgreSQL at READ COMMITTED; show that SERIALIZABLE prevents it
• Challenge (30 min): DynamoDB doesn't support SQL isolation levels. How would you implement a "select for update"-style lock using DynamoDB conditional writes?

ACID vs BASE

ACID (traditional databases):

• Atomicity: all or nothing
• Consistency: valid state transitions only
• Isolation: concurrent transactions don't interfere
• Durability: committed writes survive crashes

BASE (NoSQL / eventual consistency):

• Basically Available: system is available (even if data is stale)
• Soft state: state may change without input (convergence)
• Eventually consistent: all replicas will converge

These aren't opposites — many systems offer both, tunable per operation.

SQL Isolation Levels

PostgreSQL default: READ COMMITTED. Most anomalies require SERIALIZABLE.

Write Skew

The most subtle anomaly — possible even at REPEATABLE READ:

-- Two doctors both checking "at least one doctor on call"
-- Doctor A:
BEGIN;
SELECT count(*) FROM oncall WHERE shift_date = today; -- returns 2
UPDATE oncall SET doctor_id = NULL WHERE doctor_id = 'alice'; -- go off-call
COMMIT;

-- Doctor B (concurrent):
BEGIN;
SELECT count(*) FROM oncall WHERE shift_date = today; -- returns 2
UPDATE oncall SET doctor_id = NULL WHERE doctor_id = 'bob'; -- go off-call
COMMIT;
-- Result: 0 doctors on call! Neither transaction saw the other's write.

Fix: use SELECT FOR UPDATE or SERIALIZABLE isolation.

Multi-Version Concurrency Control (MVCC)

PostgreSQL (and most modern DBs) use MVCC instead of read locks:

• Each row has a transaction ID range (xmin, xmax) for which it's visible
• Reads see a consistent snapshot from a past transaction ID — no read locks
• Writers create new row versions; old versions are kept for concurrent readers
• VACUUM cleans up old versions that are no longer visible to any transaction

MVCC allows reads and writes to proceed concurrently without blocking each other.

Key Takeaways

• READ COMMITTED is the default in PostgreSQL; sufficient for most cases
• Write skew requires SERIALIZABLE isolation or explicit SELECT FOR UPDATE
• MVCC enables high read concurrency without read locks
• DynamoDB uses conditional writes (ConditionExpression) as the equivalent of pessimistic locking

Tomorrow: distributed locks — coordinating exclusive access across services.