Skip to main content

ACID vs BASE

Why this matters in interviews: every design has data that must never be wrong (money, inventory, unique usernames) and data that can be briefly stale (like counts, feeds, view counts). Interviewers want you to sort features into those two buckets, pick the right guarantee for each, and explain the mechanisms: isolation levels and their anomalies on the ACID side, and quorums, conflict resolution and idempotency on the BASE side. Saying "strong consistency everywhere" or "eventual consistency everywhere" is a red flag.

Blueprint components this feeds: 6 (database selection), 9 (asynchronous processing), 10 (distributed systems) and 11 (reliability) in the HLD Blueprint. Related: SQL vs NoSQL · Indexes. For SQL-level examples (locking, isolation in Postgres, deadlocks), see Database → Transactions & ACID.


1. Two philosophies​

ACIDBASE
Stands forAtomicity, Consistency, Isolation, DurabilityBasically Available, Soft state, Eventual consistency
PriorityCorrectness of every transactionAvailability and scale, with correctness reached over time
Typical homeSingle-node or consensus-replicated relational databasesPartitioned, replicated stores (Dynamo-style, Cassandra), caches, event-driven systems
On failure or partitionRefuse or block rather than be wrongKeep answering, possibly with stale or conflicting data, then reconcile
Developer experienceThe database protects invariants for youThe application must handle staleness, conflicts and retries

BASE was named in the late 1990s (Brewer and colleagues, as a deliberate contrast to ACID) and popularized by eBay's Dan Pritchett in BASE: An ACID Alternative (2008).

It's a spectrum, not a switch. Modern systems mix them: DynamoDB offers strongly consistent reads and transactions; Cassandra lets you choose consistency per query; Spanner gives ACID across regions. You choose per feature, often per query.

2. ACID, letter by letter (what each really guarantees)​

Atomicity: all or nothing​

A transaction's writes either all take effect or none do. If the process crashes halfway through a transfer, the debit without the credit is rolled back. Implemented with a write-ahead log (WAL) or undo log.

Atomicity is about aborts, not concurrency. It's what makes "retry the whole thing" safe.

Consistency: invariants hold​

Every transaction takes the database from one valid state to another: constraints (NOT NULL, UNIQUE, foreign keys, CHECK balance >= 0) are never violated.

Two important nuances:

  • Much of "C" is really the application's job. The database enforces declared constraints, but "debits equal credits" holds only if your transactions are written correctly. A, I and D are the database's tools that let you achieve C.
  • ACID's C is not CAP's C. ACID consistency means "invariants hold". CAP consistency means linearizability ("every read sees the latest write, as if there were one copy"). Mixing them up is a common interview slip.

Isolation: concurrent transactions don't step on each other​

Ideally (serializable) the result is as if transactions ran one at a time. In practice, databases default to weaker levels for performance, and each level allows certain anomalies. This is where interviews go deep.

Durability: committed means saved​

Once committed, data survives crashes: the WAL is flushed (fsync) before acknowledging. In a replicated system, durability also depends on how many replicas confirmed the write: an async replica that hadn't received the last commits loses them on failover. "Durable" should always be stated as "durable against what" (process crash, disk loss, zone loss, region loss).

3. Isolation levels and anomalies (the most-asked part)​

The anomalies​

AnomalyWhat happensExample
Dirty readYou read data another transaction hasn't committed (and may roll back)You see a balance from a transfer that's later aborted
Dirty writeYou overwrite another transaction's uncommitted writeTwo updates interleave, leaving a mix of both
Non-repeatable read (read skew)You read the same row twice and get different valuesA report sums accounts while a transfer moves money between them, so the total is off
Lost updateTwo read-modify-write cycles race, and one overwrites the otherTwo clients do likes = likes + 1 via read-then-write; one increment is lost
Phantom readA repeated range query returns new rows inserted by another transaction"Count bookings for room 7 tonight" changes mid-transaction
Write skewTwo transactions read the same data, make disjoint writes based on it, and together break an invariantTwo on-call doctors each see "2 on call", each takes themselves off, and nobody is on call

The levels​

