ACID & BASE

ACID describes the guarantees of traditional (mostly SQL) transactions: - Atomicity — all-or-nothing. - Consistency — transactions move the DB between valid states. - Isolation — concurrent transactions don't interfere. - Durability — committed data survives crashes. BASE describes the philosophy of many NoSQL systems: - Basically Available — the system always responds. - Soft state — state may change over time without input. - Eventual consistency — replicas converge given enough time.

1. Learning Objectives

By the end of this lesson, you will be able to:

• Define the four ACID guarantees (Atomicity, Consistency, Isolation, Durability) and explain the precise semantics of each property.
• Define the three BASE properties (Basically Available, Soft State, Eventual Consistency) and explain why they arise in distributed systems.
• Contrast ACID and BASE as two ends of a consistency–availability spectrum and map each to appropriate use cases.
• Analyze isolation levels (Read Uncommitted, Read Committed, Repeatable Read, Serializable) and evaluate their impact on correctness, concurrency, and performance.
• Evaluate when to choose an ACID-compliant system versus a BASE-oriented system and articulate the trade-offs involved in each decision.
• Identify how ACID properties are enforced internally (WAL, locks, MVCC) and how BASE properties are achieved (anti-entropy, read-repair, vector clocks).

2. Prerequisites

To fully comprehend the concepts discussed in this lesson, you should be familiar with:

• Fundamental database concepts: tables, rows, primary keys, foreign keys, and basic SQL operations (SELECT, INSERT, UPDATE, DELETE).
• The concept of a database transaction — a logical unit of work comprising one or more operations.
• Basic distributed systems terminology: nodes, replicas, partitions, latency.
• Familiarity with relational databases (PostgreSQL, MySQL) and at least one NoSQL store (MongoDB, Cassandra, DynamoDB).
• Understanding of concurrency basics: race conditions, locks, and deadlocks.

3. Why This Topic Matters

Every production system that stores data must answer a fundamental question: How correct does the data need to be, and how fast does the system need to respond? This question is at the heart of the ACID vs. BASE debate. Choosing the wrong consistency model can lead to catastrophic failures — a banking system that double-debits an account, an e-commerce platform that oversells inventory, or a social feed that never shows new posts because the system is waiting for full consensus.

ACID compliance is non-negotiable for domains where data integrity is paramount — financial transactions, healthcare records, airline reservations. BASE semantics, on the other hand, unlock the horizontal scalability and fault tolerance needed by systems serving hundreds of millions of users — social networks, content delivery, real-time analytics dashboards. Understanding the spectrum between these two models is essential for any system design interview and for every architect building production-grade distributed systems.

The ACID/BASE distinction is also the conceptual foundation for the CAP theorem, PACELC theorem, and distributed transaction patterns (2PC, Saga) — all of which are built upon understanding what guarantees you are willing to relax and why.

4. Real-world Analogy

Imagine two different kinds of bank tellers:

The ACID Teller (Traditional Bank Branch): When you transfer $500 from your savings to your checking account, the teller locks both accounts, debits one, credits the other, records the transaction in an indelible ledger, and only then tells you "done." If the power goes out mid-transfer, the ledger ensures everything rolls back to its original state. The process is slow but absolutely correct — no money is ever created or destroyed.

The BASE Teller (Social Media "Likes" Counter): When you "like" a post on Instagram, the system immediately tells you "liked!" and shows you a heart icon. Behind the scenes, the like count might take a few seconds to propagate to all servers worldwide. For a brief moment, someone in Tokyo might see 1,042 likes while someone in London sees 1,041. Nobody cares about this tiny discrepancy — the system is fast, always available, and the counts will eventually converge. If you demanded perfect, locked consistency for every like, Instagram would be unusably slow at 2 billion users.

5. Core Concepts

ACID Properties

• Atomicity: A transaction is an indivisible unit. Either all operations within the transaction succeed (commit), or all operations are rolled back (abort). There is no partial execution. This is enforced via a Write-Ahead Log (WAL) or undo logs.
• Consistency: A transaction brings the database from one valid state to another. All integrity constraints (primary keys, foreign keys, CHECK constraints, triggers) are satisfied before and after the transaction. Note: this is application-level consistency, not the same "C" as in the CAP theorem.
• Isolation: Concurrent transactions execute as if they were running serially. The degree of isolation is configurable via isolation levels (Read Uncommitted → Read Committed → Repeatable Read → Serializable). Stronger isolation prevents more anomalies but reduces throughput.
• Durability: Once a transaction is committed, its changes are permanent and will survive any subsequent system failure (crash, power loss, disk failure). This is enforced by flushing the WAL to non-volatile storage before acknowledging the commit.

