ACID Transactions

The ACID properties — Atomicity, Consistency, Isolation, Durability — are the canonical contract a database transaction is expected to honor. The acronym was coined by Theo Härder and Andreas Reuter in their 1983 ACM Computing Surveys paper, “Principles of Transaction-Oriented Database Recovery,” consolidating ideas earlier articulated by Jim Gray (notably his 1981 VLDB paper “The Transaction Concept”). For four decades ACID has been the lingua franca of database correctness: every relational database advertises its degree of compliance, every textbook teaches the four letters, and every distributed-systems debate eventually returns to “but does it preserve ACID?” The properties are not equally well-understood — A and D are conceptually tidy; I has a deep and confusing zoology of weak forms (Berenson et al. 1995); the C is the most-misunderstood letter in distributed systems, repeatedly conflated with the C of CAP Theorem despite being a different concept entirely. This note unpacks each letter, walks through a worked bank-transfer example, and contrasts ACID with its NoSQL counterpart BASE Properties.

1. Plain-Language Statement

A transaction is a unit of work — typically a small program that reads and writes some database rows — that the database is asked to execute as a single logical operation. The ACID properties are the four guarantees the database makes about that execution:

• Atomicity — the transaction either runs entirely or not at all. There is no partial state visible to anyone (including the application or other transactions) where some of the writes happened and others didn’t. If anything goes wrong mid-transaction (an error, a power failure, a crash), the database rolls back all changes; the database state reverts to exactly what it was before the transaction started. The transaction is treated as an atom — indivisible.

• Consistency — the transaction takes the database from one valid state to another. “Valid” here means the database satisfies all of its declared invariants: foreign key constraints, uniqueness constraints, check constraints, and (most importantly) any application-level invariants the developer has expressed. If a transaction would violate an invariant, the database refuses it (returns an error). This is the most-misunderstood of the four letters and is not the same concept as the C in CAP Theorem — see §6.1.

• Isolation — concurrent transactions appear to execute as if they were running one at a time, in some serial order. Even if N transactions are physically interleaved at the same instant, each one observes the database as if no other transactions were running. The database hides the concurrency. (In practice, full isolation is expensive, so most databases offer isolation levels — weaker forms that trade isolation for performance. See §3.3.)

• Durability — once a transaction has been committed (the database has acknowledged success), its effects survive. Power loss, crashes, kernel panics, disk failures — none of these may erase a committed transaction’s effects. The database persists committed state to non-volatile storage (typically disk) before acknowledging the commit. The classical implementation primitive here is the Write-Ahead Log.

The four properties form a tightly-coupled package. You don’t really have “atomicity without durability” — an atomic-but-non-durable change is just a temporary in-memory value. You don’t have “isolation without atomicity” — what would isolating a half-applied transaction even mean? Working database systems implement all four together, with each letter’s mechanism reinforcing the others.

2. Formal Definition

The Härder + Reuter 1983 paper is the canonical formalization. Each property is defined in terms of the database’s externally-observable behavior:

Atomicity: for any transaction T, the effects on the database are exactly the union of all writes performed by T (if T commits) or exactly the empty set (if T aborts). There is no partially-committed state. Formally, denoting the database state as a function of time, state(t_after_commit) includes all writes of T; state(t_after_abort) is identical to state(t_before_T_started) modulo other transactions’ effects.

Consistency: if the database satisfies a set of integrity predicates I before T starts, and T is correctly written (i.e., when run alone, it preserves I), then the database satisfies I after T commits. Note that this is a property jointly of the database and the application — the database alone cannot guarantee consistency in this sense; the application must write transactions that, when executed in isolation, preserve invariants. The database’s role is to ensure (via the other three letters) that the application’s per-transaction reasoning composes correctly under concurrency and failure.

Isolation: the result of executing a set of transactions concurrently is equivalent to some serial execution of those same transactions. This property is called serializability and is the gold-standard isolation level. Formally, there exists a permutation π of the transactions such that the database state and the values returned to each transaction are identical to running them in the order π(1), π(2), …, π(n) one after another. (Most databases default to weaker than serializability for performance — see §3.3.)

