ACID Transactions Explained

"ACID" gets thrown around as a synonym for "a real database," but each letter is a distinct, separately-breakable guarantee — and knowing which one a given failure violates is what actually matters in practice.

The four letters

• Atomicity — a transaction's operations happen as one indivisible unit: all of them succeed, or none of them do. A transfer that debits one account and credits another either does both or neither — never just one.
• Consistency — a transaction moves the database from one valid state to another, respecting its own defined rules (constraints, foreign keys). Worth noting: this is a narrower guarantee than the "C" in the CAP theorem — related idea, different scope, and conflating the two is a common mistake.
• Isolation — concurrent transactions don't see each other's uncommitted, in-progress changes. Without isolation, one transaction could read another's half-finished work.
• Durability — once a transaction commits, it survives a crash. A confirmed write isn't going to vanish because the server lost power a second later.

Isolation levels: how much concurrency correctness costs

Isolation isn't all-or-nothing — databases offer a spectrum of isolation levels, each allowing a different set of anomalies in exchange for better concurrency performance:

Level	Allows	Typical default
Read Uncommitted	Dirty reads (seeing another transaction's uncommitted changes)	Rarely used
Read Committed	Non-repeatable reads (a value changes between two reads in the same transaction)	PostgreSQL, SQL Server default
Repeatable Read	Phantom reads (a new row matching a query appears mid-transaction)	MySQL/InnoDB default
Serializable	Nothing — behaves as if transactions ran one at a time	Strongest, most expensive

The pattern across the table: each stricter level closes off one more category of anomaly, at the cost of more locking or more work resolving conflicts, which is why almost no database defaults to full Serializable — most workloads don't need it, and the concurrency cost isn't worth paying by default.

ACID and the CAP theorem

ACID's guarantees, and strict isolation in particular, sit naturally on the CP side of the CAP theorem: serving a genuinely isolated, consistent view of the data is fundamentally in tension with staying available when replicas can't confirm they agree. This is why sharding and replication complicate ACID in practice: a transaction that used to be trivially atomic and isolated on a single machine has to coordinate across multiple machines once the data it touches is split across shards — the same cost database sharding names explicitly as the price of scaling out a relational database.

System Design Lab

Why this matters in an interview

"The database is ACID-compliant" isn't a design decision on its own — it's a starting property of whatever relational database you picked. The stronger conversation is about isolation level: naming which anomalies a specific piece of the system can tolerate (a dashboard read that's occasionally a few seconds stale is usually fine at Read Committed) versus which parts genuinely need Serializable (a double-booking check on the last seat in a flight). That's also exactly the same reasoning strong vs. eventual consistency applies at the replication layer — different mechanism, same underlying question of what correctness a specific piece of data actually needs.

Go deeper

The full lesson includes the sequence diagram in more detail and expands on each isolation level's specific anomalies:

👉 Read the full ACID Transactions lesson — part of the free System Design Lab course.