Decode Each Letter of ACID

🟢 Junior Level

ACID — a set of four properties that guarantee reliable transaction processing in a database.

A — Atomicity

A transaction either completes entirely or not at all. Either all changes are saved, or none of them are.

Example: When transferring money from one account to another, two operations must occur: debit from the first account and credit to the second. If the second operation fails — the DBMS uses the undo log (journal of old values) to roll back the first operation.

C — Consistency

A transaction transitions the database from one valid state to another.

Valid state = all constraints (FOREIGN KEY, UNIQUE, CHECK, NOT NULL) are satisfied. If at least one is violated — the state is invalid, and the DBMS will roll back the transaction.

Example: If the database has a constraint that balance cannot be negative, a transaction will not be able to write a negative balance.

I — Isolation

Concurrent transactions should not interfere with each other. Each transaction should work as if it were the only one running.

Example: If two people transfer money from the same account simultaneously, the results should not get mixed up.

D — Durability

Once a transaction is confirmed (COMMIT), changes are permanent — even if the server suddenly shuts down.

🟡 Middle Level

How ACID Is Implemented in Practice

Atomicity is ensured through transaction logs:

Before modifying data, the DBMS writes old values to the undo log
If a transaction is cancelled (ROLLBACK), the system restores data from the log
If a power failure occurs, the redo log helps restore committed transactions

Consistency operates at two levels:

Database-level: FOREIGN KEY, UNIQUE, CHECK constraints, data types
Application-level: Business rules that the developer must ensure themselves (e.g., the sum of transfers must equal the total amount)

Isolation is implemented through:

Pessimistic Locking: Explicit row locks (SELECT FOR UPDATE)
MVCC (Multi-Version Concurrency Control) — the DBMS stores multiple versions of the same row simultaneously. Each version is tied to the transaction ID that created it. This way, readers don’t block writers.

Durability is ensured through:

Write-Ahead Logging (WAL): Data is first written to the log on disk, then to data files.

Why: If the server crashes after COMMIT but before writing to the main data file, WAL allows recovery on restart.

fsync(): Guarantees physical write to disk, not just OS cache
Group Commit: Batching multiple confirmations into a single disk operation

Common Mistakes

Confusing Consistency (from ACID) with consistency in distributed systems (CAP theorem)
Thinking Isolation means complete absence of interference — anomalies are possible at levels below Serializable

🔴 Senior Level

Deep Understanding of Implementation Mechanisms

Atomicity — Internal Implementation

Undo Log / Redo Log: PostgreSQL uses WAL (Write-Ahead Log). All changes are first written to WAL, then to data files. During crash recovery, WAL replay restores committed transactions via redo, while uncommitted ones are rolled back using undo information stored in the pages themselves (heap tuples).
Shadow Paging: An alternative approach (SQLite, Firebird) — changes are made to a copy of the page, and on commit the pointer is switched.

Consistency — Beyond Constraints

In distributed systems, ACID consistency is contrasted with Eventual Consistency (CAP theorem)
Application-level invariants: The DB doesn’t know that the sum of transfers between accounts must remain unchanged — this is the developer’s responsibility
Deferred constraints: PostgreSQL allows deferring constraint checks until the end of a transaction (DEFERRABLE)

Isolation — MVCC Deep Dive

PostgreSQL MVCC: Each row stores xmin and xmax — transaction IDs that determine visibility. Readers don’t block writers and vice versa.

Relevant for PostgreSQL 14-16. Implementation details may change between major versions.

PostgreSQL SSI (Serializable Snapshot Isolation): Since version 9.1, PostgreSQL uses SSI for the Serializable level. The system tracks read/write dependencies and aborts transactions with a serialization failure error when conflicts are detected.
Trade-off: Perfect isolation (Serializable) comes at a high cost. Most systems operate on Read Committed, tolerating certain anomalies.

Durability — Production Nuances

synchronous_commit = off in PostgreSQL increases throughput but risks losing ~0.5-1 sec of data on crash
fsync() overhead: On some SSDs, fsync can be expensive. Important to consider during benchmarking
Group Commit: An optimization that batches multiple transactions into a single I/O operation to reduce overhead
Battery-backed RAID controllers: In enterprise environments, hardware controllers with batteries guarantee durability even without fsync

Performance Trade-offs

Disabling synchronous_commit gives 2-5x throughput but with data loss risk
MVCC requires storing multiple row versions → table bloat → VACUUM overhead
Serializable level requires retry logic in the application

Production Recommendations

For most use cases: Read Committed + proper error handling
For financial transactions: Serializable with retry logic
Monitor txid exhaustion in PostgreSQL (32-bit transaction IDs)
Configure autovacuum aggressively to prevent bloat under high write load

🎯 Interview Cheat Sheet

Must know:

ACID = Atomicity, Consistency, Isolation, Durability — 4 properties of reliable transactions
Atomicity = all or nothing, implemented via undo/redo log
Consistency = transition from one valid state to another (constraints + application invariants)
Isolation = concurrent transactions don’t interfere with each other (MVCC, locks)
Durability = COMMIT = data saved permanently (WAL, fsync)
MVCC — key mechanism: PostgreSQL stores xmin/xmax on every row
Durability is ensured by Write-Ahead Logging: log to disk first, then data
At the Serializable level, PostgreSQL uses SSI (Serializable Snapshot Isolation) with RW-dependency tracking

Common follow-up questions:

How does Consistency in ACID differ from Consistency in CAP? — ACID Consistency = DB constraints, CAP Consistency = all nodes see the same data
What happens on power failure after COMMIT? — WAL replay restores data on restart
Why doesn’t PostgreSQL MVCC require read locks? — Each row has versions tied to transaction IDs
What are Deferred Constraints? — Constraints whose validation is deferred until COMMIT (DEFERRABLE in PostgreSQL)

Red flags (DO NOT say):

“Consistency in ACID is the same as eventual consistency” — these are different concepts
“Isolation means complete absence of transaction interference” — anomalies are possible below Serializable
“Durability = data goes straight to disk” — WAL first, then async flush to data files

Related topics:

[[2. What transaction isolation levels exist]]
[[11. What is the default isolation level in PostgreSQL]]
[[18. What is rollback in transactions]]
[[6. What is Serializable]]