Durability: if T commits at time t, then for any subsequent time t' > t and any system failure mode the database is designed to handle (power loss, crash, controlled restart), the database state at t' reflects T’s writes. Recovery is the mechanism that makes this true: on restart, the database replays its log to reconstruct the committed state.

The 1983 paper goes further, defining a transaction abstraction as a sequence of read/write operations bracketed by a BEGIN and a COMMIT or ROLLBACK, and arguing that any database that wants to expose multi-statement transactions must implement all four properties or risk corruption. The formalism is dense but the intuition is straightforward: a transaction is supposed to behave like a single instantaneous, indivisible, durable, isolated operation, even when it isn’t actually any of those things at the implementation level.

3. The Four Properties in Detail

3.1 Atomicity

The classic illustration: a bank transfer of $100fromaccountAtoaccountBisatransactioncontainingtwowrites—debitAby$100, credit B by $100.Withoutatomicity,thedatabasemightcommitthedebitbutcrashbeforethecredit,leaving$100 missing from the system. Atomicity guarantees that either both writes land or neither does.

Implementation. Atomicity is conventionally implemented via the Write-Ahead Log (WAL) and a transaction log that records each transaction’s intended changes before they are applied. The log entry sequence for the bank transfer is:

LOG: BEGIN T123
LOG: T123 UPDATE accounts SET balance = balance - 100 WHERE id = A  [old=500, new=400]
LOG: T123 UPDATE accounts SET balance = balance + 100 WHERE id = B  [old=200, new=300]
LOG: COMMIT T123

The log is force-flushed to durable storage (fsync) before the COMMIT acknowledgment is returned. If the system crashes before COMMIT, recovery reads the log, sees no COMMIT for T123, and undoes T123’s changes (by applying the recorded “old” values). If the crash is after COMMIT, recovery redoes any T123 writes that hadn’t yet propagated from the log to the data pages. The canonical algorithm — ARIES (Mohan et al. 1992 ACM TODS) — formalizes this undo/redo logic.

Subtle point: rollback semantics. “Rollback” doesn’t mean “physically undo the writes that already happened on disk.” It means “make the post-rollback database state observationally equivalent to the pre-T state.” Many databases implement this with undo logs that record the previous values; some use MVCC (multi-version concurrency control) where the old version is simply not yet garbage-collected and continues to be the visible version after rollback.

Atomicity in distributed transactions. Single-machine atomicity is solved. Distributed atomicity — a transaction touching N machines must commit on all N or none — requires Two-Phase Commit (2PC) or a more sophisticated commit protocol (Paxos Commit, Calvin’s deterministic execution). 2PC has well-known failure modes: a coordinator crash after PREPARE but before COMMIT can leave participants blocked indefinitely. Modern systems either use timeouts + presumed-abort (PostgreSQL distributed transactions), Paxos-replicated coordinators (Spanner), or sidestep 2PC by partitioning data so cross-shard transactions are rare.

3.2 Consistency — The Most-Confused Letter

Consistency in ACID means: the application has declared a set of invariants (foreign keys, check constraints, uniqueness, “balances ≥ 0”), and a committed transaction always leaves the database satisfying those invariants.

Crucially, this C is NOT the C of CAP Theorem. The CAP-C is linearizability — a cluster-wide property about whether all replicas reflect the latest write. The ACID-C is an application-defined invariant property of a single transaction’s effect on a logical database state. They are different concepts that share an unfortunate letter; mixing them up is one of the most common interview tells. Brewer himself wrote in his 2012 retrospective (“CAP Twelve Years Later”): “The ‘C’ in ACID is different from the ‘C’ in CAP.”

Implementation. Most databases enforce a subset of ACID-C automatically: declared CHECK constraints (balance ≥ 0), foreign keys, NOT NULL, UNIQUE — these are evaluated at commit time, and a violating transaction aborts. Application-level invariants (“a chess game has at most two players,” “a comment thread depth ≤ 100”) are not visible to the database and must be enforced either with explicit constraints or by careful transaction design. The C of ACID is a shared responsibility: the database enforces declared constraints; the application must not ask the database to violate undeclared ones.

This is why some critics (notably Joe Hellerstein in his SIGMOD writeups) have argued the C is the weakest letter — it’s just “the database doesn’t break the rules you told it about.” The “real” guarantees are A, I, and D; C is a tautology bolted on to make the acronym pronounceable.