BASE Properties

• Basically Available: The system guarantees availability at all times. Every request will receive a response (success or failure), even during partial system failures. This is achieved through replication and partitioning across multiple nodes.
• Soft State: The system's state is not guaranteed to be consistent at all times. Data may change over time even without new input, as background processes propagate updates across replicas. The system does not enforce immediate consistency.
• Eventual Consistency: If no new updates are made to a given piece of data, all replicas will eventually converge to the same value. The convergence window depends on network latency, replication lag, and the specific anti-entropy protocol used.

Isolation Level Anomalies

6. Visualization

The diagram below illustrates the ACID–BASE consistency spectrum and how different database systems position themselves along it.

Below is a flowchart showing the decision process for choosing between ACID and BASE guarantees:

7. How It Works

Understanding how ACID and BASE work requires examining the lifecycle of a transaction under each model.

ACID Transaction Lifecycle

1. BEGIN: The client issues BEGIN TRANSACTION. The database engine allocates a transaction ID (XID), creates an undo log entry, and starts tracking all modifications made by this transaction.
2. Execute Operations: The client issues SQL statements (INSERT, UPDATE, DELETE). Each modification is first written to the Write-Ahead Log (WAL) on disk, then applied to in-memory buffer pages. Locks (row-level, table-level, or predicate locks depending on isolation level) are acquired to prevent conflicting concurrent access.
3. Validation: Before commit, the database checks all integrity constraints — primary key uniqueness, foreign key references, CHECK constraints, and triggers. If any constraint is violated, the transaction is aborted.
4. COMMIT: The WAL commit record is flushed to persistent storage (fsync). Once this succeeds, the transaction is guaranteed durable. The database then releases all locks, making the changes visible to other transactions (according to their isolation level).
5. ROLLBACK (failure path): If any step fails, the undo log is replayed in reverse order, restoring all modified pages to their pre-transaction state. All acquired locks are released.

BASE Operation Lifecycle

1. Client Write: The client sends a write to any available node (the coordinator). The coordinator writes the data locally.
2. Acknowledge: Once the coordinator (and optionally a quorum of W replicas) confirms the write, the client receives an acknowledgment. The system does not wait for all replicas to confirm.
3. Asynchronous Replication: The coordinator propagates the write to remaining replicas in the background. Network delays, node failures, or partitions may cause temporary divergence.
4. Anti-Entropy / Read Repair: Background processes (gossip protocols, Merkle tree comparisons, read-repair on read) detect and reconcile divergent replicas. Conflict resolution strategies (last-write-wins, vector clocks, CRDTs) determine the winning value.
5. Convergence: After a bounded (but unpredictable) period, all replicas hold the same value. The system has reached eventual consistency.

8. Internal Architecture

The table below details the internal components responsible for enforcing ACID and BASE properties, their responsibilities, and common failure points.

ACID Enforcement Components

BASE Enforcement Components

9. Request Lifecycle

Let's trace a concrete example through both models: transferring $100 from Account A to Account B.

ACID Request Lifecycle (PostgreSQL)

BASE Request Lifecycle (Cassandra)

10. Deep Dive

MVCC: The Engine Behind Modern ACID Isolation

Multi-Version Concurrency Control (MVCC) is the dominant technique for achieving isolation without forcing readers to block writers. Instead of locking a row when it's being read, the database maintains multiple versions of the row. Each transaction sees a snapshot of the database as it existed at the transaction's start time.

In PostgreSQL, every row has hidden columns xmin (the XID that created this version) and xmax (the XID that deleted or updated this version). A transaction can see a row only if xmin is committed and less than the transaction's snapshot XID, and xmax is either unset or uncommitted. This allows readers and writers to operate on the same table concurrently with zero lock contention — a massive performance advantage over traditional two-phase locking (2PL).

