🔐ACID properties

The OLTP reliability contract

ACID is how banks, hospitals, and inventory systems sleep at night: transactions either land completely or vanish, constraints never lie, concurrent sessions see controlled illusions, and committed bytes survive power loss. When any letter weakens, product bugs become forensic puzzles.

📜The four letters

Interview answers should tie each letter to a failure mode users would notice.

Property	Meaning	If it broke…
Atomicity	All statements in a transaction commit, or none persist.	Half a money transfer posts, books never reconcile.
Consistency	Constraints + rules true before and after commit.	Negative inventory, duplicate MRN numbers, impossible states.
Isolation	Concurrent transactions cannot observe forbidden intermediate mixes.	Reads flicker mid-transfer; aggregates lie under race.
Durability	After COMMIT success, data survives crash (modulo replication lag caveats).	“We charged the card but the order vanished.”

Assuming defaults are “safe enough”

Treating READ COMMITTED as if it removed every race, phantoms and write skew still exist until you add locks, constraints, or stronger isolation.
Holding a transaction open across slow network calls, your latency becomes everyone’s lock duration.
Testing only single-threaded paths; races show up under concurrent load or chaos tests.

Match isolation and scope to invariants

Document isolation next to schemas and services that share a database.
Keep transactions short: few rows, no user think-time inside BEGIN.
Use capped retries for serializable conflicts; pair critical writes with outbox patterns when you need “DB + message.”

🪵Durability: The WAL and fsync()

How data survives a power pull.

Write-Ahead Logging (WAL)

Writing to an 8KB data page is slow and random. Instead, the engine appends changes to a sequential WAL file first. A transaction is "Committed" only once the WAL entry is fsynced to disk. If the DB crashes, it replays the WAL to reconstruct the data pages.

LSN (Log Sequence Number)

Every log entry has a unique, increasing 64-bit ID called an LSN. Data pages store the LSN of the last update; during recovery, the engine skips any WAL entry older than the page's LSN.

The Checkpointer

The background process that flushes "dirty" pages from the Buffer Pool to the physical table files. Once flushed, the old WAL files can be recycled.

🚑Atomicity: The Undo Log

Aborting a transaction shouldn't leave scars.

To provide Atomicity, the engine tracks the "Before Image" of every row. In MySQL/InnoDB, this is the Undo Log. If you ROLLBACK, the engine applies the undo records to revert the state. In PostgreSQL, MVCC handles this by keeping the old version until vacuumed.

SQL

Atomic transfer (illustrative)

BEGIN ISOLATION LEVEL SERIALIZABLE;
UPDATE wallets SET usd = usd - 25 WHERE user_id = 9;
UPDATE wallets SET usd = usd + 25 WHERE user_id = 12;
COMMIT;

✓Illustrative result set

observation
Both updates succeed or neither appears after crash

System design tie-in

Microservices often split the monolith database, distributed transactions get expensive. Saga + idempotent retries recover cross-service flows while each service keeps local ACID.

TL;DR

ACID is the contract for OLTP transactions: Atomicity (commit all statements or roll back none), Consistency (constraints hold before and after commit), Isolation (concurrent sessions see only allowed histories, tuned via isolation levels), and Durability (committed work survives crashes via WAL/redo). Weaker isolation improves throughput but admits anomalies you must accept or detect in application logic.