MVCC & isolation
Studies in this cluster, in series order. Each one keeps its own URL.
Databases
Indexes, isolation, storage engines, shard and partition keys, and zero-downtime migrations you can ship without a maintenance window.
MVCC & isolation
6 studies- 1.MVCC, Snapshot Isolation & Write Skewxmin/xmax visibility; SI vs SSI; write skew; SERIALIZABLE + 40001 retry; FOR UPDATE; VACUUM/bloat.
- 2.Isolation Levels Deep DiveIsolation levels are the contract for what concurrent transactions may see. ANSI labels hide engine gaps: Postgres RR is SI, MySQL RR uses gap locks, and write skew sits outside the ANSI phenomena list.
- 3.SSI vs Snapshot IsolationSnapshot Isolation stops lost updates on the same row but allows write skew. Postgres SERIALIZABLE (SSI) tracks rw-conflicts and aborts one txn with SQLSTATE 40001; retry the whole transaction.
- 4.Row Locks: SELECT FOR UPDATESELECT FOR UPDATE materializes the read set under SI so concurrent writers wait instead of write-skewing. Know NOWAIT, SKIP LOCKED, lock duration, and deadlock order.
- 5.VACUUM, Bloat & XID WraparoundDead MVCC versions stay until no snapshot can see them. VACUUM reclaims them and freezes XIDs; lag plus long txns cause bloat and wraparound that refuses writes.
- 6.Phantoms vs Write SkewPhantoms are new rows matching a predicate; write skew is disjoint writes that break a multi-row invariant. SI allows both; SSI, gap/predicate locks, or constraints close them differently.