LevelPreventsStill allowsDefault in
Read uncommittedDirty writesEverything elseRarely used
Read committedDirty reads, dirty writesNon-repeatable reads, lost updates, phantoms, write skewPostgreSQL, Oracle, SQL Server
Repeatable read / snapshot isolationThe above + non-repeatable reads (and phantoms in snapshot-based engines)Write skew; lost updates in some enginesMySQL InnoDB (repeatable read); Postgres's REPEATABLE READ is snapshot isolation
SerializableEverything aboveNothing (at the cost of locking or aborts)CockroachDB, FoundationDB, Spanner (stronger still: externally consistent)

Engine nuances worth knowing: Postgres SERIALIZABLE uses SSI (serializable snapshot isolation: optimistic, aborts conflicting transactions, so the client must retry). MySQL SERIALIZABLE uses locking. Oracle's SERIALIZABLE is actually snapshot isolation, so it allows write skew. Names are not portable, so say what anomalies you need to prevent.

Fixing the common anomalies without going fully serializable​

ProblemFix
Lost update on a counterAn atomic update: UPDATE posts SET likes = likes + 1 WHERE id = ?
Lost update on a read-modify-writeOptimistic locking with a version column: UPDATE ... SET ..., version = version + 1 WHERE id = ? AND version = ? and retry if 0 rows changed. Or pessimistic: SELECT ... FOR UPDATE.
Write skew (doctors, double booking)Lock the rows the decision depends on (FOR UPDATE), materialize the conflict (a row per room-night with a unique constraint), or use serializable isolation
Unique username / one seat sold onceA unique constraint or a conditional insert. The database enforces it atomically.
Inventory never negativeUPDATE stock SET qty = qty - 1 WHERE sku = ? AND qty > 0 and check the affected-row count

Interview line: "Booking a seat is a write-skew risk. I'll make each seat a row and do a conditional update WHERE status = 'AVAILABLE', so the database serializes the conflict on that row. I don't need serializable isolation for the whole system."

4. BASE, in depth​

Basically available​

The system keeps answering during failures and partitions, possibly with a degraded response (stale data, partial results, a queued write), instead of returning errors.

Soft state​

The state may change without new input as replicas exchange updates in the background. What a replica holds right now isn't necessarily final.

Eventual consistency​