3.3 Isolation — The Zoo of Levels

Full isolation means serializability: the result of running transactions concurrently is indistinguishable from running them one at a time in some serial order. Serializability is expensive — it generally requires either pessimistic locking (which limits throughput) or optimistic concurrency control with abort-and-retry (which limits throughput differently). Most databases therefore offer isolation levels that relax serializability for performance.

The ANSI SQL standard (1992) defined four levels:

• Read Uncommitted — a transaction can see writes of other uncommitted transactions (“dirty reads” allowed).
• Read Committed — a transaction only sees writes of committed transactions (no dirty reads), but successive reads of the same row may return different values if another transaction commits between them.
• Repeatable Read — within a transaction, every read of the same row returns the same value (snapshot of read rows is stable), but new rows may appear in range queries (phantom reads).
• Serializable — full serializability, no anomalies.

The 1995 SIGMOD paper “A Critique of ANSI SQL Isolation Levels” by Berenson, Bernstein, Gray, Melton, O’Neil, and O’Neil is the canonical reference for what’s wrong with this taxonomy. The paper argues that the ANSI levels are defined in terms of three specific anomalies (P0 dirty write, P1 dirty read, P2 non-repeatable read, P3 phantom) but miss several important ones — most famously write skew and lost updates. The Berenson et al. paper proposes new levels including Snapshot Isolation (which is what Oracle, Postgres, MySQL InnoDB, and most modern databases actually implement under the “Repeatable Read” or “Serializable” name).

Snapshot Isolation (SI) — each transaction sees a consistent snapshot of the database as of the moment it began; writes are checked at commit time for first-committer-wins conflicts. SI prevents most anomalies including dirty reads, non-repeatable reads, and phantom reads, but it is not serializable — it allows write skew. The classic write-skew example: two doctors are on call; each transactionally checks “are there at least 2 doctors on call?” (yes), then sets their own status to off-call. Both transactions commit; now zero doctors are on call. SI didn’t catch this because each transaction’s read set didn’t conflict with the other’s write set, even though the aggregate invariant was violated.

Serializable Snapshot Isolation (SSI) (Cahill et al. SIGMOD 2008) — a refinement that detects “dangerous structures” in the read-write conflict graph and aborts one of the conflicting transactions to enforce true serializability while keeping SI’s mostly-non-locking performance. Postgres uses SSI for its SERIALIZABLE level since 9.1.

Database defaults differ.

• PostgreSQL defaults to Read Committed; REPEATABLE READ is actually Snapshot Isolation; SERIALIZABLE is SSI.
• MySQL InnoDB defaults to Repeatable Read, which is Snapshot Isolation with some extensions (gap locks for range queries to prevent phantoms — different semantics from Postgres SI under the same name).
• Oracle: defaults to Read Committed; SERIALIZABLE is actually Snapshot Isolation (Oracle has no true serializability mode).
• SQL Server: defaults to Read Committed (not snapshot); has SNAPSHOT and SERIALIZABLE modes.

The takeaway: two databases both saying “Repeatable Read” or “Serializable” do not necessarily mean the same thing. Always check the manual. (The Adya 1999 PhD thesis is the rigorous reference for what each level actually guarantees.)

3.4 Durability

A committed transaction’s effects must survive all anticipated failure modes. The standard set of failures: power loss, controlled OS shutdown, OS crash, database process crash, disk-controller crash. Durability does not mean surviving disk-media failure — for that, replication or backups are needed (see Leader-Follower Replication Architecture).

Implementation. The standard implementation is the Write-Ahead Log (WAL): every change is appended to the log, the log is fsync()-ed to durable storage, and only then is the transaction acknowledged as committed. On crash, recovery reads the log and reconstructs the committed state. The data pages themselves can be updated lazily — the log is the source of truth.

The fsync question. “Durable” depends on what fsync() actually does on your hardware. A naive fsync() on consumer SSD with on-board write cache can return before data hits flash; a power loss in that millisecond loses “committed” data. Production databases use fdatasync + write-cache disable, or rely on battery-backed RAID controllers, to make fsync truly durable. PostgreSQL’s synchronous_commit=on (default) and wal_sync_method=fdatasync are the relevant knobs.