The downside of MVCC is version bloat. Old versions (dead tuples) accumulate and must be cleaned up by a background process — PostgreSQL's VACUUM, InnoDB's purge thread. If vacuuming falls behind (e.g., due to long-running transactions holding snapshots), the table bloats, queries slow down, and disk usage spikes. This is a common production incident in PostgreSQL deployments.

Tunable Consistency: The Space Between ACID and BASE

Many modern databases do not sit at a single point on the ACID–BASE spectrum. Systems like Cassandra, DynamoDB, and CockroachDB offer tunable consistency. In Cassandra, you configure consistency per query:

• ONE — write/read to a single replica. Maximum speed, lowest consistency. Pure BASE.
• QUORUM — write/read to a majority of replicas (⌊N/2⌋ + 1). Guarantees strong consistency if W + R > N.
• ALL — write/read to every replica. Strongest consistency, but a single node failure blocks the operation. Approaches ACID.
• LOCAL_QUORUM — quorum within the local datacenter only. Useful for multi-region deployments where cross-region latency is unacceptable.

By choosing W=QUORUM and R=QUORUM, you get linearizable reads (strong consistency) even from a BASE-oriented system like Cassandra. This proves that ACID and BASE are not binary — they are a continuum.

Eventual Consistency Convergence Protocols

Last-Write-Wins (LWW): Each write carries a timestamp. On conflict, the write with the highest timestamp wins. Simple but can silently discard valid writes if clocks are skewed. Used by Cassandra and DynamoDB by default.

Vector Clocks: Each node maintains a logical clock vector. On conflict, the system can detect causal ordering versus true concurrent writes. True concurrent writes are returned to the application for resolution. Used historically by Amazon Dynamo (the paper), Riak.

CRDTs (Conflict-free Replicated Data Types): Data structures mathematically guaranteed to converge without coordination. Examples include G-Counters (grow-only counters), OR-Sets (observed-remove sets), and LWW-Registers. Used by Redis (CRDTs in Redis Enterprise), Riak, and Automerge.

11. Production Example

Stripe — ACID for Payment Processing

Stripe processes billions of dollars in payments annually and relies heavily on ACID-compliant PostgreSQL for its core payment ledger. Every charge, refund, and payout is wrapped in a serializable transaction. When a customer is charged $50, Stripe's system:

1. Creates a charges record with status pending.
2. Sends the charge to the card network (Visa/Mastercard).
3. On success, updates the charge to succeeded and creates a balance_transaction record, atomically within a single transaction.
4. If the network call fails, the transaction is rolled back, and the charge remains in pending or transitions to failed.

Stripe cannot afford eventual consistency here — a double-charge or a missing refund record would erode trust and violate financial regulations.

Netflix — BASE for Viewing History and Recommendations

Netflix serves 260+ million subscribers across 190+ countries. Their viewing history and recommendation engine uses Apache Cassandra — a BASE-oriented, AP system. When you finish watching an episode:

1. The viewing event is written to the nearest Cassandra datacenter with consistency level LOCAL_ONE.
2. The write is asynchronously replicated to other global datacenters.
3. If you immediately switch to a different device in a different region, you might briefly not see the episode marked as "watched" — but within seconds, the data converges.
4. Netflix's recommendation engine reads with LOCAL_ONE for speed, tolerating slight staleness because a marginally outdated recommendation is far better than a timed-out request.

Netflix chooses BASE because the cost of a 2-second delay in viewing history sync is negligible, but the cost of a 2-second latency spike affecting all users globally would be catastrophic.

12. Advantages

Advantages of ACID

• Data Integrity: Guarantees that the database is always in a valid, consistent state. Essential for financial, healthcare, and regulatory compliance systems.
• Simplified Application Logic: Developers don't need to write compensating logic for partial failures — the database handles rollback automatically.
• Predictable Behavior: Serializable isolation means transactions behave as if they ran one at a time, making reasoning about correctness straightforward.
• Audit and Compliance: WAL-based durability and atomic commits create a reliable audit trail required by SOX, PCI-DSS, HIPAA, and similar regulations.
• Easier Debugging: Data anomalies (lost updates, phantom reads) are prevented by the database engine, reducing the surface area for bugs.

Advantages of BASE

