ACID Properties

In a Nutshell

ACID is the set of four guarantees that make relational databases trustworthy for critical operations — money transfers, inventory updates, user registrations, and anything where partial failure would be catastrophic. Atomicity says a transaction is all-or-nothing. Consistency says the database moves from one valid state to another. Isolation says concurrent transactions don't interfere. Durability says committed data survives crashes. Together, they let you trust that "transfer $100 from Alice to Bob" either completes fully or doesn't happen at all — never leaving the system in a half-done state.

How It Actually Works

The Four Properties in Detail

Atomicity — "All or Nothing"

A transaction is an indivisible unit. If any operation within it fails, all operations roll back.

BEGIN TRANSACTION;
  UPDATE accounts SET balance = balance - 100 WHERE user = 'Alice';
  UPDATE accounts SET balance = balance + 100 WHERE user = 'Bob';
COMMIT;
-- If either UPDATE fails, NEITHER takes effect

Without atomicity: If the first UPDATE succeeds but the second fails (crash, constraint violation), Alice loses $100 that Bob never receives. The money evaporates.

How databases implement it: Write-Ahead Log (WAL). Every change is first written to a log. On commit, the log is flushed to disk. On crash, the database replays the log and rolls back incomplete transactions.

Consistency — "Valid State to Valid State"

The database enforces all constraints (foreign keys, unique constraints, check constraints) — a transaction cannot leave the database in an invalid state.

-- Constraint: balance >= 0
ALTER TABLE accounts ADD CONSTRAINT positive_balance CHECK (balance >= 0);

-- This transaction will be rejected:
BEGIN;
  UPDATE accounts SET balance = balance - 500 WHERE user = 'Alice';
  -- Alice only has $100 → CHECK violation → ROLLBACK
COMMIT;

Important nuance: "Consistency" in ACID is about application-level invariants enforced by constraints. This is different from "consistency" in CAP theorem (which means "every read returns the latest write").

Isolation — "No Interference"

Concurrent transactions behave as if they ran sequentially. One transaction cannot see another's uncommitted changes.

Timeline:
T1: Read Alice balance = $100
T2:                              Read Alice balance = $100
T1: Withdraw $50 → balance = $50
T2:                              Withdraw $30 → balance = ???

Without isolation: T2 reads $100, sets to $70. T1's withdrawal is lost.
With isolation:    T2 waits or reads $50, sets to $20. Both withdrawals apply.

The reality: Full isolation (Serializable) is expensive. Databases offer weaker isolation levels that trade safety for performance. See Transactions & Isolation Levels.

Durability — "Committed Means Permanent"

Once a transaction is committed, the data survives hardware failures, power outages, and crashes.

How databases implement it:

1. Write-Ahead Log (WAL) — Changes written to durable storage (disk) before the commit is acknowledged
2. Checkpointing — Periodically write in-memory data to data files
3. Replication — Copy committed data to replica servers (for redundancy beyond single-machine durability)

The nuance: Single-disk durability protects against process crashes but not disk failures. True durability requires replication to separate machines.

ACID in Practice

Property	What It Prevents	Real-World Example
Atomicity	Partial transactions	Bank transfer debits Alice but crash prevents crediting Bob
Consistency	Constraint violations	Booking the same seat to two passengers
Isolation	Lost updates, dirty reads	Two users buying the last item in stock (both see stock=1)
Durability	Data loss on crash	Payment confirmed to user but data lost on server restart

Databases and ACID Support

Database	ACID Support	Notes
PostgreSQL	Full ACID	Default: Read Committed isolation
MySQL (InnoDB)	Full ACID	Default: Repeatable Read isolation
MongoDB (4.0+)	Multi-document transactions	More expensive than single-doc atomicity
Cassandra	❌ Not ACID	Lightweight transactions (LWT) for single-partition compare-and-set
Redis	❌ Not ACID	MULTI/EXEC provides atomicity but not isolation
CockroachDB	Full ACID	Serializable isolation by default
DynamoDB	Single-item ACID	TransactWriteItems for multi-item (limited to 100 items)

Seeing It in Action

Scenario: E-commerce checkout — atomicity prevents overselling

-- Without ACID: race condition
-- Thread 1 and Thread 2 both check stock simultaneously
SELECT stock FROM products WHERE id = 42;  -- Both see stock = 1
UPDATE products SET stock = stock - 1 WHERE id = 42;  -- Both decrement
-- Result: stock = -1 (oversold!)

-- With ACID: serializable transaction prevents this
BEGIN;
  SELECT stock FROM products WHERE id = 42 FOR UPDATE;  -- Lock the row
  -- If stock > 0:
  UPDATE products SET stock = stock - 1 WHERE id = 42;
  INSERT INTO order_items (order_id, product_id, quantity) VALUES (101, 42, 1);
COMMIT;
-- Thread 2's SELECT FOR UPDATE blocks until Thread 1 commits/rollbacks
-- Thread 2 then sees stock = 0 and cannot proceed

Why ACID matters here:

• Atomicity — If the INSERT fails, the stock decrement rolls back
• Isolation — SELECT FOR UPDATE prevents the race condition
• Consistency — A CHECK constraint stock >= 0 rejects overselling at the database level
• Durability — Once committed, the order survives a server crash

Interview Questions

1. Q: Explain ACID properties with a real-world example. Hint: Bank transfer: Atomicity (debit + credit are one unit), Consistency (total money in the system is preserved), Isolation (a balance check during the transfer sees a consistent state), Durability (the transfer survives a crash after commit). Focus on what goes wrong when each property is missing.

2. Q: What's the difference between "consistency" in ACID and "consistency" in CAP theorem? Hint: ACID consistency: the database enforces application-level constraints (foreign keys, CHECK constraints). CAP consistency: every read returns the most recent write (linearizability). They're completely different concepts that unfortunately share a name.

3. Q: How does a database guarantee durability? What can still cause data loss? Hint: WAL (Write-Ahead Log) — changes flushed to disk before commit is acknowledged. Data loss still possible if: disk itself fails (need replication), entire datacenter fails (need cross-region replication), or the database has fsync disabled for performance (some do this for speed at the risk of durability).

4. Q: Why don't NoSQL databases like Cassandra or Redis provide full ACID? Hint: ACID (especially Isolation) requires coordination between concurrent operations — locking or MVCC. In a distributed system, this coordination across nodes is expensive (network round trips, consensus). NoSQL databases trade ACID for horizontal scalability and lower write latency. Some offer limited ACID: MongoDB for single documents, DynamoDB for small batches.

5. Q: A junior engineer says "we should use Serializable isolation everywhere for maximum safety." What's your response? Hint: Serializable is the safest but slowest — it's equivalent to running transactions one at a time. For most applications, Read Committed or Repeatable Read is sufficient and much more performant. Use Serializable only for critical operations where correctness cannot be compromised (financial transactions, inventory). Default to the weakest level that prevents the anomalies relevant to your use case.

References

• Designing Data-Intensive Applications by Martin Kleppmann — Chapter 7: Transactions
• PostgreSQL Transaction Documentation — official transaction semantics
• MySQL InnoDB and ACID — how InnoDB implements ACID

Dive Deeper

• Jepsen — Consistency Testing — Kyle Kingsbury's testing of ACID claims in distributed databases
• Transaction Processing: Concepts and Techniques by Jim Gray & Andreas Reuter — the academic reference on transactions
• CockroachDB — Serializable Transactions — how a distributed database achieves ACID