Foundations · Phase 1: Foundations
ACID vs. BASE & The Consistency Spectrum
Deconstruct database transaction guarantees from Serializable isolation down to Eventual consistency, exploring snapshot isolation, dirty reads, and write skew.
Primary specification: A Critique of ANSI SQL Isolation Levels (Berenson et al.)
Lesson 6 of 33
1d streakThe Architectural Question
Teams assume Read Committed isolation prevents race conditions, resulting in silent inventory overselling and corrupted account balances.
Core Engineering Objectives
- ACID transaction boundaries: Atomicity, Consistency invariants, Isolation levels, Durability
- ANSI SQL isolation anomalies: Dirty Read, Non-Repeatable Read, Phantom Read, Write Skew
- BASE model: Basically Available, Soft state, Eventual consistency with causal ordering
1. What is it & Why it Matters
ACID and BASE represent the two foundational approaches to data management: - ACID (Pessimistic & Centralized): Guarantees strict transactional correctness. If any part of an update fails, the entire transaction is rolled back. - BASE (Optimistic & Distributed): Embraces network latency and partial failure by trading immediate consistency for continuous availability and low latency.
The Core Architectural Trade-Off
2. ACID Demystified
Atomicity
All or nothing. Either all database operations in the transaction succeed, or all changes are rolled back.
Consistency
The database moves only between valid states that satisfy all declared schema constraints and business invariants.
Isolation
Concurrent transactions do not observe each other’s intermediate, uncommitted updates (enforced via row locks or MVCC).
Durability
Once a transaction commits, its data survives crashes and power cuts (flushed to Write-Ahead Log on persistent disk).
3. BASE & The Consistency Spectrum
Basically Available
The system guarantees availability for all requests, even if nodes must return degraded or slightly stale data during partial outages.
Soft State
Data values may change over time without explicit user writes as replicas asynchronously catch up.
Eventual Consistency
Given sufficient time with no new updates, all replicas converge to identical data.
Architectural Boundary · Do Not Confuse
ACID (Relational - Postgres, MySQL)
BASE (NoSQL - Cassandra, DynamoDB)
ACID describes transaction properties, while BASE is an informal description of systems that may expose temporary inconsistency. Neither label alone determines scalability or partition behavior; the actual transaction, replication, and consistency model does.
4. Decision Guide & Consistency Cheat Sheet
| Consistency Level | What It Guarantees | Latency / Cost | Real-World Example |
|---|---|---|---|
| Strict Serializability / Linearizability | Every read sees the latest global write instantaneously | High (multi-node consensus / locks) | Bank ledgers, distributed locks (etcd) |
| Causal Consistency | Operations causally related are seen in the same order everywhere | Moderate (tracked via vector clocks) | Comment threads, collaborative doc editing |
| Read-Your-Writes | A user always observes updates submitted by their own session | Low (pin user reads to primary replica for 5s) | User profile updates, account settings |
| Eventual Consistency | Replicas synchronize asynchronously in the background | Lowest (local reads/writes without locks) | DNS propagation, video view counts, social feeds |
Use it when
Choose ACID when modeling financial balances, seat reservations, or authentication state where reading stale data causes fraud or business loss.
Reconsider when
Choose BASE when building high-velocity telemetry, chat presence, notification streams, or social feeds where 100% write availability outweighs microsecond consistency.
5. Active Recall Checkpoint
Active Recall Checkpoint
What is "Read-Your-Writes" consistency and how is it typically implemented in a primary-replica database setup?
Hint: Think about routing read queries for a short window after a user performs a write.
Show answer & explanation
Read-Your-Writes guarantees that a user will immediately observe their own updates, even if other users see eventual consistency. In a primary-replica architecture, it is implemented by routing reads to the primary database for 5–10 seconds following a user write (or tracking the write's replication timestamp/LSN), while routing all other reads to replicas.
Architectural Trade-Off Analysis
Compare latency, throughput, consistency, and operational complexity across core industry alternatives.
Architecture Decision Matrix
Multi-axis engineering evaluation and FAANG interview rules of thumb.
SQL (Postgres / MySQL)
Relational ACID Store
Structured schema, relational joins, ACID transactions, and strict foreign keys.
NoSQL (Cassandra / DynamoDB)
Distributed Non-Relational Store
Horizontal partition keys, denormalized documents/wide-columns, and tunable consistency.
| Evaluation Axis | SQL (Postgres / MySQL) | NoSQL (Cassandra / DynamoDB) | Details |
|---|---|---|---|
Latency | Moderate | Strong | |
Throughput | Moderate | Strong | |
Scalability | Moderate | Strong | |
Consistency | Strong | Moderate | |
Complexity | Strong | Moderate | |
Cost | Moderate | Strong |
When to Choose in an Architecture Interview
Choose SQL when ACID guarantees are non-negotiable (financial ledgers, payment processing, inventory checkout) or when query access patterns are unpredictable and require ad-hoc analytical joins.
Choose NoSQL when write volume exceeds single-node capacity (>20K writes/sec), data is unstructured/hierarchical (logs, telemetry, user feeds), or multi-region active-active replication is mandatory.
Never say "NoSQL is faster than SQL" without qualification. SQL with proper B-Tree indexes on indexed primary keys is lightning fast. NoSQL wins on horizontal write scaling, not inherent single-row read speed.
Production Postmortem & Verification
Production Incident Postmortem
- Trigger / Setup: Two concurrent transactions check `SELECT SUM(balance)` before withdrawing funds under standard Read Committed isolation.
- Production Symptom: Both transactions read the initial positive balance and commit withdrawals simultaneously, leaving the account overdrawn.
- Root Cause & Fix: Read Committed does not guard against phantom rows or write skew; use pessimistic SELECT FOR UPDATE or serializable conflict detection.
Acceptance Criteria & Outcomes
- Define exact SQL isolation levels and identify which anomalies each level tolerates.
- Explain Multi-Version Concurrency Control (MVCC) mechanics and why Snapshot Isolation allows write skew.
- Map out the consistency spectrum: Strict Linearizability > Sequential > Causal > Monotonic Reads > Eventual.
Architectural & Technical Interview Review
Core FAANG Interview Questions
Q01How do you justify introducing ACID vs. BASE & The Consistency Spectrum into a high-scale production architecture?Click to reveal model answer →Hide answer ↑Requirements & Design Brief
Introduce ACID vs. BASE & The Consistency Spectrum only when measured scale, data size, or failure boundaries exceed single-node capacity. State the exact bottleneck, evaluate operational overhead, and verify that the trade-offs justify the added moving parts.
Q02What is the primary trade-off and hidden operational cost of ACID vs. BASE & The Consistency Spectrum?Click to reveal model answer →Hide answer ↑Architecture Trade-offs
It improves scalability and availability, but introduces network hops, serialization overhead, and eventual consistency or coordination complexity.
Q03How do you handle catastrophic failure or dependency degradation in ACID vs. BASE & The Consistency Spectrum?Click to reveal model answer →Hide answer ↑Failure Review & Recovery
Implement connect/read timeouts, exponential backoff with jitter, and fallback defaults so downstream degradations do not cause cascading outages.
Production Pitfalls & Anti-Patterns
Critical Failure Anti-Patterns
Premature Distributed Complexity
Scattering distributed components before identifying concrete write, read, or storage limits creates accidental points of failure and operational debt.
Production Mitigation: Start with the simplest modular design that satisfies requirements, evolving it only as measured constraints dictate.
Unbounded Retries and Cascading Storms
When a dependency degrades, immediate synchronized retries multiply downstream traffic, turning a minor slowdown into a total fleet outage.
Production Mitigation: Enforce strict retry budgets, exponential backoff, and full randomized jitter on all client calls.
Neglecting Observability and Metrics
Deploying architecture without distributed tracing or p99 tail latency alerts leaves engineers blind when partial partitions occur.
Production Mitigation: Export metrics for request rate, error rate, p95/p99 duration, and saturation at every service boundary.