ACID, One Letter at a Time

#TL;DR

ACID is the four-letter acronym — Atomicity, Consistency, Isolation, Durability — that distinguishes a database from a file. It’s a promise: transactions either complete fully or not at all; the data stays internally valid; concurrent transactions don’t see each other’s half-done work; committed data survives crashes. The implementation behind each letter is a separate deep topic. Atomicity and Durability rely on write-ahead logging. Isolation is built with locks or multi-version concurrency control. Consistency is half a constraint enforcement story and half “everything else is working.” Every modern relational database spends most of its engineering budget making these four promises hold under load.

#Where ACID Came From

The term ACID was coined by Theo Härder and Andreas Reuter in their 1983 survey paper Principles of Transaction-Oriented Database Recovery. The underlying ideas were older — Jim Gray’s papers on transactions date from the mid-1970s, and System R had been working out the implementation since 1974 — but Härder and Reuter gave the properties a memorable name and a tidy taxonomy.

The paper was framed as survey, not as specification. But the acronym stuck, and “ACID-compliant” became shorthand for “we take transactions seriously.”

#A (Atomicity): All or Nothing

A transaction executes in its entirety or not at all. No partial state is ever visible to other transactions or persisted to disk.

The canonical example:

BEGIN;
  UPDATE accounts SET balance = balance - 500 WHERE id = 1;
  UPDATE accounts SET balance = balance + 500 WHERE id = 2;
COMMIT;

If the system crashes between the two UPDATEs, the transaction must either (a) finish — both updates land — or (b) be rolled back as if it never happened. It must never be the case that money disappeared from account 1 without appearing in account 2.

How atomicity is implemented: the write-ahead log (WAL).

Before applying any change to the database’s actual data files, the database writes a record of the change to an append-only log file:

LSN 1001: BEGIN tx 7
LSN 1002: UPDATE accounts SET balance=balance-500 WHERE id=1  (old: 1000, new: 500)
LSN 1003: UPDATE accounts SET balance=balance+500 WHERE id=2  (old:  200, new: 700)
LSN 1004: COMMIT tx 7

The actual data pages may or may not be updated on disk yet. The log is what matters. On crash recovery, the database:

Scans the log from the last checkpoint.
For each transaction that has a COMMIT record — re-applies its changes (redo).
For each transaction that doesn’t have a COMMIT record — rolls back its changes (undo).

After this recovery process, the database is in a state where every committed transaction is durable and every incomplete transaction has been erased. Atomicity is preserved across crashes.

The WAL is the single most important mechanism in database engineering. Every major relational database has one — Postgres calls it WAL, MySQL/InnoDB calls it the redo log, Oracle calls it the redo log, SQL Server calls it the transaction log. They’re all the same pattern.

#C (Consistency): Valid States Only

A transaction moves the database from one valid state to another valid state. Defined constraints (foreign keys, unique constraints, check constraints, triggers) are never violated in a committed state.

C is the odd letter out in ACID. The other three are enforced by the database’s internal machinery. Consistency is partly the database’s job and partly the application’s.

What the database enforces:

PRIMARY KEY uniqueness.
FOREIGN KEY referential integrity.
NOT NULL, CHECK, UNIQUE constraints.
Any triggers defined on the table.

What the database does not enforce:

Business rules encoded only in application logic.
Cross-service invariants (e.g., the total balance should equal the sum of transactions).
Semantic correctness (the schema might permit negative account balances if no CHECK prevents it).

If your transaction leaves the database in a state that technically passes all constraints but is semantically invalid — deducting money from an account without creating a corresponding transaction record somewhere else — the database says “consistent.” You say “inconsistent.” Both of you are correct about different things.

The practical meaning of C in ACID is: if you’ve defined the constraints the database can check, it’ll check them. Anything else is on you.

#I (Isolation): Concurrent Transactions Don’t Corrupt Each Other

Concurrent transactions behave as if they ran one at a time. No transaction sees another transaction’s intermediate state.

This is the hardest letter to deliver in practice, and the one where databases offer tunable tradeoffs.

#The Anomalies

Four classical concurrency anomalies, each harder to prevent than the last:

Dirty read — transaction A reads data that transaction B has written but not yet committed. If B rolls back, A has seen phantom data.
Non-repeatable read — A reads a row, B modifies and commits the row, A reads it again and sees different values.
Phantom read — A runs a query returning a set of rows, B inserts a new row matching the query’s criteria and commits, A reruns the query and sees the new row.
Serialization anomaly / write skew — two transactions each read the same data, make decisions based on it, and write back — with the combined effect that no serial order of the two transactions could have produced.

#Isolation Levels

SQL defines four isolation levels, each preventing more anomalies (and generally costing more):

Level	Dirty read	Non-repeatable	Phantom	Write skew
Read Uncommitted	allowed	allowed	allowed	allowed
Read Committed	prevented	allowed	allowed	allowed
Repeatable Read	prevented	prevented	depends	allowed
Serializable	prevented	prevented	prevented	prevented

Read Uncommitted — reads can see uncommitted data. Almost never what you want. Most databases don’t implement it and silently upgrade to Read Committed.
Read Committed — the default in Postgres and Oracle. Reads only see committed data, but the same query can return different results within a transaction.
Repeatable Read — the default in MySQL/InnoDB. Within a transaction, reads are stable. Whether phantoms are prevented depends on the database.
Serializable — the strictest. Transactions behave exactly as if they ran one at a time. Expensive; most applications don’t use it.

The tradeoff is concurrency vs. correctness. Higher isolation gives you stronger guarantees but reduces throughput and increases latency. Most production systems run at Read Committed and accept that some anomalies can happen — and carefully design the application to minimize the impact.