Group commit. Naive durability is slow — every commit costs an fsync, and fsync on rotational disk takes ~10ms. Group commit batches many transactions’ WAL writes into one fsync, amortizing the cost. Throughput climbs from ~100 commits/second per spindle to thousands.

Replication and durability. Single-node durability is solved. Cluster durability — a committed transaction must survive even if the primary node’s disk dies — requires replication. The standard is to wait for at least one replica’s WAL to also be fsynced before acknowledging commit (synchronous replication / synchronous_commit=on in Postgres terms). Spanner takes this further with Paxos-replicated commit logs across data centers (Corbett et al. OSDI 2012). See Distributed SQL Database System Design for the general pattern.

4. Origins

Pre-history (1970s)

The transaction concept itself predates ACID by a decade. Jim Gray’s 1981 VLDB paper “The Transaction Concept: Virtues and Limitations” is the first place the four ideas are clearly articulated together — Gray credits IBM’s System R prototype work in the mid-1970s for the ground truth. The IBM System R team (which included Gray, Don Chamberlin, Pat Selinger, and others) built the first functioning multi-user RDBMS with full transaction semantics; the internal IBM technical reports document each property’s implementation.

What System R demonstrated was that concurrency control (the I) and recovery (the A and D) could be solved together in a single coherent system. Before System R, database researchers knew about locking and about logging, but the full set of guarantees — and their inter-implementation — was murky. System R made it concrete.

The acronym (1983)

Theo Härder and Andreas Reuter published “Principles of Transaction-Oriented Database Recovery” in ACM Computing Surveys 15(4), December 1983. The paper is primarily about recovery — the durability and atomicity machinery — but in §1.3 (“The Concept of a Transaction”) they consolidate the four properties under the ACID acronym. The acronym was their contribution; the underlying concepts were Gray’s and System R’s.

Härder and Reuter’s framing is striking in retrospect — they argue that the four properties together constitute the contract a database makes with the application, and that any system that fails any one of them is unsuitable as a transaction processor. The argument holds up: nearly every database advertised as “ACID” today is following the 1983 contract, and most “non-ACID” databases (key-value stores, NoSQL) are explicitly waiving one or more letters.

The 1990s — formalization of weak isolation

The 1995 Berenson et al. SIGMOD paper “A Critique of ANSI SQL Isolation Levels” sharpened the I letter, identifying the gaps in the ANSI 1992 standard and introducing Snapshot Isolation as a practical sweet spot. Atul Adya’s 1999 MIT PhD thesis “Weak Consistency: A Generalized Theory and Optimistic Implementations for Distributed Transactions” gave the rigorous theoretical framework that all modern formal isolation work builds on.

The 2000s — the NoSQL counter-movement

BASE Properties (Pritchett 2008, “BASE: An Acid Alternative”) was the AP-side counter to ACID. The 2007 Dynamo paper, the 2008 Cassandra paper, and the 2010 Riak project all positioned themselves as deliberately non-ACID in exchange for partition-tolerance and availability. The mid-2010s saw a counter-counter-movement: the “NewSQL” wave (Spanner, CockroachDB, TiDB) showed you could have ACID and horizontal scale, just at very high engineering cost.

Recent (2014–present)

Bailis et al.’s 2014 VLDB paper “Highly Available Transactions: Virtues and Limitations” formally analyzes which subsets of ACID are achievable under partition. The answer: most of ACID can coexist with availability except for serializability and snapshot isolation. This nuance is increasingly important as databases offer per-transaction or per-table isolation knobs.

5. Worked Example — Bank Transfer with All Four Properties

The canonical worked example. Two checking accounts:

accounts table:
  id  | balance
  ----+---------
   A  | 500.00
   B  | 200.00

Application transaction: transfer $100 from A to B. The application code:

BEGIN;
  UPDATE accounts SET balance = balance - 100 WHERE id = 'A';
  UPDATE accounts SET balance = balance + 100 WHERE id = 'B';
COMMIT;

The application has declared an invariant: balance ≥ 0 (a CHECK constraint).

Atomicity in action