• High Availability: The system continues responding even when nodes fail or network partitions occur. No single point of failure.
• Horizontal Scalability: Data is partitioned across many nodes. Adding capacity is as simple as adding nodes — no complex sharding logic at the application layer.
• Low Latency: Writes can be acknowledged after a single replica confirms, avoiding the round-trip overhead of distributed locking or consensus protocols.
• Geo-Distribution: Multi-region deployments can serve reads from the nearest datacenter without cross-region consensus, achieving sub-50ms global read latency.
• Fault Tolerance: Designed from the ground up to handle node failures gracefully via replication, hinted handoff, and automatic repair.

13. Limitations

Limitations of ACID

• Scalability Ceiling: Strong consistency requires coordination (locks, 2PC, consensus) that creates bottlenecks. Scaling beyond a single node requires complex distributed transaction protocols.
• Latency Overhead: Serializable isolation and synchronous WAL flushes add milliseconds to every transaction. In high-throughput systems, this overhead compounds.
• Deadlocks: Pessimistic locking can lead to deadlocks, requiring detection and automatic rollback of victim transactions.
• Distributed Transactions are Hard: Extending ACID across multiple services or databases (via 2PC or 3PC) introduces blocking, coordinator failure risks, and significant complexity.
• Single-Region Bias: Most ACID databases are optimized for single-datacenter deployments. Multi-region ACID (e.g., Google Spanner) requires specialized hardware (TrueTime) or significant latency penalties.

Limitations of BASE

• Complexity Shifts to the Application: Without automatic rollback, developers must implement compensating transactions, idempotency, and conflict resolution manually.
• Stale Reads: Clients may read outdated data. This is unacceptable for financial balances, inventory counts, or seat availability.
• Conflict Resolution: Concurrent writes to the same key from different nodes can produce conflicts. Resolving them (LWW, vector clocks, CRDTs) adds complexity and can silently discard data.
• Debugging Difficulty: Eventual consistency makes reproducing bugs extremely hard — the same query may return different results depending on which replica is queried and when.
• No Global Order: Without a global transaction log, it's impossible to reconstruct a total ordering of events across the system, complicating auditing and compliance.

14. Trade-offs

The fundamental trade-off: ACID trades availability and latency for correctness; BASE trades correctness (temporarily) for availability and latency. Most production systems use a hybrid approach — ACID for critical paths (payments, inventory), BASE for non-critical paths (notifications, analytics, feeds).

15. Performance Considerations

• WAL Flush Latency: Every ACID commit requires an fsync to disk. On HDDs, this can take 5–10ms. On NVMe SSDs, this drops to 50–100µs. Batching WAL flushes (group commit) amortizes this cost across multiple transactions.
• Lock Contention: Under high concurrency, row-level locks create contention hot spots (e.g., a single "counter" row). Solutions include advisory locks, optimistic concurrency (retry on conflict), or moving to MVCC with Serializable Snapshot Isolation (SSI).
• MVCC Vacuum Overhead: In PostgreSQL, dead tuple accumulation from MVCC requires periodic vacuuming. Autovacuum settings must be tuned to match write throughput — under-vacuumed tables cause index bloat and query degradation.
• Replication Lag (BASE): Asynchronous replication introduces a lag window (typically milliseconds to low seconds). Monitor replication lag and alert if it exceeds the application's staleness tolerance.
• Quorum Latency (BASE): Quorum operations (W=QUORUM, R=QUORUM) wait for the slowest node in the quorum. The p99 latency is bounded by the slowest healthy replica, not the fastest.
• Cross-Region Consensus: ACID across regions (e.g., Spanner, CockroachDB) adds cross-region RTT (50–200ms) to every transaction. This is acceptable for banking but catastrophic for gaming or real-time chat.
• Connection Pooling: ACID systems create one transaction per connection. Exhausting the connection pool (default: 100 in PostgreSQL) under load causes queuing. Use PgBouncer or similar poolers to multiplex connections.

16. Failure Scenarios

ACID Failure Scenarios

