Skip to content

ACID Properties#

Problem statement (interviewer prompt)

A transfer of $100 between two bank accounts must either fully succeed or fully fail; never partial. Define the four properties (ACID) that make this safe, and explain how each is implemented by a relational database.

ACID is the contract a transactional database makes with the application:

flowchart TB
  Txn[Transaction] --> A[Atomicity<br/>all or nothing]
  Txn --> C[Consistency<br/>invariants preserved]
  Txn --> I[Isolation<br/>concurrent txns don't interfere]
  Txn --> D[Durability<br/>committed data survives crash]

    classDef client fill:#dbeafe,stroke:#1e40af,stroke-width:1px,color:#0f172a;
    classDef edge fill:#cffafe,stroke:#0e7490,stroke-width:1px,color:#0f172a;
    classDef service fill:#fef3c7,stroke:#92400e,stroke-width:1px,color:#0f172a;
    classDef datastore fill:#fee2e2,stroke:#991b1b,stroke-width:1px,color:#0f172a;
    classDef cache fill:#fed7aa,stroke:#9a3412,stroke-width:1px,color:#0f172a;
    classDef queue fill:#ede9fe,stroke:#5b21b6,stroke-width:1px,color:#0f172a;
    classDef compute fill:#d1fae5,stroke:#065f46,stroke-width:1px,color:#0f172a;
    classDef storage fill:#e5e7eb,stroke:#374151,stroke-width:1px,color:#0f172a;
    classDef external fill:#fce7f3,stroke:#9d174d,stroke-width:1px,color:#0f172a;
    classDef obs fill:#f3e8ff,stroke:#6b21a8,stroke-width:1px,color:#0f172a;
    class Txn,A,C,I,D service;

Each property fights a different threat: atomicity vs partial writes, consistency vs invalid state, isolation vs concurrency races, durability vs crash.

ACID describes what a transactional database guarantees. Each letter is implemented by concrete machinery; understanding the machinery explains performance trade-offs.

A - Atomicity#

A transaction either fully commits or fully aborts. No partial state visible.

sequenceDiagram
  participant App
  participant DB
  App->>DB: BEGIN
  App->>DB: UPDATE A SET balance -= 100
  App->>DB: UPDATE B SET balance += 100
  App->>DB: COMMIT
  Note over DB: write all changes durably or abort all

Implemented via: - Write-ahead log (WAL): all changes are appended to a log before being applied. On crash, recovery replays committed records and rolls back uncommitted ones. - Undo log (Postgres MVCC, InnoDB rollback segment): stores the pre-image so a txn can be reversed.

C - Consistency#

The database moves from one valid state to another. Constraints (foreign keys, CHECK, NOT NULL, unique) are not violated at commit time.

This is the squishiest letter: the database enforces declared invariants. Application-level invariants (e.g. "every order has at least one line") are the app's responsibility.

I - Isolation#

Concurrent transactions don't see each other's intermediate state. The strictest level (serializable) is equivalent to running them one at a time.

sequenceDiagram
  participant T1 as Txn 1
  participant T2 as Txn 2
  T1->>DB: read x=10
  T2->>DB: write x=20, commit
  T1->>DB: read x=?
  Note over T1: under REPEATABLE READ, still sees 10
  Note over T1: under READ COMMITTED, sees 20

Implemented via: - MVCC (Postgres, Oracle, MySQL InnoDB): each transaction sees a snapshot consistent at its start time. Writers don't block readers. - Two-phase locking: take shared/exclusive locks; release at commit.

See mvcc-isolation-levels for the four levels and the anomalies each prevents.

D - Durability#

Once a commit returns success, the data survives crash, power loss, OS reboot.

flowchart TB
  Txn[Commit] -->|fsync| WAL[(WAL on disk)]
  WAL -.async checkpoint.-> Heap[(Heap tables)]

    classDef client fill:#dbeafe,stroke:#1e40af,stroke-width:1px,color:#0f172a;
    classDef edge fill:#cffafe,stroke:#0e7490,stroke-width:1px,color:#0f172a;
    classDef service fill:#fef3c7,stroke:#92400e,stroke-width:1px,color:#0f172a;
    classDef datastore fill:#fee2e2,stroke:#991b1b,stroke-width:1px,color:#0f172a;
    classDef cache fill:#fed7aa,stroke:#9a3412,stroke-width:1px,color:#0f172a;
    classDef queue fill:#ede9fe,stroke:#5b21b6,stroke-width:1px,color:#0f172a;
    classDef compute fill:#d1fae5,stroke:#065f46,stroke-width:1px,color:#0f172a;
    classDef storage fill:#e5e7eb,stroke:#374151,stroke-width:1px,color:#0f172a;
    classDef external fill:#fce7f3,stroke:#9d174d,stroke-width:1px,color:#0f172a;
    classDef obs fill:#f3e8ff,stroke:#6b21a8,stroke-width:1px,color:#0f172a;
    class Txn service;
    class WAL,Heap datastore;

Mechanics: - Commit writes to WAL and calls fsync() before returning success. - Heap pages catch up later (lazy write). - After crash, WAL replay reconstructs missing heap writes.

Group commit batches fsync() calls across many transactions to amortize disk cost.

BASE - the relaxed alternative#

NoSQL systems often trade ACID for BASE (Basically Available, Soft state, Eventual consistency). Same words, different stance:

  • Basically Available: reads/writes succeed under failures, possibly with stale data.
  • Soft state: state may change over time without new input (background replication).
  • Eventual consistency: replicas converge to the same value if no new writes.

See CAP and PACELC for the formal trade.

Distributed ACID#

Single-node ACID is well-understood. Distributed transactions add complexity:

  • 2PC (two-phase commit): prepare + commit phases. Blocks on coordinator failure.
  • Sagas: long-running, compensating actions instead of distributed locks.
  • Spanner / CockroachDB: serializable transactions using synchronized clocks + Raft.

See distributed-transactions.

Practical implications#

  • ACID has a cost: every commit pays an fsync (1-5ms on SSD, 10ms on HDD).
  • Group commit + batch writes amortize this.
  • Read-heavy systems can use replicas (eventually consistent reads).
  • Cross-shard transactions are slow; design schemas so transactions stay local.

How ACID is implemented#

flowchart TB
  ACID((ACID))
  MVCC[MVCC isolation levels<br/>the I expanded]
  WAL[Write-ahead log<br/>backs A and D]
  CAP[CAP and PACELC<br/>where ACID maps in distributed]
  DT[Distributed transactions<br/>2PC, Sagas]
  ACID --> MVCC
  ACID --> WAL
  ACID --> CAP
  ACID --> DT

    classDef client fill:#dbeafe,stroke:#1e40af,stroke-width:1px,color:#0f172a;
    classDef edge fill:#cffafe,stroke:#0e7490,stroke-width:1px,color:#0f172a;
    classDef service fill:#fef3c7,stroke:#92400e,stroke-width:1px,color:#0f172a;
    classDef datastore fill:#fee2e2,stroke:#991b1b,stroke-width:1px,color:#0f172a;
    classDef cache fill:#fed7aa,stroke:#9a3412,stroke-width:1px,color:#0f172a;
    classDef queue fill:#ede9fe,stroke:#5b21b6,stroke-width:1px,color:#0f172a;
    classDef compute fill:#d1fae5,stroke:#065f46,stroke-width:1px,color:#0f172a;
    classDef storage fill:#e5e7eb,stroke:#374151,stroke-width:1px,color:#0f172a;
    classDef external fill:#fce7f3,stroke:#9d174d,stroke-width:1px,color:#0f172a;
    classDef obs fill:#f3e8ff,stroke:#6b21a8,stroke-width:1px,color:#0f172a;
    class ACID service;
    class MVCC,WAL,CAP,DT datastore;

Glossary & fundamentals#

Tag Concept What it is Page
HLD MVCC isolation levels the I in detail mvcc-isolation-levels
HLD CAP and PACELC how ACID maps onto CAP cap-pacelc
HLD Distributed transactions ACID across nodes distributed-transactions
HLD Write Ahead Log how A and D are implemented wal

Quick reference#

The four#

Letter Guarantee Mechanism
Atomicity All or nothing WAL + undo
Consistency Invariants preserved Constraints, triggers
Isolation No concurrent interference MVCC, 2PL
Durability Committed = persistent fsync, WAL replay

Isolation levels#

  • Read Uncommitted (rare)
  • Read Committed (PG default)
  • Repeatable Read (MySQL default)
  • Snapshot
  • Serializable

Durability cost#

Every commit → fsync (~1-5ms SSD). Group commit batches across txns.

BASE alternative#

  • Basically Available
  • Soft state
  • Eventual consistency

Distributed ACID#

  • 2PC: blocks on coordinator failure
  • Sagas: compensating actions
  • Spanner / CockroachDB: synchronized clocks + Raft

Anti-patterns#

  • App-level "consistency" left implicit (use DB constraints)
  • autocommit=true for multi-step logical ops
  • Long-held transactions blocking writers
  • Read-Modify-Write without locks/CAS

Refs#

  • Gray & Reuter - Transaction Processing
  • DDIA ch 7
  • PostgreSQL Concurrency Control docs
  • Spanner paper

FAQ#

What are the four ACID properties?#

Atomicity makes a transaction all-or-nothing, Consistency keeps invariants intact, Isolation prevents concurrent transactions from interfering, and Durability ensures committed data survives crashes.

What is the difference between ACID and BASE?#

ACID favors strong consistency and is typical of relational databases. BASE favors availability and eventual consistency, common in NoSQL stores that scale horizontally.

Does NoSQL support ACID transactions?#

Many modern NoSQL systems support ACID within a single document or partition. Distributed multi-key ACID needs a transaction coordinator like MongoDB sessions, DynamoDB transactions, or Spanner.

How does a database guarantee durability?#

Durability is achieved by writing changes to a write-ahead log on disk before acknowledging commit. After a crash the log is replayed so all committed transactions survive.

What does isolation level mean in ACID?#

Isolation level defines how concurrent transactions see each other. Levels range from read uncommitted (loose) through repeatable read up to serializable, which behaves as if transactions ran one at a time.

Further reading#

Video walkthrough

ACID Properties in Databases With Examples : via ByteByteGo