ACID Transactions

A transaction is a logical unit of work that must satisfy ACID properties: Atomicity (all-or-nothing), Consistency (invariants preserved), Isolation (concurrent transactions appear serial), and Durability (committed data survives crashes). In distributed systems, achieving ACID across multiple nodes requires coordination protocols like Two-Phase Commit (2PC) or consensus-based protocols (Paxos, Raft), which introduce latency, availability risk, and blocking behavior when the coordinator fails.

Key Points

• Atomicity is implemented via rollback logs (PostgreSQL's WAL undo, InnoDB's undo tablespace): on failure, undo records reverse partial changes.
• Durability requires WAL flush to disk before acknowledging commit; fsync=off can make PostgreSQL 5–10x faster but risks data loss on power failure.
• 2PC protocol: Phase 1 (PREPARE) — coordinator asks all participants "can you commit?"; Phase 2 (COMMIT) — coordinator sends COMMIT only if all replied YES. If coordinator crashes between phases, participants are blocked.
• 2PC is "blocking" because a participant holding locks waits indefinitely for the coordinator to recover; this is why 2PC is avoided in high-availability systems.
• Saga pattern replaces 2PC with a sequence of local transactions, each publishing an event or message, with compensating transactions for rollback — no global locks held.
• Distributed deadlock: can occur across multiple databases in a 2PC scenario; detected by timeout or by building a global wait-for graph.
• Savepoints allow partial rollback within a transaction (SAVEPOINT sp1; ... ROLLBACK TO SAVEPOINT sp1) without aborting the entire transaction.
• PostgreSQL Serializable Snapshot Isolation (SSI) at SERIALIZABLE level detects and aborts serialization anomalies without locking, using predicate locks tracked in memory.