1. Crash During Transaction: The database crashes after writing to the WAL but before flushing dirty pages. On restart, the WAL is replayed (redo) to restore committed transactions and undo uncommitted ones. Data integrity is preserved.
2. Deadlock: Transaction A holds lock on row 1 and waits for row 2; Transaction B holds lock on row 2 and waits for row 1. The deadlock detector (runs periodically or on timeout) aborts one transaction (the "victim") and rolls it back.
3. Long-Running Transaction: A transaction running for hours holds snapshots (MVCC) or locks. This prevents vacuum from cleaning dead tuples (PostgreSQL) or blocks other transactions. Solution: set idle_in_transaction_session_timeout.
4. Disk Full: WAL cannot be written. The database refuses all new writes (and potentially crashes). Solutions: disk space monitoring, WAL archival to external storage, PG's max_wal_size configuration.

BASE Failure Scenarios

1. Network Partition: Two groups of nodes cannot communicate. Writes to one group are invisible to the other. After the partition heals, anti-entropy reconciles the divergent data. Conflicts are resolved via LWW, vector clocks, or CRDTs.
2. Write Conflict: Two clients update the same key on different replicas simultaneously. With LWW, the write with the latest timestamp wins — the other is silently discarded. This can cause data loss. Solution: use CRDTs or application-level merge logic.
3. Hinted Handoff Overflow: A replica is down for an extended period. Hints accumulate on coordinator nodes. If hints exceed storage limits, they are dropped, and the recovering node must rely on full anti-entropy repair, which is slow and CPU-intensive.
4. Read-Your-Writes Violation: A client writes to Replica A, then reads from Replica B before replication completes. The client sees stale data (its own write is "missing"). Solution: sticky sessions (route client to same replica) or read-your-writes consistency level.

17. Best Practices

• Keep ACID transactions short: Long transactions hold locks and MVCC snapshots, causing contention and vacuum delays. Aim for transaction durations under 100ms.
• Use the weakest isolation level that is correct: Don't default to Serializable. Most OLTP workloads are correct with Read Committed (PostgreSQL default). Only upgrade to Serializable when you need to prevent phantom reads or write skew.
• Use optimistic concurrency control for low-contention workloads: Instead of pessimistic locks, use version columns (WHERE version = N) and retry on conflict. This avoids lock waits entirely.
• Design idempotent operations for BASE systems: Since retries are common in eventually consistent systems, every write operation should produce the same result if applied multiple times.
• Use hybrid architectures: Route critical-path operations (payments, inventory) through an ACID database; route high-volume, latency-sensitive operations (feeds, analytics) through a BASE store.
• Monitor replication lag in BASE systems: Set up alerts for replication lag exceeding your staleness SLA. Dashboard the p50, p95, and p99 replication lag.
• Set idle transaction timeouts: Configure idle_in_transaction_session_timeout in PostgreSQL and equivalents in other RDBMS to prevent leaked transactions.
• Use CRDTs over LWW when writes can conflict: If your application allows concurrent writes to the same key, CRDTs provide mathematically safe convergence without data loss.
• Tune autovacuum aggressively: In PostgreSQL, the default autovacuum settings are conservative. For high-write workloads, reduce autovacuum_vacuum_scale_factor and increase autovacuum_max_workers.

18. Common Mistakes

• Using ACID for everything: Wrapping every operation (including logging, analytics inserts, notification sends) in a serializable transaction creates unnecessary bottlenecks. Only use ACID where correctness truly matters.
• Assuming BASE means "no consistency": BASE guarantees eventual consistency, not no consistency. With proper quorum configurations (W + R > N), BASE systems can provide strong consistency per-query.
• Confusing ACID Consistency with CAP Consistency: The "C" in ACID means integrity constraints (application-level). The "C" in CAP means linearizability (all nodes see the same data at the same time). These are different concepts.
• Ignoring isolation level defaults: PostgreSQL defaults to Read Committed; MySQL/InnoDB defaults to Repeatable Read. Not understanding these defaults can lead to unexpected phantom reads or non-repeatable reads in PostgreSQL, or unnecessary overhead in MySQL.
• Not handling deadlocks in application code: ACID databases will abort one transaction in a deadlock. If your application doesn't catch this error and retry, the user sees an unexplained failure.
• Using LWW without understanding clock skew: Last-Write-Wins depends on synchronized clocks. If Node A's clock is 5 seconds ahead, its writes will always "win" over Node B's, even if Node B's write was logically later. Use NTP or, better, logical clocks.
• Running long-running analytics queries on the OLTP database: This holds MVCC snapshots, preventing vacuum and causing bloat. Use read replicas or a dedicated OLAP store for analytics.
• Ignoring read-your-writes semantics: After a write in a BASE system, immediately reading from a different replica may return stale data. Use sticky sessions or a higher consistency level for the subsequent read.