Imagine the database crashes after the first UPDATE but before the second. Without atomicity: A’s balance is 400, B’s is 200, and $100 has vanished from the system. The application’s books would not balance. With atomicity: on recovery, the database sees the WAL has no COMMIT for this transaction, undoes the first UPDATE, and the database state on restart is A=500, B=200 — exactly as if the transaction never started.

Consistency in action

Suppose A’s balance is only 50 and the application tries to transfer 100 anyway. The first UPDATE would set A’s balance to -50, violating the CHECK constraint. The database raises an error, the transaction is rolled back, and A’s balance remains 50. The system’s invariant (balance ≥ 0) is preserved because the database refused to commit the violating transaction. Note: this is application-defined consistency — the database knows about the CHECK constraint because the schema declared it. An application invariant the database doesn’t know about (“transfers must be approved by a manager during business hours”) would not be enforced by ACID-C.

Isolation in action

Suppose two concurrent transfers run at the same instant:

• T1: transfer 100 from A to B
• T2: transfer 50 from A to C

Without isolation, the interleaving might be:

1. T1 reads A.balance = 500
2. T2 reads A.balance = 500
3. T1 writes A.balance = 400 (500 - 100)
4. T2 writes A.balance = 450 (500 - 50, based on its earlier read)
5. T1 writes B.balance = 300
6. T2 writes C.balance = +50

Now A’s balance is 450 (T2’s write overwrote T1’s), but the system has paid out 100 to B and 50 to C — total $150paid,only$50 deducted. Lost update. Isolation prevents this. At Serializable, T1 and T2 would either run sequentially or one would abort and retry. At Snapshot Isolation, the second-committer would detect a conflict on row A and abort. Either way, the lost update doesn’t happen.

Durability in action

After COMMIT returns successfully to the application, the application sends an acknowledgment to the user: “Transfer complete.” The user closes the laptop. A power outage hits the data center. When the database restarts, the WAL is replayed, the transferred funds are present in the on-disk pages. The user, on opening their laptop the next day, sees A=400, B=300. The transfer survived the outage because durability was honored.

What happens if you remove a letter

• No A: partial transfers leak. $100 vanishes or is double-credited.
• No C: the database accepts transfers that drive A’s balance negative; the bank’s books are wrong.
• No I: concurrent transfers produce lost updates and double-spending.
• No D: committed transfers vanish on power loss; users get angry support tickets.

Each letter exists to close one specific failure mode. The acronym is not arbitrary — it’s the minimal set of guarantees that make a database safe for money.

6. Common Misconceptions

6.1 The C in ACID and the C in CAP are the same thing

The most common confusion. They are completely different:

• ACID-C = the database does not commit transactions that violate declared (or application-enforced) integrity constraints. It is a per-transaction property about effect on database state.
• CAP-C = linearizability across cluster replicas. It is a cluster-wide property about visibility of writes.

A system can be ACID-C without being CAP-C. Example: MySQL with asynchronous replication. The primary enforces ACID locally; reads from a stale replica can return data older than the latest committed transaction (not linearizable). ACID-C holds; CAP-C does not.

6.2 ACID and BASE are mutually exclusive

A common framing is “you must choose ACID or BASE.” This is mostly false. NewSQL systems (Spanner, CockroachDB, TiDB, FaunaDB) provide ACID transactions across distributed clusters with BASE-grade availability. Many “BASE” systems offer ACID-style guarantees per-key or per-shard (DynamoDB transactional API; Cassandra Lightweight Transactions). The distinction is now a sliding scale, not a binary, and most modern systems sit somewhere in between.

6.3 “Repeatable Read” means the same thing in every database

It does not. As §3.3 detailed: PostgreSQL’s REPEATABLE READ is Snapshot Isolation (no phantoms in your read set, but write skew possible). MySQL InnoDB’s REPEATABLE READ is SI plus next-key locking that prevents phantoms in range queries — closer to serializability but still not actually serializable. Oracle’s SERIALIZABLE is Snapshot Isolation (no true serializability mode at all). The names are aspirational; the actual semantics are vendor-specific and require reading the manual.

6.4 ACID is a property of databases

