ACID vs BASE Properties in Databases

ACID is not a switch you flip; it is the observable behavior of two engine mechanisms grinding away underneath every transaction — a write-ahead log (WAL) that durably records what a transaction intends to change before that change touches the real data pages, and multi-version concurrency control (MVCC) that hands each transaction its own consistent snapshot so concurrent readers and writers neither block nor corrupt one another.

Everything people call "ACID" falls out of those two mechanisms. BASE (Basically Available, Soft state, Eventually consistent) is the design you reach for when you deliberately give up the single-copy-always-correct guarantee — usually to keep serving during network partitions and to spread writes across many machines. They are not a binary; they are the two ends of a spectrum of how much correctness you trade for availability and scale. The rest of this page shows how ACID is actually implemented, then where BASE and the modern middle ground fit.

The four properties, stated precisely

We will trace one running example: transfer $100 from A to B, where A starts at $500 and B at $100 (total $600).

• Atomicity — all-or-nothing. The debit of A and credit of B either both take effect or neither does. If the process dies after debiting A, recovery undoes the partial work so A is back at $500. This is a pure WAL property (undo).
• Consistency — no declared invariant is ever left violated. This is the subtle one, and the property people most often misstate. ACID-C does not mean "money is conserved." It means: every committed transaction moves the database from one state that satisfies all the constraints you declared (primary keys, foreign keys, CHECK, uniqueness, triggers) to another such state — the engine will refuse to commit anything that violates them. "Total stays $600" is only guaranteed if you actually encode that rule (a constraint/trigger, or a serializable transaction that reads and writes both rows). ACID-C is the one property the application co-owns: the DB enforces exactly what you declare, no more. (Note: this is a different "C" from the C in CAP — see below.)
• Isolation — concurrent transactions appear to run one at a time. The gold standard is serializable, but in practice you choose a weaker level and accept specific anomalies in exchange for throughput. Implemented by MVCC plus locking.
• Durability — once COMMIT returns, the change survives a crash. Guaranteed because the transaction's commit log record was flushed (fsync'd) to stable storage before the client was told "committed."

How atomicity and durability actually work: the write-ahead rule

One rule buys both properties: the log record describing a change must reach durable storage before the modified data page does, and a transaction is not "committed" until its COMMIT record is fsync'd. The data pages themselves can stay dirty in memory and get flushed minutes later — the log is the source of truth. Trace the transfer:

1. Append #10 BEGIN T1 to the log buffer.
2. Append #11 A: 500→400 (old value and new value — old value enables undo, new value enables redo).
3. Append #12 B: 100→200.
4. Append #13 COMMIT and fsync the log up to #13. Only now does the client hear "success."

After a crash, recovery (the ARIES scheme: analyze → redo → undo) scans the log. If #13 made it to disk, it redoes #11 and #12 even if the data pages were never written — durability. If #13 did not, T1 is undone using the old values — atomicity. The single fsync at commit is why durability costs latency; databases amortize it with group commit (batching many transactions' commit records into one fsync).

How isolation actually works: MVCC and isolation levels

Under MVCC, an UPDATE does not overwrite a row — it writes a new version, tagged with the transaction id that created it (xmin) and, on the old version, the id that superseded it (xmax). Each transaction reads against a snapshot: it sees only versions committed as of its start point. So a reader never waits for a writer and a writer never waits for a reader — the classic "readers don't block writers" behavior. Old versions live until no snapshot needs them, then a background collector (Postgres VACUUM) reclaims them.

Perfect isolation (serializable) is expensive, so engines expose weaker levels, each admitting specific anomalies:

*The SQL standard allows phantoms at Repeatable Read; PostgreSQL's Repeatable Read is actually snapshot isolation and blocks phantoms too — but still permits write skew. Defaults differ and this bites people: PostgreSQL, Oracle and SQL Server default to Read Committed; MySQL/InnoDB defaults to Repeatable Read. Guaranteeing a cross-row invariant like "total stays $600" requires Serializable (or explicit locking) — a plain snapshot is not enough.

What is BASE?

BASE describes systems — mostly Dynamo-style distributed stores — that keep serving even when nodes can't all talk to each other, by relaxing when copies must agree.

• Basically Available: every request gets a response (possibly stale) rather than an error or a hang, even during a partition or under heavy load.
• Soft state: a replica's state can drift on its own as updates propagate in the background — there is no single authoritative value at every instant.
• Eventually consistent: if writes stop, all replicas converge to the same value. When, and by what merge rule, is up to the system.

The machinery here is replication, not logging: writes go to several replicas, reads gather from several, and the system uses quorums (serve the read when enough replicas answer), read repair, hinted handoff, and conflict resolution (last-write-wins, vector clocks, or CRDTs) to converge. Many stores let you dial this per operation via tunable consistency: if reads + writes overlap on a majority (R + W > N), you get read-your-writes even on an eventually-consistent engine, at the cost of latency.

The CAP trade-off, stated correctly

CAP is routinely mangled as "pick two of three." The accurate statement: when a network partition happens (and it will — P is not optional), a distributed system must choose, for that partition, between staying Consistent (linearizable) and staying Available. It cannot be both while the partition lasts. When there is no partition, a system can be both C and A — CAP forces a choice only during the split. So the real design axis is CP vs AP, and P is a fact of life, not a menu item.

