Developer/ConsistencyConcurrency/ACID/Fundamentals/acid-transactions-and-the-acid-vs-base-tradeoff

ACID Transactions and the ACID vs BASE Tradeoff

Concept

ACID — Atomicity, Consistency, Isolation, Durability — is the set of guarantees a relational database transaction gives you, formalized by Jim Gray in the late 1970s / early 1980s and later named as an acronym by Theo Härder and Andreas Reuter in 1983. Each letter answers a different question about what happens when a multi-step write executes:

Atomicity — a transaction is all-or-nothing. If a transfer debits one account and credits another, either both writes commit or neither does; there is no state where the debit happened but the credit didn't, even if the process crashes mid-transaction. Implemented via a write-ahead log (WAL): changes are journaled before they're applied, so a crash mid-transaction can be rolled back by replaying the log.
Consistency — a transaction moves the database from one valid state to another, never violating declared constraints (foreign keys, unique indexes, check constraints). This is the application/schema's contract, enforced by the database at commit time.
Isolation — concurrent transactions appear to execute as if they ran one at a time, even though the database is actually interleaving them for throughput. How strictly this is enforced is itself a tunable spectrum (see Related) — full serializability is the strongest and most expensive isolation level; most production databases default to something weaker (e.g. Postgres's default is Read Committed, not Serializable).
Durability — once a transaction commits, it survives a crash. The WAL is typically fsync'd to disk before the commit is acknowledged to the client, so an in-flight, uncommitted write dying with the process is expected, but an acknowledged commit disappearing is not.

Tradeoffs

Model	Guarantee	Cost
ACID (single-node relational: Postgres, MySQL/InnoDB)	Strong, well-understood correctness for free — the database enforces atomicity/isolation so the application doesn't hand-roll it	Throughput/latency ceiling from locking and WAL fsync; horizontal write scaling is hard without sacrificing some guarantee
BASE (Basically Available, Soft state, Eventually consistent — Dynamo-style NoSQL)	Scales writes horizontally across nodes with high availability, even under partition	Application must tolerate and reason about temporarily stale/conflicting reads; no free multi-step atomicity across records
Distributed ACID (Spanner, CockroachDB, YugabyteDB)	ACID guarantees across a horizontally sharded, multi-region cluster	Higher write latency than single-node ACID (consensus round trips per commit — e.g. Spanner's TrueTime-bounded commit wait); more operational complexity than either pure model

The tradeoff traces back to Eric Brewer's CAP theorem: under a network partition, a system must choose consistency or availability, and ACID's isolation/consistency guarantees are exactly the thing BASE systems give up first to keep serving reads and writes through the partition. Distributed-ACID systems like Spanner don't dodge this tradeoff — they pay for it in commit latency (consensus and clock-uncertainty waits) rather than in weakened guarantees.

When to use / when not to

Use ACID transactions for any multi-step write where partial application would corrupt state a user or another system depends on being correct — financial postings, inventory decrements tied to a purchase, anything with a foreign-key-shaped invariant across tables.
Wrap the entire multi-step write in one transaction rather than hand-rolling compensating logic for partial failure — that's what the database is for, and hand-rolled compensation is where atomicity bugs live.
Don't reach for full ACID guarantees (and pay their throughput cost) for data that's genuinely fine to be eventually consistent — a view counter, a non-critical analytics event, a cache — BASE-style storage is a legitimate and often better fit there.
Don't assume "it's in a transaction" alone gives you the isolation level you're picturing — check the database's default isolation level (often Read Committed, not Serializable) before relying on stronger guarantees like preventing all race conditions between concurrent transactions.

Common pitfall

Assuming "ACID" means "fully serializable, no anomalies possible" by default, when most production relational databases ship a weaker default isolation level for performance reasons. Postgres defaults to Read Committed, which prevents dirty reads but still allows non-repeatable reads and phantom reads between statements in the same transaction; MySQL/InnoDB defaults to Repeatable Read. A lost-update bug (two concurrent transactions both read a row, both compute an update based on that stale read, and one overwrites the other's write) can happen under either default despite both transactions being individually "ACID." The fix isn't assuming stronger isolation than what's configured — it's explicitly choosing SERIALIZABLE (or an application-level optimistic-concurrency check, e.g. a version column) for the specific operations where that race actually matters, since serializable isolation carries its own throughput cost from the retries/aborts it requires under contention.

Engineering Lens

ACID is a useful example of a guarantee that's easy to name and hard to actually reason about at the isolation-level layer, and being fluent in the distinction — knowing that "the database is ACID" doesn't automatically mean "this specific transaction is safe from every race" — is a real signal in a design review. The deeper point generalizes past databases: ACID vs BASE is one instance of the same tradeoff CAP theorem describes everywhere in distributed systems (strong guarantees cost latency/availability, weak guarantees cost application-level complexity), and the Principal-level move is picking the guarantee level per operation rather than per system — using full transactional guarantees for the operations where correctness failures are expensive, and cheaper eventual-consistency models for the ones where they aren't, instead of defaulting the entire system to one extreme.