19. Implementation

Below is a complete, working example demonstrating ACID transaction semantics (using PostgreSQL with Python) and a conceptual BASE operation pattern.

ACID Transaction — Bank Transfer (Python + PostgreSQL)

BASE Operation — Idempotent Like Counter (Python + Cassandra)

20. Interview Questions

Easy

Q: What does each letter in ACID stand for, and what does each guarantee?

A: Atomicity — all-or-nothing execution; Consistency — the database transitions between valid states, honoring all constraints; Isolation — concurrent transactions don't interfere with each other; Durability — committed data persists through crashes. Together, these guarantee that transactions are reliable, correct, and permanent.

Q: What does "eventual consistency" mean in practical terms?

A: It means that after a write, not all replicas may immediately reflect the new value. However, given enough time with no new writes, all replicas will converge to the same value. The "convergence window" is typically milliseconds to low seconds in a healthy system. During this window, different clients may see different values depending on which replica they read from.

Medium

Q: How does MVCC achieve isolation without locking, and what is its main drawback?

A: MVCC maintains multiple versions of each row, stamped with transaction IDs. Each transaction reads from a snapshot — it sees only row versions committed before its start time. Writers create new versions rather than overwriting existing ones, so readers never block writers and vice versa. The main drawback is version bloat: old row versions (dead tuples) accumulate and must be cleaned up by background processes (e.g., PostgreSQL's VACUUM). If cleanup falls behind, table size grows, indexes bloat, and query performance degrades.

Q: A system uses Cassandra with W=QUORUM and R=QUORUM on a replication factor of 3. Is this ACID or BASE? Does it provide strong consistency?

A: The system is architecturally BASE (Cassandra is an AP system with no built-in transactions, locking, or WAL-based atomicity). However, with W=QUORUM (2 of 3) and R=QUORUM (2 of 3), since W + R = 4 > N = 3, every read is guaranteed to hit at least one replica that has the latest write. This provides strong consistency for individual reads and writes — but it does not provide multi-statement ACID transactions (atomicity across multiple operations, rollback, or isolation).

Hard

Q: You are designing a global e-commerce platform. The checkout flow involves: (1) reserving inventory, (2) charging the customer's credit card, (3) creating an order record, and (4) sending a confirmation email. Which operations should be ACID, which should be BASE, and how would you coordinate them?

A: Operations (1), (2), and (3) must be coordinated with ACID-like guarantees because they involve money and inventory. However, wrapping all three in a distributed ACID transaction (2PC) is fragile and slow. The recommended approach is the Saga pattern: each step is a local ACID transaction, and if a step fails, compensating transactions undo previous steps (e.g., release reserved inventory, refund the charge). Operation (4) — sending the confirmation email — should be BASE: enqueue the email to a message queue (e.g., SQS, Kafka) and process it asynchronously. If the email fails, retry later; the customer's order is already safely committed. This hybrid approach achieves correctness for financial operations and availability for non-critical side effects.

Q: Explain the "write skew" anomaly. Under which ACID isolation levels can it occur, and how does Serializable Snapshot Isolation (SSI) prevent it?

A: Write skew occurs when two transactions read the same data, make decisions based on that data, and then write to different rows — but the combined effect violates an invariant. Classic example: a hospital requires at least one doctor on call. Doctors A and B both read "2 doctors on call," decide it's safe to go off call, and each writes their own row to "off call." Result: 0 doctors on call. This cannot happen under Serializable isolation but can happen under Repeatable Read (which only prevents writes to the same rows). SSI (used by PostgreSQL Serializable) detects this by tracking read dependencies: if transaction T1 read data that T2 modified (or vice versa), and both commit, SSI detects the dangerous cycle and aborts one transaction with a serialization error, requiring a retry.

21. Practice Exercises

Easy

Given the following scenario, classify each system as ACID or BASE and justify your answer:

1. A banking application where withdrawals and deposits must never result in negative balances.
2. A real-time analytics dashboard showing the number of active users on a website.
3. An airline reservation system that prevents overbooking seats on a flight.
4. A social media feed showing the latest posts from people you follow.

Medium

Design the database schema and transaction flow for a movie ticket booking system. The system must:

• Allow users to select seats and pay for tickets.
• Prevent two users from booking the same seat.
• Handle concurrent booking attempts gracefully.
• Specify the isolation level you would use and explain why.
• Show what happens if the payment gateway fails mid-transaction.

Hard

You are building a multi-region e-commerce inventory system using Cassandra (3 datacenters, RF=3 per DC). Design the consistency configuration for the following operations:

• Decrementing inventory when an order is placed (must not oversell).
• Displaying the current inventory count on the product page (can tolerate slight staleness).
• Generating a daily inventory report for the warehouse team (can tolerate staleness up to 1 hour).

For each operation, specify the consistency level (ONE, LOCAL_QUORUM, QUORUM, ALL), explain the trade-off, and describe what happens if a datacenter goes down during each operation.

22. Challenge Problem

Scenario: You are the lead architect at a fintech company building a digital wallet platform (like Venmo or Cash App). The platform has 50 million users across 15 countries and processes 100,000 transactions per second at peak. The system must support:

1. Peer-to-peer money transfers: User A sends $25 to User B. Both balances must update atomically. No money should ever be created or destroyed.
2. Transaction history feed: Each user sees a chronological list of their sent and received transactions. The feed can be slightly delayed (up to 5 seconds).
3. Real-time fraud detection: Every transaction is scored by an ML model in real-time. Suspicious transactions are flagged but should not block legitimate transfers.
4. Promotional cashback: After certain transactions, users receive cashback credits. The cashback amount can be eventually consistent.
5. Multi-region deployment: The platform must serve users in North America, Europe, and Asia-Pacific with sub-200ms latency for all operations.

Design the data architecture specifying which components use ACID and which use BASE. Address: database choices per component, consistency levels, failure handling, cross-region replication strategy, and how you would handle a network partition between the US and EU datacenters during a transfer from a US user to an EU user.

23. Summary

ACID and BASE represent two fundamental philosophies for managing data in computer systems. ACID (Atomicity, Consistency, Isolation, Durability) provides strong transactional guarantees — every transaction is all-or-nothing, the database always transitions between valid states, concurrent operations don't interfere, and committed data survives any failure. These guarantees come at the cost of scalability and latency, making ACID ideal for financial systems, healthcare records, and any domain where data correctness is non-negotiable.

BASE (Basically Available, Soft State, Eventual Consistency) trades immediate correctness for availability, scalability, and low latency. The system always responds, state may temporarily diverge across replicas, and replicas converge over time. BASE is ideal for social media feeds, analytics, content delivery, and any domain where slight staleness is acceptable but downtime is not.

The key insight is that ACID and BASE are not binary choices — they are endpoints on a continuum. Modern systems use tunable consistency (quorum reads/writes), hybrid architectures (ACID for critical paths, BASE for non-critical paths), and patterns like the Saga to get the best of both worlds. The right choice depends on your domain's tolerance for staleness, your availability requirements, and your scale ambitions.

24. Cheat Sheet

25. Quiz

1. Which ACID property is enforced by the Write-Ahead Log (WAL)?

1. a) Consistency only
2. b) Isolation only
3. c) Atomicity and Durability
4. d) Availability