ACID is a property of transactions, which a database may or may not implement, and which an application may or may not use. PostgreSQL, MySQL InnoDB, and Oracle all support ACID transactions, but each has many operations that are not in a transaction (autocommit mode, individual reads, replication, vacuum). Saying “PostgreSQL is ACID” is shorthand for “PostgreSQL supports ACID transactions”; whether a particular operation is ACID depends on whether the application wrapped it in a transaction.

6.5 Durability means data lives forever

Durability in ACID is the modest guarantee that a committed transaction’s effects survive system crashes. It does not protect against:

• Disk failure (need RAID or replication)
• Data center destruction (need geographic replication)
• Bit rot (need checksums + scrubbing)
• Operator error (need backups)
• Software bugs that corrupt data (need backups + version history)

The full “your data is safe” stack is much bigger than ACID-D.

6.6 NoSQL means non-ACID

The first wave (Dynamo, Cassandra, Riak, original MongoDB) was indeed largely non-ACID. The current wave is ACID-equipped: MongoDB has multi-document ACID transactions since 4.0 (2018); DynamoDB has TransactWriteItems and TransactGetItems; Cassandra has Lightweight Transactions for single-row CAS; CockroachDB and Spanner are fully ACID across clusters. “NoSQL” is no longer a reliable proxy for “non-ACID.”

7. Real-World Implications

7.1 Architectural choice: ACID vs eventual

Choosing whether a particular subsystem needs ACID transactions is one of the central architectural decisions in any distributed system. The rule of thumb: anything involving money or invariants people will sue over needs ACID; anything where eventual consistency is acceptable can be BASE (see BASE Properties).

Concretely:

• Bank ledger, payment processing, inventory decrement, order placement, account creation — ACID.
• Like counter, view counter, social timeline, recommendation cache, search index — BASE / eventual.
• Email send, SMS, push notification — depends on idempotency and exactly-once-vs-at-least-once requirements.

7.2 Hybrid systems

Most large systems are hybrids: an ACID core (typically Postgres or a NewSQL database) for the small set of strongly-consistent data, and BASE periphery (Redis, Cassandra, Kafka) for high-throughput eventually-consistent flows. Order placement might write to Postgres synchronously, then publish to Kafka for async fanout to recommendation systems and analytics.

7.3 The cost of ACID at scale

ACID at single-machine scale is cheap. ACID at distributed scale is expensive: distributed serializability requires either a global commit log (Spanner’s TrueTime + Paxos), deterministic pre-ordering (Calvin), or distributed locking + 2PC. All are operationally complex and incur cross-region latency on every commit. Many systems opt for “ACID per shard” + “eventual across shards,” which captures most of the benefit.

7.4 Influence on schema design

ACID-enabled systems encourage normalized schemas (foreign keys, joins) because the database can ensure referential integrity. ACID-disabled systems encourage denormalized schemas (embedded documents, duplicated data) because referential integrity is not enforced — you must avoid needing it. The database choice ripples into the data model.

8. Variants and Extensions

8.1 BASE — Basically Available, Soft state, Eventual consistency

The acid-base chemistry pun coined by Brewer (~1998–2000), formalized by Pritchett 2008. The explicit BASE-vs-ACID framing structures the NoSQL discussion. See BASE Properties.

8.2 BASE + tunable consistency

Modern systems frequently offer per-operation knobs: Cassandra’s consistency levels, DynamoDB’s strongly-consistent reads, Cosmos’s five named consistency levels. The result is “BASE by default, ACID-flavored on demand.”

8.3 NewSQL

A class of distributed databases providing ACID transactions across horizontally-scaled clusters. Spanner (Google, OSDI 2012), CockroachDB (2014), TiDB (2015), FaunaDB, YugabyteDB. The trick is replicating the commit log via Paxos/Raft and using either TrueTime (Spanner) or HLC (Hybrid Logical Clocks; CockroachDB) to maintain serializability.

8.4 External Consistency / Strict Serializability

Spanner’s claimed property: not only are transactions serializable, but the serialization order matches real time — if T1 commits before T2 starts (in real time), T1 appears before T2 in the serialization. This is strictly stronger than serializability, which only requires some serial order. External consistency is what TrueTime buys: hardware-derived global time bounds let Spanner produce a correct real-time order without coordination.

8.5 SAGAs