Two clarifications a senior engineer keeps straight. First, the C in CAP (linearizability across nodes) is not the C in ACID (invariant preservation) — a single-node ACID database trivially has ACID-C but says nothing about CAP. Second, CAP only speaks about partitions; PACELC completes it: else (no partition), you still trade latency against consistency. And modern distributed SQL (Google Spanner, CockroachDB, YugabyteDB) shows CP done well — full ACID transactions across partitions using consensus (Raft/Paxos) and tightly synchronized clocks, paying commit latency for it. ACID vs BASE is a spectrum, not a wall.

Pitfalls

• "Committed" that isn't durable. Turning off per-commit fsync for speed (innodb_flush_log_at_trx_commit=2, or Redis appendfsync everysec) means an OS crash or power loss silently loses acknowledged writes. Durability is a promise you can accidentally disable.
• Lost updates at Read Committed. A read-modify-write like balance = balance - 100 done as SELECT-then-UPDATE from two sessions can clobber each other. Fix with SELECT ... FOR UPDATE, an atomic UPDATE ... SET balance = balance - 100, or a higher isolation level.
• Write skew fools "Repeatable Read." Two transactions each read that at least one doctor is on-call, each concludes it's safe to go off-call, both commit — now zero on-call. Snapshot isolation permits this; only Serializable forbids it. RR is not serializability.
• MVCC version bloat. A long-running transaction (or an idle-in-transaction connection) holds an old snapshot, so VACUUM can't reclaim dead versions; tables and indexes bloat, and in Postgres you risk transaction-id wraparound. Keep transactions short.
• "BASE means I don't handle consistency." You just moved the work into your app: last-write-wins silently drops concurrent updates (shopping-cart items vanish); you need vector clocks or CRDTs to merge intent.
• Treating single-object atomicity as multi-object transactions. A document store may make each document write atomic but expose partial state across two documents unless you use its explicit transaction API.
• Mislabeling Redis as "just BASE." On a single node Redis is strongly ordered — its event loop runs each command, and each MULTI/EXEC block or Lua script, atomically with no interleaving. Its "BASE" character comes from the distributed story: asynchronous replication means a replica can lag and a failover can lose writes the primary already acknowledged. The isolation is strong; the durability-and-replication guarantee is weak.

When to use ACID, BASE, or the middle ground

Reach for a single-primary ACID database (PostgreSQL, MySQL/InnoDB, Oracle) when correctness of multi-row invariants is non-negotiable and one machine (plus replicas) can hold your write volume: money movement, inventory decrement, bookings, anything with "must never double-spend / oversell." Signals: you find yourself wanting BEGIN…COMMIT spanning several rows, foreign keys, and uniqueness. Cost: a single write leader is a scaling ceiling and a coordination point; cross-shard transactions are painful.

Prefer a BASE store (Cassandra, DynamoDB, Riak) when write volume or geographic spread exceeds one primary and the domain tolerates brief staleness: activity feeds, telemetry, session/user-profile data, product catalogs, view counters. Signals: partition-friendly access by a single key, append-heavy or independent-per-key writes, must-stay-up-during-a-partition. What you gain: horizontal write scale and availability. What it costs: you own conflict resolution, you lose easy multi-key transactions and ad-hoc joins, and reads can be stale unless you tune R + W > N.

Choose distributed SQL (Spanner, CockroachDB, YugabyteDB) when you want ACID transactions and horizontal scale across regions and can't shard the correctness away. Cost: higher commit latency (cross-region consensus) and operational complexity. Crisp rule: choose single-node ACID when invariants matter and one leader suffices; choose BASE when availability and write scale dominate and staleness is acceptable; choose distributed SQL when you refuse to give up either and will pay latency for it. "SQL = ACID, NoSQL = BASE" is now only a rough heuristic — MongoDB has had multi-document ACID transactions since 4.0, and distributed SQL is ACID at scale.

Takeaways

• ACID is what you observe; WAL (log-before-data + fsync at commit) delivers atomicity and durability, and MVCC + an isolation level delivers isolation. Learn the mechanisms, not just the acronym.
• ACID's C means "no declared invariant is violated," co-owned with your schema — it is not automatic money-conservation, and it is a different C from CAP's linearizability.
• Isolation is a dial: defaults differ across engines, and only Serializable prevents lost updates and write skew — a snapshot alone does not.
• CAP forces a C-vs-A choice only during a partition (P is unavoidable); BASE picks A and hands you the conflict-resolution bill, while distributed SQL buys ACID at scale by paying consensus latency.

Re-authored and deepened for this guide. Sources: Mohan et al., "ARIES" (ACM TODS, 1992) for WAL recovery; PostgreSQL documentation on MVCC and transaction isolation; Berenson et al., "A Critique of ANSI SQL Isolation Levels" (SIGMOD 1995) for anomalies and write skew; DeCandia et al., "Dynamo" (SOSP 2007) for BASE/eventual consistency; Gilbert & Lynch's proof of Brewer's CAP conjecture and Daniel Abadi's PACELC; Corbett et al., "Spanner" (OSDI 2012); and Kleppmann, Designing Data-Intensive Applications (chapters 7 and 9).