Answer: c) The WAL ensures atomicity (undo uncommitted changes on crash) and durability (redo committed changes on crash).

2. What does "Soft State" in BASE mean?

1. a) The system can tolerate software bugs
2. b) State may change over time even without new input
3. c) Data is stored in volatile memory only
4. d) The system state is always eventually deleted

Answer: b) Soft state means the system's state may change over time as background replication processes propagate updates, even without new client input.

3. Which isolation level prevents all read anomalies (dirty reads, non-repeatable reads, and phantom reads)?

1. a) Read Uncommitted
2. b) Read Committed
3. c) Repeatable Read
4. d) Serializable

Answer: d) Serializable is the strictest isolation level and prevents all three anomalies by ensuring transactions execute as if they ran serially.

4. In a Cassandra cluster with RF=3, which consistency level configuration guarantees strong consistency?

1. a) W=ONE, R=ONE
2. b) W=ONE, R=ALL
3. c) W=QUORUM, R=QUORUM
4. d) Both b) and c)

Answer: d) Strong consistency requires W + R > N. With N=3: (b) 1 + 3 = 4 > 3 ✓ and (c) 2 + 2 = 4 > 3 ✓. Both satisfy the quorum overlap condition.

5. The "C" in ACID and the "C" in CAP refer to the same concept.

