A deep-dive guide to ACID properties — Atomicity, Consistency, Isolation, Durability — covering isolation levels, MVCC, 2PL, WAL, write skew, distributed ACID, and practical interview questions. Includes the real priority order (I → C → D → A), the AI PR story, and the link to the full isolation levels deep-dive.
Deep-dive into all 5 isolation anomalies (dirty read, non-repeatable, phantom, lost update, write skew), the 4 standard isolation levels, PostgreSQL vs MySQL vs Oracle implementation differences, snapshot scope (per-statement vs per-transaction), practical fix strategies without raising isolation, and the spec vs implementation distinction.
Strategies for dealing with race conditions, concurrent writes, hot spots, and lock contention in distributed systems including optimistic locking, MVCC, distributed locks, and queue-based serialization.
Comprehensive deep dive into PostgreSQL internal storage mechanics — 8KB slotted pages, HeapTupleHeader, CTID double-hop lookup, the UPDATE dilemma, HOT optimization, MVCC visibility, and VACUUM mechanics based on Hussein Nasser's database engineering architecture.
ACID properties, isolation levels, locking mechanisms, MVCC, deadlocks, and optimistic vs pessimistic concurrency.