If writes stop, all replicas converge to the same value eventually. It says nothing about when (usually milliseconds to seconds, but unbounded during partitions) or which value wins (that's the conflict-resolution policy).

How BASE systems actually converge​

MechanismWhat it does
Quorum reads and writesN replicas, write to W, read from R. R + W > N means reads overlap the latest write (N=3, W=2, R=2). Lower R or W for speed and availability.
Read repairA read that sees replicas disagree writes the newest value back to the stale ones
Hinted handoffIf a replica is down, another node keeps the write as a "hint" and delivers it when the replica returns
Anti-entropy with Merkle treesReplicas compare hash trees of their data and sync only the differing ranges
Conflict resolutionLast-write-wins (simple, but silently drops concurrent writes, and relies on clocks), vector clocks / version vectors (detect concurrency, let the app merge, like Dynamo's shopping cart), CRDTs (data types that merge automatically: counters, sets, collaborative text)

Note on quorums: R + W > N isn't the same as linearizability. Sloppy quorums (writing to substitute nodes during failures), concurrent writes and clock-based LWW can still produce stale or lost writes. That's why systems needing true compare-and-set add a consensus step (Cassandra lightweight transactions use Paxos).

Consistency between strong and eventual​

"Eventual" is the floor, not the only option. Ask which of these the feature actually needs:

GuaranteeMeaningTypical need
LinearizableBehaves like a single copy, in real-time orderLocks, leader election, unique IDs, balances
CausalEffects never appear before their causesA reply never shows before the comment it answers
Read-your-writesYou always see your own updatesAfter editing your profile, you see the edit
Monotonic readsYou never see data go backwards in timeA refreshed feed never loses posts you already saw
EventualConverges sometimeLike counts, view counts, recommendations

Session guarantees (read-your-writes, monotonic reads) are cheap to add: route a user's reads to the primary for a few seconds after they write, pin a session to one replica, or carry a "minimum version" token with each request.

5. How this connects to CAP and PACELC​

  • CAP: during a network partition, a replicated system must choose between consistency (linearizability; refuse some requests) and availability (answer everything, possibly stale). ACID-style systems lean CP; BASE systems lean AP.
  • PACELC: else, when there's no partition, you still trade latency vs consistency. Synchronous replication across zones costs milliseconds on every write; async replication is fast but can lose the last writes on failover. This is the trade-off you make every day, not just during partitions.
  • Details and examples: Blueprint §10 and Replication & Scaling.

6. ACID across services: what to do when one database isn't enough​

In a microservice design, an "order" touches the order, payment and inventory services, each with its own database. A single ACID transaction no longer spans the whole operation. The options:

ApproachHowTrade-off
Keep it in one serviceRedraw boundaries so the invariant lives in one databaseSimplest and strongest. Prefer it when possible.
Two-phase commit (2PC)A coordinator gets every participant to prepare, then commitAtomic, but blocking if the coordinator fails, and slow (locks held across network round-trips). Rare across microservices.
SagaA chain of local ACID transactions; on failure, run compensating transactions (refund, release stock)No distributed locks, but intermediate states are visible, and compensations must be designed
Transactional outboxWrite the business change and an event row in the same local transaction; a relay publishes the eventGuarantees "DB updated ⇔ event published" without 2PC. At-least-once delivery, so consumers must be idempotent.
Idempotency keysClients send a unique key per logical operation; the server stores results by keyMakes retries safe, which every BASE pipeline relies on

A saga for "place order":

1. Order service:     create order (PENDING)                      ← local ACID transaction
2. Payment service: charge card ── fails? → cancel order
3. Inventory service: reserve items ── fails? → refund payment → cancel order
4. Order service: mark order CONFIRMED

Each step is ACID locally; the whole flow is BASE (eventually consistent, with compensations).

7. Choosing per feature: one system, both models​

E-commerce example:

FeatureModelWhy
Payment capture, ledgerACIDMoney must balance; double charges are unacceptable
Inventory reservation at checkoutACID (conditional update on the SKU row)Overselling the last item is a real loss
Order placement across servicesSaga + outboxSpans services; compensations are acceptable
Shopping cartBASEMust always accept "add to cart"; merging two concurrent versions is fine
Product reviews and ratings averageBASEA few seconds of staleness is invisible
Search index, recommendationsBASEDerived data rebuilt from the source of truth
"Your order was placed" pageRead-your-writesThe user must see their own order immediately, even if other views lag

8. Interview questions (with crisp answers)​

Q: What's the difference between ACID and BASE? ACID guarantees each transaction is atomic, isolated and durable, keeping invariants intact even if that costs availability or latency. BASE keeps the system available and scalable, accepting temporary inconsistency that converges later. Most real systems use both, per feature.

Q: Is the C in ACID the same as the C in CAP? No. ACID's C means invariants and constraints hold. CAP's C means linearizability: every read sees the latest write across replicas.

Q: What isolation level does Postgres use by default, and what can go wrong? Read committed. It allows non-repeatable reads, lost updates on read-modify-write code and write skew. Use atomic updates, FOR UPDATE, optimistic version checks, or serializable (with retries) where those matter.

Q: Explain write skew with an example. Two doctors on call, the rule "at least one on call". Both read "2 on call" concurrently, both remove themselves, and both commit because they wrote different rows. Snapshot isolation allows it. Serializable isolation or locking the rows read (FOR UPDATE) prevents it.

Q: How do eventually consistent systems resolve conflicts? Last-write-wins (simple but lossy), version vectors with application-level merges, or CRDTs that merge automatically. Plus read repair, hinted handoff and anti-entropy to spread the winning value.

Q: How do you keep data consistent across microservices without distributed transactions? Keep invariants inside one service where possible. Otherwise use sagas with compensations, the transactional outbox for reliable events, and idempotent consumers keyed by an idempotency key or event ID.

Q: Would you use eventual consistency for a bank balance? Not for the balance itself, which needs ACID. But many surrounding features can be eventual: transaction history views, notifications, analytics, fraud-score updates.

9. Common mistakes​

  • "NoSQL is BASE, SQL is ACID" as an absolute. Both families now offer both, with limits.
  • Assuming the default isolation level is serializable.
  • Read-modify-write in application code without a lock, version check or atomic update.
  • Using eventual consistency for invariants (inventory, unique usernames, balances).
  • Using strong consistency everywhere, paying latency and availability for data nobody needs fresh.
  • Last-write-wins with wall clocks from different machines, then being surprised by lost writes.
  • Retries without idempotency, producing duplicate charges or messages.
  • Calling async replication "durable" without saying it can lose the last writes on failover.