Q14 — What does ACID really mean — not the acronym, but the actual guarantees and trade-offs?
Why does ACID exist as a concept?
Databases serve multiple clients simultaneously, and clients do complex multi-step operations — transfer money, place an order, update a user's profile. Each operation might touch multiple rows across multiple tables, and each step might take milliseconds.
During those milliseconds, other clients are also doing things. Machines crash. Disks fail. Without any guarantees about how the database manages these concurrent, failure-prone multi-step operations, applications would be impossible to write correctly. Every developer would have to reason about every possible interleaving of every possible concurrent operation and every possible failure point.
ACID is the database's contract with the application developer: "if you use transactions, we guarantee these four properties, and you don't have to think about everything else."
Atomicity — all or nothing, not "most of it"
A transaction contains multiple operations. Atomicity means the database guarantees that either all of them happen, or none of them happen. There is no observable state where some operations have taken effect and others haven't.
Consider a bank transfer: debit account A by $100, credit account B by $100. Without atomicity, a crash between these two operations leaves the database in a state where $100 has vanished — it left A and never reached B.
With atomicity, the crash causes the entire transaction to be rolled back. Account A still has its original balance. Account B is unchanged. The transfer simply didn't happen. The application can retry.
What makes this possible is the WAL and the buffer pool. Changes are made in memory and logged. If the transaction doesn't commit (either because the application rolled it back or the machine crashed), the changes are never made durable. Recovery reads the WAL, finds the transaction has no commit record, and discards its changes.
Atomicity is not about speed. It's about the guarantee that partial transactions are never visible.
Consistency — the database moves from one valid state to another
Consistency is the most misunderstood of the four. It's often described as "the database stays consistent" — which is circular and unhelpful.
Consistency means: if the database is in a valid state before the transaction starts, it will be in a valid state after the transaction commits. "Valid" is defined by the constraints and rules you've defined: NOT NULL constraints, UNIQUE constraints, FOREIGN KEY constraints, CHECK constraints.
If you try to insert an order for a customer_id that doesn't exist in the customers table, the FOREIGN KEY constraint rejects the transaction — it would have violated consistency.
If you try to insert a second user with the same email address, the UNIQUE constraint rejects it.
Consistency is partly the database's responsibility (enforcing constraints) and partly the application's responsibility (writing business logic that maintains invariants the database can't express as constraints). A database can enforce "account balance is NOT NULL" but it can't enforce "total money in the system before and after a transfer must be equal" — the application has to get that right.
Atomicity enables consistency: if a transaction would violate a constraint, it's rolled back atomically, leaving the database in its prior valid state.
Isolation — concurrent transactions don't see each other's chaos
This is where most of the real complexity lives in databases.
Consider two transactions running simultaneously:
- Transaction A is transferring $100 from account 1 to account 2 (two operations)
- Transaction B is reading the balance of accounts 1 and 2 (to show the user their total)
If Transaction B reads account 1 after it's been debited but before account 2 has been credited, it sees a state that never durably existed — $100 appears to have vanished. This is called a dirty read (or in this case, a non-repeatable read since A hasn't committed yet).
Isolation guarantees that concurrent transactions appear to execute as if they ran serially — one after another, not interleaved. Transaction B would either see the state before A started or the state after A committed, never the in-between.
The challenge is that full isolation (called serializable isolation) is expensive — it requires locking rows that transactions are working with, which blocks concurrent transactions from proceeding. So databases define multiple isolation levels, each trading safety for performance:
Read Uncommitted: No isolation. Can read uncommitted changes from other transactions (dirty reads). Almost never used.
Read Committed: Can only read committed data. But if you read the same row twice in one transaction, you might get different values if another transaction commits between your reads (non-repeatable reads). Most databases' default.
Repeatable Read: Once you've read a row, you'll see the same value if you read it again in the same transaction — even if another transaction commits a change in between. But new rows inserted by other transactions can appear in range queries (phantom reads). MySQL's default.
Serializable: Full isolation. Transactions appear to execute one at a time. No dirty reads, no non-repeatable reads, no phantom reads. Most expensive — often uses predicate locks or optimistic concurrency checks.
The right isolation level depends on what your application can tolerate. Most applications run fine at Read Committed because the operations they do are short and the probability of anomalies is low. Financial systems often require Repeatable Read or Serializable.
Durability — committed means committed, forever
Once the database tells your application "transaction committed," that data will survive any subsequent failure: machine crash, power outage, operating system panic. It's on durable storage.
What makes this possible is the WAL flush on commit. Before the database returns "committed," it writes a commit record to the WAL and calls fsync — which forces the OS to push the log data from its buffer to physical disk. Only after fsync returns does the database tell the application the commit succeeded.
This is why fsync being disabled (or faked, as some cloud storage providers have done in the past) is a serious correctness problem — without it, "durable" data is actually just in the OS page cache, which evaporates on power loss.
Durability is separate from replication. A committed transaction is durable on the machine it committed on. If that machine's disk fails catastrophically, you might still lose data. Full durability across hardware failures requires replication — the WAL is replicated to another machine before the commit is acknowledged.
The trade-offs ACID imposes
ACID guarantees are not free:
Atomicity requires tracking all changes in a transaction and being able to undo them — WAL overhead on every write.
Isolation requires some form of locking or version tracking (MVCC) to prevent concurrent transactions from seeing each other's in-progress work — this limits concurrency.
Durability requires fsync on commit — disk I/O before the client gets a response, adding latency to every write.
This is why many NoSQL databases weakened or abandoned ACID: by relaxing these guarantees, they can offer higher write throughput, lower latency, and better horizontal scalability. The question is always whether your application can tolerate the anomalies that result.
The mental model
Atomicity: all ops in a transaction succeed, or none do — no partial states
How: WAL + rollback on crash or explicit ROLLBACK
Consistency: valid state → valid state — constraints always enforced
How: constraint checks before commit, rollback if violated
(Application is also responsible for business logic constraints)
Isolation: concurrent transactions don't see each other's in-progress changes
How: locking / MVCC — multiple levels with different trade-offs
Serializable > Repeatable Read > Read Committed > Read Uncommitted
Durability: committed = durable, even through crash
How: WAL fsync before "commit" acknowledged to client
Cost:
Atomicity → WAL write overhead
Isolation → locking limits concurrency
Durability → fsync latency on every commit
NoSQL trade: relax some of these → more throughput, less safety
ACID is not just four buzzwords. It's a contract that lets application developers write code as if the database is a simple, single-threaded system that never crashes — when in reality it's concurrent, distributed, and failure-prone. The database absorbs enormous complexity so the application doesn't have to.