#Two Implementation Strategies

There are two fundamental ways to implement isolation:

Two-phase locking (2PL). Every row a transaction reads gets a shared lock. Every row it writes gets an exclusive lock. Locks are held until the transaction commits. Conflicts between transactions cause one of them to wait.

Pros: conceptually simple, strong guarantees. Cons: readers block writers, writers block readers, deadlocks are possible, throughput suffers under contention. Used by MySQL/InnoDB for certain isolation levels, and traditional SQL Server.

Multi-Version Concurrency Control (MVCC). Every write creates a new version of the row; old versions are kept as long as any transaction might still need them. Readers never block writers, because they can always see the version that was current when their transaction started. Writers never block readers.

Pros: much higher concurrency for read-heavy workloads. Cons: disk space overhead (old row versions accumulate and need periodic cleanup, aka “vacuuming” in Postgres), more complex implementation, subtler semantics for certain isolation levels.

Postgres, Oracle, and modern SQL Server use MVCC. MySQL/InnoDB uses a hybrid approach that’s mostly MVCC but with some locking for serializable mode. MVCC is the dominant modern approach, because read-heavy workloads are the common case and blocking readers is catastrophic for throughput.

#D (Durability): Committed Data Survives Crashes

Once a transaction has been committed, its effects survive any subsequent crash. Power failure, kernel panic, kill -9 on the database process — committed data is still there when the database comes back.

How durability is implemented: fsync().

When a transaction commits, the database writes its WAL records and then calls fsync() (or fdatasync()) to force the log to physical storage. Only after fsync() returns does the database tell the application “commit successful.”

This is the actual bottleneck in most transactional workloads. Modern disks can sustain hundreds of thousands of writes per second in throughput — but fsync is a different operation entirely. It’s a barrier: the database is telling the OS, telling the filesystem, telling the disk, persist this data now and confirm back to me before I continue. Every layer in that stack can be a source of lies.

#The fsync() Problem

A famous and recurring embarrassment: fsync lies.

Various combinations of filesystems, disks, RAID controllers, and OS configurations have, over the years, reported “fsync successful” before the data was actually on stable storage. The write was in a disk cache or controller cache that could be lost on power failure.

In 2018, Postgres and MySQL discovered that certain Linux filesystems, under certain failure modes, would fsync() successfully but then lose data on subsequent errors. This led to the famous “fsync bug” post from Postgres developers that prompted kernel-level changes.
Consumer SSDs have long been known to sometimes report fsync completion while data is still in a volatile controller cache. Enterprise SSDs have power-loss-protected caches; consumer drives don’t.
RAID controllers with write-back caching are supposed to have battery backup; when the battery degrades, durability silently becomes best-effort.

The result: “durable” is a property of the entire stack, not just the database. A database can only be as durable as the storage layer beneath it allows. Most production databases ship with warnings about what storage configurations they consider safe, and Postgres has an explicit fsync = off setting that trades durability for performance (and explicitly voids the durability guarantee).

#Synchronous vs. Asynchronous Commit

Within the limits of storage, databases offer tunable durability levels:

Synchronous commit (default). Wait for WAL fsync before reporting commit. Maximum durability, minimum throughput.
Asynchronous commit. Report commit immediately; fsync the WAL in the background. Higher throughput, but a crash in the window between commit and fsync loses recently-committed transactions.
Group commit. Batch multiple transactions’ WAL records into one fsync, amortizing the cost across many commits. Most databases do this automatically.
Replication-based durability. Wait for the WAL to reach a replica rather than the local disk. Often faster than local fsync (network is fast, fsync is slow) and provides a different form of durability guarantee.

#ACID in Distributed Systems: BASE and CAP

ACID’s promises are relatively easy to keep on a single machine. Distributed across many machines, they become much harder. This is what the CAP theorem and the BASE (Basically Available, Soft state, Eventually consistent) tradition explored in the 2000s.

The 2000s NoSQL movement was partly a reaction to the cost of ACID in distributed systems. Databases like early MongoDB, Cassandra, and Riak gave up some ACID properties in exchange for horizontal scalability and partition tolerance.

The 2010s and 2020s have been partly a reversal: systems like Google Spanner, CockroachDB, YugabyteDB, and FoundationDB demonstrated that you can have distributed ACID if you’re willing to pay for clever infrastructure (atomic clocks, consensus protocols, careful network engineering). Spanner uses the TrueTime API — GPS and atomic clocks in every data center — to provide globally-ordered commit timestamps, enabling serializable isolation across continents.

ACID didn’t lose. It turned out to be achievable in distributed systems, just expensive. Where that expense isn’t acceptable, the BASE approach survives. Most modern systems pick the ACID level they need for each workload, rather than treating it as all-or-nothing.

#What ACID Actually Is

The Relational Model post describes ACID as “the promise databases make.” That framing is accurate but understates what’s underneath. Each letter is the tip of an implementation iceberg:

A is write-ahead logging — a 40-year body of research on how to recover durably from arbitrary failure points.
C is constraint enforcement and schema design — a relatively thin database-layer piece on top of a thick application-layer concern.
I is concurrency control — the hardest problem in databases, with two major solution families (locking, MVCC) and constant research on variants.
D is storage-stack engineering — getting the operating system, filesystem, and hardware to reliably persist data, which is harder than anyone expects the first time they encounter it.

Getting all four of these right, under high load, without sacrificing throughput, is essentially the entire engineering problem of databases. Every major relational database in 2026 — Postgres, MySQL, Oracle, SQL Server — is, at its core, a very sophisticated machine for making ACID hold while serving tens of thousands of transactions per second. The math Codd published in 1970 gives you the what. Härder and Reuter gave the promise. The forty-three years since have been about the how.