1. a) True
2. b) False

Answer: b) False. ACID "C" refers to application-level integrity constraints (foreign keys, CHECK constraints). CAP "C" refers to linearizability — all nodes see the same data at the same time.

6. Which mechanism allows readers and writers to operate concurrently without blocking each other in PostgreSQL?

1. a) Two-Phase Locking (2PL)
2. b) Multi-Version Concurrency Control (MVCC)
3. c) Optimistic Concurrency Control
4. d) Gossip Protocol

Answer: b) MVCC maintains multiple row versions, allowing readers to see a snapshot while writers create new versions. No read locks are needed.

7. A "write skew" anomaly can occur under which isolation level?

1. a) Serializable
2. b) Repeatable Read
3. c) Read Uncommitted only
4. d) It cannot occur under any ACID isolation level

Answer: b) Write skew occurs when two transactions read the same data and write to different rows, violating an invariant. Repeatable Read prevents writes to the same rows but cannot prevent write skew. Only Serializable prevents it.

8. Which of the following is NOT an advantage of BASE systems?

1. a) High availability during node failures
2. b) Horizontal scalability
3. c) Automatic rollback of failed operations
4. d) Low write latency

Answer: c) Automatic rollback is an ACID property (atomicity). BASE systems require the application to implement compensating transactions for failure handling.

9. What is the primary risk of using Last-Write-Wins (LWW) for conflict resolution in a BASE system?

1. a) It requires distributed locks
2. b) It can silently discard valid writes due to clock skew
3. c) It prevents eventual consistency
4. d) It requires a centralized coordinator

Answer: b) LWW uses timestamps to resolve conflicts. If node clocks are not perfectly synchronized (clock skew), a logically earlier write with a later timestamp will overwrite a logically later write, silently discarding data.

10. Which pattern replaces distributed ACID transactions (2PC) in microservice architectures?

1. a) CQRS
2. b) Event Sourcing
3. c) Saga Pattern
4. d) Sharding

Answer: c) The Saga pattern coordinates a sequence of local ACID transactions across services. If a step fails, compensating transactions undo the effects of previous steps, achieving eventual atomicity without the blocking nature of 2PC.

26. Further Reading

• "Designing Data-Intensive Applications" by Martin Kleppmann — Chapters 7 (Transactions) and 9 (Consistency and Consensus). The definitive modern reference on ACID, isolation levels, and distributed consistency.
• "A Critique of ANSI SQL Isolation Levels" by Berenson et al. (1995) — The seminal paper that formalized isolation level anomalies (dirty reads, phantom reads, write skew) beyond the ANSI SQL standard.
• "BASE: An Acid Alternative" by Dan Pritchett (2008) — The original article that coined the BASE acronym and articulated the trade-offs for large-scale web systems.
• "Dynamo: Amazon's Highly Available Key-value Store" (2007) — Amazon's foundational paper on eventually consistent, AP systems with tunable consistency, vector clocks, and sloppy quorums.
• "Cassandra – A Decentralized Structured Storage System" by Lakshman & Malik (2010) — Describes Cassandra's approach to tunable consistency, gossip-based failure detection, and anti-entropy.
• "Spanner: Google's Globally-Distributed Database" (2012) — How Google achieved globally distributed ACID with TrueTime (GPS + atomic clocks) — the state of the art in strong consistency at scale.
• "Conflict-free Replicated Data Types" by Shapiro et al. (2011) — The foundational CRDT paper describing data structures that converge without coordination.

27. Next Lesson Preview

In the next lesson, CAP Theorem, we will formalize the trade-off between consistency and availability that underpins the ACID vs. BASE decision. You will learn why network partitions are unavoidable in distributed systems, why the real choice during a partition is between consistency (CP) and availability (AP), and how to classify popular databases along the CP/AP spectrum. The CAP theorem provides the theoretical foundation for understanding why BASE systems exist and when you must relax ACID guarantees to keep your system operational at scale.