Cruxly › System design › Databases: Choosing One

ACID, precisely

four promises, four owners, and where each one leaks

ACID is not one guarantee. It is four, owned by different parts of the system, and each can be weakened by a setting you control. A senior answer says which letter the database provides, which one your code provides, and which dial you turned.

Four properties, four different jobs

The acronym appears in a 1983 paper by Härder and Reuter (ACM Computing Surveys). Treat it as four claims, each with a different owner.

Two confusions come up in interviews.

• Atomicity is not about concurrency. If someone asks whether two transactions can see each other's half-finished work, that is isolation.
• Consistency in ACID is not consistency in CAP. In ACID it is your invariant, such as "credits equal debits". In CAP it means linearizability. Name which one you mean.

:::key C is the application's job The database cannot know that credits must equal debits. It can reject a negative balance because you declared CHECK (balance >= 0). Everything else is your code, and your code stays correct only because A and I stop crashes and races from breaking it halfway through. Declare every rule you can as a constraint, because a constraint holds even when a future code path forgets to check. :::

Atomicity and the log: how a commit really works

Databases get atomicity and durability from the same trick: a write-ahead log (WAL). The rule, in PostgreSQL's words, is that changes to data files may be written only after the log records describing them have reached permanent storage.

At commit, only the log must be flushed. PostgreSQL documents why this is cheap: the log is written sequentially, and one fsync can commit several transactions at once. Each log record carries a CRC-32C, so recovery can tell a complete record from a torn one.

After a crash, recovery starts from the last checkpoint and does two jobs:

• Redo replays logged changes that never reached the data files, so committed work survives.
• Undo (or its equivalent) removes changes from transactions that never committed, even if their pages were flushed early.

The two engines you will be asked about differ here. InnoDB keeps a redo log plus undo records that say how to reverse each change. PostgreSQL has no undo log: a new row version written by a transaction that never committed is simply invisible to every later snapshot, because the commit status of that transaction says it did not commit.

One detail worth saying aloud: InnoDB's manual states that crash recovery works whatever flush setting you chose, and transactions are either applied entirely or erased entirely. Relaxing durability never breaks atomicity. You lose the tail of recent commits, not the all-or-nothing property.

Durability is a dial, not a switch

"Durable" means the commit record reached non-volatile media. Every step on the way is a setting or a hardware property.

Group commit is why the safe setting is affordable. Concurrent commits queue behind one flush, so one fsync covers many of them. PostgreSQL's commit_delay (default 0) can wait a little to widen the batch, but only when at least commit_siblings (default 5) other transactions are open.

Torn pages. A PostgreSQL page is 8 KB, but disk sectors are much smaller (512 bytes in the classic case), so a crash can leave a page half new and half old. A log record that only says "change these bytes" cannot repair that. PostgreSQL logs a full page image the first time a page changes after a checkpoint. InnoDB writes each page to a doublewrite buffer first, so recovery can find a good copy.

The disk can lie. PostgreSQL's documentation warns that drive write-back caches are volatile, and that a drive can acknowledge a flush while the data still sits in cache. Hardware you trust, or a battery-backed controller, is part of the durability claim. As evidence that the software side holds up: Jepsen tested MySQL 8.0.34 with LazyFS, a filesystem that drops un-fsynced writes, and found no lost committed transactions after process crashes when innodb_flush_log_at_trx_commit was 1, and data loss when it was 0.

:::key Say the trade-off with a number "I'd run synchronous_commit = off for the view-count table: I accept losing up to about 600 ms of counts in a crash, and I get commits that never wait on a flush. Orders stay on on." :::

Durable on one machine is not durable

A fsync protects against a process or OS crash. It does nothing against a dead disk or a lost machine. For that, a second machine must have the record before the client is told.

PostgreSQL spells out the ladder with synchronous_commit when a synchronous standby is configured:

Single-leader systems such as PostgreSQL, MySQL and ClickHouse acknowledge before a replica has the write by default, and that is where acknowledged writes die. ClickHouse's replication documentation, for example, says an INSERT waits for confirmation from only one replica, so recent inserts can be lost if that node is lost, and insert_quorum changes that. Consensus-based stores (Spanner, CockroachDB) and MongoDB (default write concern majority, with an exception for some arbiter setups) wait for a quorum. Treat every system the same way: ask what the default acknowledgement waits for.

The costs of the strong end, which a good answer volunteers:

• Latency. Every commit adds a network round trip and a remote flush.
• Availability. PostgreSQL's documentation warns that commits may never complete if a synchronous standby crashes, and the fix is manual: lower the number of required synchronous standbys. Real deployments list several candidate standbys or use quorum settings so one failure does not stall writes.

:::key Two questions for any durability claim

1. Does an acknowledged commit survive the process dying? (Is the log flushed?)
2. Does it survive the machine dying? (Did a second machine confirm?)

Most "lost committed write" incidents are a yes to the first and a no to the second. :::

Isolation: the I that is not serializable

Full isolation means serializable: the outcome equals some one-at-a-time order. Most single-node engines do not default to it.

The level names are not portable. PostgreSQL's Repeatable Read is snapshot isolation. MySQL's Repeatable Read behaves differently for plain reads and locking reads, and Jepsen's 2023 analysis of MySQL 8.0.34 found lost updates and read skew at Repeatable Read on a single healthy node. Say the engine and the level, not just the level.

Write skew is the anomaly to know cold. Both transactions read an overlapping set, each writes a different row, and a rule that spans the rows breaks. Snapshot isolation allows it because there is no write-write conflict to detect.

Your options, from most to least general:

1. Serializable. PostgreSQL's version uses non-blocking predicate locks to track read-write dependencies, and aborts a transaction when a combination of them could lead to a serialization anomaly. Monitoring adds no blocking beyond Repeatable Read. The price is that it can abort transactions that serial execution would not have failed, so the application must retry on SQLSTATE 40001.
2. Lock what you read. SELECT ... FOR UPDATE on the rows you depend on. It protects rows that exist; it does not stop a new row (a phantom) from appearing.
3. Make the conflict physical. A constraint, or a single row everyone must update (a counter or a lock row), turns an invisible conflict into a write-write one.

PostgreSQL's SSI is not immune to bugs either. Jepsen found a case in 2020 where three concurrent transactions slipped through serializable in several releases, and it was fixed in the August 2020 minor releases. Serializable is a promise to test, not assume.

:::key Retry is part of the contract A system that offers serializable by aborting transactions has moved work to your client. Say it: "The code wraps each transaction in a retry loop on 40001, with a bounded retry count and idempotent side effects." :::

Distributed transactions: what two-phase commit adds

When one transaction spans two databases or shards, each can commit its half locally and still disagree. Two-phase commit (2PC) closes that gap:

1. Prepare. The coordinator asks every participant to prepare. Each does the work, writes a durable prepare record, keeps its locks, and votes yes or no. A yes is a promise to commit if told.
2. Commit or abort. If every vote is yes, the coordinator durably logs COMMIT. That write is the commit point. Then it tells everyone. Any no, or a missing vote, means abort.

It gives you atomicity across machines. It costs you three things:

• Latency. At least two network round trips and extra log flushes on every commit, and the slowest participant sets the pace.
• Locks held across the round trips. A prepared participant cannot release anything until the decision arrives.
• Blocking. If the coordinator dies after the votes, participants can neither commit nor abort on their own, so they wait in doubt holding locks. PostgreSQL's documentation for PREPARE TRANSACTION says a prepared transaction keeps all its locks, a forgotten one interferes with vacuum, and in extreme cases it can force a shutdown to prevent transaction ID wraparound. It also says the feature is meant for an external transaction manager, not for applications.

So the standard advice is to avoid needing it. Keep a transaction inside one shard by choosing the shard key so related rows live together. Use a store with native multi-record transactions where the cost is already engineered in (MongoDB, for example, atomically updates a single document always, and added multi-document transactions at feature compatibility 4.0 on replica sets and 4.2 on sharded clusters). When you cannot avoid crossing systems, a saga runs local transactions with compensating actions, and you accept that it gives up isolation: other users can see the intermediate states.

If you do use 2PC, say the mitigation: the coordinator's decision log has to be as available as the data, and a timeout or operator procedure must resolve in-doubt transactions.

ACID vs BASE: a slogan, not a choice

BASE stands for Basically Available, Soft state, Eventually consistent. It is a pun on ACID, popularised by Dan Pritchett of eBay in the ACM Queue article "BASE: An Acid Alternative". Its one testable promise is convergence: once writes stop, replicas end up the same. Nothing in it says how stale a read can be, whether a user sees their own writes, or in what order updates appear.

The useful move in an interview is to replace the slogan with the specific property you give up:

Also drop the idea that ACID and BASE are two camps. The choice that matters is made per piece of data: money and inventory want atomic multi-row updates and a strong isolation level; a feed, a counter or a search index can tolerate lag. One system often has both.

Saying it in an interview, and the wrong statements

A structure that works for any "is it ACID?" or "will I lose data?" question:

1. Name the invariant the data must keep (C, in your words).
2. Atomicity: which operations must succeed or fail together, and whether they fit in one node or one shard.
3. Isolation: the engine, the level, the anomaly you are worried about, and the fix (serializable with retries, a locking read, or a constraint).
4. Durability: flush setting, then replication: "acknowledged after the primary's flush and one synchronous replica's flush".
5. The cost in latency and availability that this choice carries.

Follow-ups to prepare:

• "How can a committed write be lost on a healthy database?" Acknowledged before the flush (synchronous_commit = off, a relaxed InnoDB flush setting), a disk that acknowledges a flush while holding the data in volatile cache, or an asynchronous replica promoted after the primary was lost.
• "Why does Postgres write full pages to the log?" To repair torn pages, because a change record assumes an intact page.
• "What does a distributed transaction add?" Atomicity across nodes at the price of latency, locks held across round trips, and blocking if the coordinator fails.

Wrong statements to avoid

| "Serializable means no retries" | Optimistic implementations abort, and your code must retry

How to say it in the interview

When I say a store is ACID, I mean four things I can check. Atomicity: a failed transaction leaves no trace, because recovery undoes it. Isolation: concurrent transactions don't corrupt each other, and I'll name the level, because the default is often not serializable. Durability: an acknowledged commit survived a flush, and I'll say whether a replica also has it. Consistency is my invariant, which the database protects only through constraints I declare and through A and I.

Checklist

• I can state what each of A, C, I and D guarantees, and name C as the application's invariant.
• I can walk through a commit on one node: log record, fsync, acknowledge, lazy page flush, crash recovery with redo and undo.
• I can name the durability dials (synchronous_commit, innodb_flush_log_at_trx_commit, fsync) and what each costs in a crash.
• I can explain why a flushed write is not safe against losing the machine, and what a synchronous replica adds and costs.
• I can describe write skew, name which isolation levels allow it, and give three fixes including the retry requirement.
• I can explain two-phase commit, its blocking failure, and how to avoid needing it.
• I can replace the words BASE and eventually consistent with the precise property given up.
• I can list the wrong statements in the closing table and correct each one.

Related lessons

• How to choose a database
• Isolation levels, in depth
• Data models: what each family is built for, and where it fails
• Storage engines: B-trees, LSM-trees, row vs column
• NoSQL modelling in practice: DynamoDB, Cassandra, MongoDB
• Distributed SQL: Spanner, CockroachDB, Aurora and friends