For long-running multi-step business processes (booking a trip = flight + hotel + car), 2PC is impractical because participants would hold locks for hours. SAGAs (Garcia-Molina + Salem 1987) decompose the transaction into a sequence of local sub-transactions, each with a compensating action. If step 5 fails, run compensations 4-3-2-1 in reverse to undo. SAGAs trade strict isolation for liveness and are the standard for cross-service business workflows in microservice architectures.

8.6 Read-only optimization

Many ACID databases offer optimized read-only transactions that don’t need locks or two-phase commit — they read from a snapshot. Spanner’s “read-only transactions at a chosen timestamp” are sufficient for analytics queries without slowing down the write path.

9. Pitfalls in Application

1. Confusing ACID-C with CAP-C. As §6.1 covered. They share a letter and nothing else. Mixing them in design discussions or interviews is a tell that the candidate hasn’t internalized either. Always say “ACID consistency” or “linearizability” (CAP-C) rather than just “consistency” when the audience might be confused.

2. Assuming default isolation is serializable. Most databases default to Read Committed or Snapshot Isolation, both of which permit anomalies (write skew, lost updates of certain kinds). Applications that assume serializability and don’t request it can have data integrity bugs that only manifest under load. Either explicitly set SERIALIZABLE (and pay the cost) or design transactions to be safe under the default level (idempotent, single-row, CAS-based).

3. Assuming “Repeatable Read” prevents all anomalies. Repeatable Read in Postgres (= SI) does not prevent write skew. Two transactions reading non-overlapping rows but writing under a shared invariant can both commit and violate the invariant. Either move the invariant check into a single row update, use SELECT FOR UPDATE to acquire row locks, or use SERIALIZABLE.

4. Over-relying on ACID across services. ACID is for intra-database transactions. Microservice architectures with ACID-per-service end up with cross-service inconsistencies that ACID does not solve. The standard answer is SAGAs or the outbox pattern (write transactionally to your local DB and a local outbox table; a separate process replicates the outbox to other services).

5. Forgetting fsync semantics. “Durable” means fsync was honored. Cheap consumer SSDs, virtualized cloud volumes, and NFS mounts can lie about fsync. Production deployments need to verify durability properties — pull the power on a test cluster mid-write and see what survives. Surprisingly many “ACID” deployments fail this test because of misconfigured storage.

6. Treating distributed transactions as cheap. A 2PC across 5 services adds a cross-network round trip and can block on coordinator failure. Spanner-style commits add cross-region latency (~100ms in worst case). Many designs assume distributed ACID is “just like local ACID, with more nodes” — it isn’t. The right approach is to partition data so transactions stay within one shard, keeping cross-shard transactions rare.

7. Long-running transactions. Holding a transaction open for minutes (waiting on user input, external API calls) holds locks, blocks other transactions, and bloats the WAL / undo log. Always close transactions quickly; never wait on external systems while holding a transaction. The pattern is: read state, release transaction, do external work, re-open a short transaction with optimistic concurrency to commit the result.

8. Phantom reads and predicate locks. A “phantom” is a row that wasn’t in your initial range query but appears on a re-read because another transaction inserted it. Snapshot Isolation prevents phantoms within the snapshot; range locking (MySQL’s gap locks) prevents inserts in a locked range; SSI detects predicate-read/insert conflicts. The right defense depends on the database; the wrong assumption (“my range query is stable”) is a common bug.

10. Common Interview Discussion Points

• “What does ACID stand for and what does each letter mean?” Atomicity (all-or-nothing), Consistency (preserves application invariants — and not CAP-C), Isolation (concurrent transactions appear serial), Durability (committed effects survive crashes). Cite Härder + Reuter 1983.

• “What’s the difference between the C in ACID and the C in CAP?” ACID-C is application invariants; CAP-C is linearizability. Different concepts, same letter. Brewer himself acknowledged the confusion in 2012.

• “What isolation levels are there and what anomalies does each prevent?” Read Uncommitted (allows dirty reads), Read Committed (no dirty reads), Repeatable Read (no non-repeatable reads, may allow phantoms depending on database), Serializable (no anomalies). Note that “Repeatable Read” varies by database; cite Berenson et al. 1995.

• “What’s Snapshot Isolation and what anomaly does it allow?” Each transaction sees a stable snapshot from its start time; commits with first-committer-wins on conflicts. Allows write skew because reads of non-overlapping rows don’t conflict even if they share an invariant.

