Skip to content

ACID

1. What is it? (2-3 lines)

ACID is an acronym representing the four foundational properties of reliable database transactions: Atomicity, Consistency, Isolation, and Durability. Together, they guarantee data integrity despite hardware crashes, power outages, and concurrent operations.

2. Why does it exist?

Without ACID guarantees, multi-step concurrent operations on shared data risk partial executions, race conditions, corrupt state, and lost updates. ACID provides developers with a reliable mathematical model for state storage.

3. Where is it used?

  • RDBMS: PostgreSQL, MySQL (InnoDB), Oracle, SQL Server.
  • Distributed NewSQL: CockroachDB, Google Spanner, YugabyteDB, TiDB.
  • Transactional NoSQL: MongoDB (Multi-document transactions), FoundationDB.

4. How does it work? (simple flow)

  1. Atomicity (All or Nothing): Implemented via Undo Logs / WAL. If a transaction fails mid-way, the engine reads undo logs to revert every modified byte, making partial updates invisible.
  2. Consistency (Valid State Transitions): Enforced via schema constraints (CHECK, FOREIGN KEY, UNIQUE) and application business logic before and after commit.
  3. Isolation (Concurrent Execution): Implemented via Locks (2PL) or MVCC (Multi-Version Concurrency Control), ensuring concurrent transactions execute without illegal state interference.
  4. Durability (Persistent Survival): Implemented via WAL fsync. Once committed, log records are written to non-volatile storage before returning success to the client.

5. Advantages

  • Absolute data correctness for financial ledgers, inventories, and mission-critical state.
  • Simplifies application development by pushing concurrency and failure handling into the database engine.
  • Prevents data corruption caused by hardware crashes or concurrent thread collisions.

6. Limitations

  • Strict ACID enforcement (especially Serializability & synchronous fsync) severely caps write throughput and increases latency.
  • Distributed ACID across multi-region clusters incurs heavy network round-trip latencies (Paxos/Raft consensus).
  • Scaling ACID databases horizontally requires complex sharding and distributed transaction managers (2PC/Saga).

7. Common interview questions (10)

  1. Explain each property of ACID and the specific database mechanism implementing it.
  2. How does Atomicity differ from Consistency?
  3. How does Durability interact with OS page cache and disk fsync calls?
  4. What is the CAP Theorem trade-off regarding ACID (CA) vs BASE (AP) systems?
  5. How does Google Spanner achieve global ACID transactions using TrueTime atomic clocks?
  6. Why is Consistency in ACID different from Consistency in the CAP Theorem?
  7. What performance trade-offs occur when relaxing Durability (fsync off) or Isolation (Read Committed)?
  8. How does Two-Phase Commit (2PC) maintain Atomicity across multiple distributed database nodes?
  9. How does MVCC achieve Isolation without read locks?
  10. What happens to Durability when using asynchronous database replication?

8. Common misconceptions

  • Misconception: "C" in ACID means CAP Theorem Consistency. Fact: CAP Consistency means Linearizability (all nodes see the exact same data simultaneously). ACID Consistency means the database satisfies user schema constraints (FOREIGN KEY, NOT NULL).
  • Misconception: ACID databases cannot scale horizontally. Fact: Distributed SQL (CockroachDB/Spanner) scales horizontal ACID transactions via Raft/Paxos consensus.

10. 30-second cheat sheet

  • A (Atomicity): All-or-Nothing \(\implies\) Implemented via Undo Logs / WAL.
  • C (Consistency): Valid state \(\implies\) Implemented via Schema Rules & Constraints.
  • I (Isolation): Concurrency control \(\implies\) Implemented via MVCC & Locks.
  • D (Durability): Saved permanently \(\implies\) Implemented via WAL fsync to Disk.
  • Golden Rule: Use ACID for money and mission-critical state; relax to BASE for massive high-throughput analytics.