Transactions & isolation levels

**ACID in practice**: Atomicity is guaranteed by the WAL (Write-Ahead Log — every change is logged before applied, so recovery replays committed + undoes in-flight). Durability is the WAL fsync guarantee. Consistency is constraint enforcement (PK, FK, CHECK, NOT NULL). Isolation is the concurrency control (MVCC in Postgres/Oracle, locking in SQL Server by default).

**MVCC (Multi-Version Concurrency Control)**: used by Postgres, Oracle, MySQL/InnoDB. Each row has version metadata (xmin/xmax in Postgres). Readers see a snapshot of committed data AS OF a specific transaction ID — readers never block writers, writers never block readers. Writers that conflict (UPDATE/DELETE on the same row) take a row-level lock and may block or fail with a serialization error.

Isolation anomalies matrix (SQL standard):

| Level | Dirty | Non-repeatable | Phantom | Lost update | Write skew |

|---|---|---|---|---|---|

| Read Uncommitted | ✓ (can occur) | ✓ | ✓ | ✓ | ✓ |

| Read Committed | ✗ | ✓ | ✓ | ✓ | ✓ |

| Repeatable Read | ✗ | ✗ | ✓ in standard / ✗ in Postgres+MySQL | ? | ✓ |

| Serializable | ✗ | ✗ | ✗ | ✗ | ✗ |

**Postgres specifics**: default is Read Committed. Repeatable Read = snapshot isolation — no phantoms in practice, but write skew possible. Serializable uses SSI (Serializable Snapshot Isolation) — optimistic, aborts transactions that would violate serializability, can retry.

**Lost update** and **write skew** (sneaky ones): lost update = two transactions both read X=5 and both write X=6 when they should have written X=7. Classic with naïve 'read, modify, write' patterns. Fix: SELECT FOR UPDATE (pessimistic lock) or optimistic concurrency (version column + compare-and-swap).

Write skew: two transactions each read the same set, check a condition (e.g., 'at least one doctor on call'), each independently concludes they can go off-call, both write. Final state: nobody on call. Requires Serializable (or explicit predicate locks).

Savepoints: sub-transactions within a transaction. SAVEPOINT s1; ... ROLLBACK TO s1; — undoes work since the savepoint without aborting the whole transaction. Useful for implementing try/catch in stored procs and in some ORMs (e.g., Django's nested transactions).

**Long transactions are anti-patterns**: hold locks, generate WAL that can't be reclaimed, cause replication lag, bloat autovacuum in Postgres. Keep transactions short, do heavy work in batches with commit between.