• “How does a database implement durability?” Write-Ahead Log + fsync before commit acknowledgment. Cite ARIES (Mohan et al. 1992). Mention group commit for throughput.

• “How do distributed databases preserve ACID?” Sharding + 2PC for cross-shard, replicated commit log via Paxos/Raft, optionally TrueTime (Spanner) or HLC (CockroachDB) for external consistency.

• “When would you choose BASE over ACID?” When availability and write throughput matter more than strong consistency, and when the application can tolerate eventual convergence — counters, social feeds, shopping carts, analytics ingest.

• “What’s a real-world bug caused by weak isolation?” Classic write-skew examples: doctor on-call schedule, double-spending, double-booking flights. Or Aphyr’s Jepsen tests of various NoSQL systems showing data loss / inconsistencies under partition.

11. Quick-Reference Cheat Sheet

For interview recall:

• Acronym: Atomicity, Consistency, Isolation, Durability.
• Origin: Härder + Reuter 1983 ACM CSUR coined the term; Jim Gray 1981 VLDB articulated the concepts; IBM System R was the first implementation.
• A: transaction is all-or-nothing; rollback semantics; implemented via WAL + undo logs.
• C: application invariants preserved (NOT linearizability — different from CAP-C). Mostly a tautology — the database doesn’t break the rules you told it about.
• I: concurrent transactions appear serial; isolation levels (Read Uncommitted, Read Committed, Repeatable Read, Snapshot Isolation, Serializable) trade strictness for performance.
• D: committed effects survive crashes; implemented via WAL + fsync.
• Canonical reference for I: Berenson et al. 1995 SIGMOD “A Critique of ANSI SQL Isolation Levels.”
• Distributed extension: Two-Phase Commit for cross-node atomicity; Paxos/Raft for replicated commit logs (Spanner, CockroachDB).
• Counterpart: BASE Properties for AP-side weak consistency.

12. The History of ACID at IBM

A piece of context useful for interviews and for understanding why the four properties cluster together. IBM’s System R project, started in 1973 and led by Don Chamberlin and Ray Boyce (with Jim Gray contributing heavily on transaction processing), built the first relational database that combined a query optimizer, transaction manager, and recovery manager in one system. Before System R, database research had isolated work on each component. System R’s contribution was showing they had to be designed together — the recovery manager has to know about transactions, the lock manager has to coordinate with the recovery log, the query optimizer has to respect transactional read consistency.

The four ACID letters reflect this co-design. Atomicity needs Durability (you can’t roll back if the WAL isn’t durable). Isolation needs Atomicity (you can’t isolate a partially-applied write). Durability builds on the WAL that Atomicity uses. Consistency is the application-facing promise that the other three letters’ machinery delivers. The acronym is not arbitrary alphabet soup; it is the minimal set of co-designed properties that make a concurrent multi-user database safe.

The IBM technical reports from the System R era (RJ-1738, “The Recovery Manager of the System R Database Manager,” is one of many) document each component in detail. They are still relevant today: the algorithms in Postgres, MySQL, Oracle, and SQL Server all trace lineage to System R via Gray’s papers, the ARIES papers, and the textbook lineage they spawned. ACID is, in a meaningful sense, the IBM System R contract — refined and standardized over the following decades but still recognizable as the same thing.

13. See Also

• CAP Theorem — the cluster-side counterpart; ACID-C and CAP-C are different
• BASE Properties — the AP-side alternative bundle
• PACELC Theorem — extends CAP by adding the latency-vs-consistency axis
• Two-Phase Commit — distributed atomic commit primitive used to extend ACID across nodes
• Raft — consensus algorithm used by NewSQL systems for replicated commit logs
• Paxos High-Level — the older consensus algorithm used by Spanner and Chubby
• Write-Ahead Log — the durability + atomicity primitive
• Distributed SQL Database System Design — the architectural pattern that scales ACID
• Distributed Key Value Store System Design — the BASE-flavor alternative
• Leader-Follower Replication Architecture
• Multi-Leader Replication Architecture
• Leaderless Replication Architecture
• SWE Interview Preparation MOC
• Major System Designs MOC