transactions • Advanced

ACID Properties & Write-Ahead Logging (WAL)

ACID guarantees database reliability: Atomicity (All or Nothing), Consistency (Constraints preserved), Isolation (Concurrent transactions do not interfere), and Durability (Committed data survives crashes via WAL).

Intuitive Mental Model

The Flight Reservation & Bank Transfer: Either both the bank debit and credit succeed (Atomicity), or the transaction rolls back completely with zero lost funds.

SQL DDL / DML QueryPostgreSQL / MySQL

BEGIN TRANSACTION;

-- Deduct from Account A:
UPDATE accounts SET balance = balance - 500 WHERE id = 1 AND balance >= 500;

-- Credit Account B:
UPDATE accounts SET balance = balance + 500 WHERE id = 2;

-- Guarantee durability to disk:
COMMIT;

Key Architectural Takeaways

•Write-Ahead Logging (WAL): Changes are written sequentially to the append-only WAL log on disk BEFORE dirty data pages are flushed to disk.
•Crash Recovery: Upon unexpected power cut, the database replays the WAL log (REDO) to reconstruct committed state and UNDO uncommitted transactions.
•Checkpointing periodically flushes dirty in-memory pages to disk, bounding recovery time on restart.

Common Production Mistake

Disabling fsync in production for short-term write speed benchmarks, risking complete database corruption on server power loss.