8.7 Technology deep dives
7.1 PostgreSQL MVCC + SSI
Problem it solves. Full serializability without readers blocking writers, on a single node, with predictable latency.
Why not 2PL? Unstable latencies, terrible high percentiles under contention, a table-scanning transaction blocking all writers, frequent deadlocks (§4.2).
Why not serial execution? Bounded by one core, requires stored procedures and an in-memory dataset.
How it works internally. §3.2's MVCC (xmin/xmax on every tuple, versions in the heap, visibility computed from a snapshot's transaction list) plus §4.3's SSI (SIREAD locks recording what each transaction read, tracking rw-dependencies, aborting a transaction that sits in a "dangerous structure" of two consecutive rw-edges). Serialization failures surface as SQLSTATE 40001.
Deployment. default_transaction_isolation = 'serializable' per-database or per-transaction; the application MUST have a retry loop on 40001, or serializable mode is worse than useless.
Monitoring.
- Serialization failure rate (40001) and deadlock rate (40P01) — the two abort classes, with different causes
max_pred_locks_per_transactionexhaustion — when exceeded, predicate locks are escalated to coarser granularity, causing a cascade of false-positive aborts- Long-running transactions (
pg_stat_activitywherexact_startis old) — these hold back the xmin horizon, block vacuum, and inflate SSI tracking - Table and index bloat, autovacuum progress, and transaction ID wraparound age (
age(datfrozenxid)) — the §3.2 32-bit txid hazard, and a genuine "database refuses writes" outage - Lock waits by lock type
Scaling. Read replicas for read-only work at snapshot isolation. Write throughput is single-node.
Backup. Base backup + WAL archiving. Note the §3.2 connection: a consistent backup is exactly a long-running snapshot-isolation read — which is why backups and vacuum fight each other.
What actually breaks.
- No retry loop. The application gets 40001 and shows the user an error. This is the single most common serializable-Postgres failure, and it's an application bug — §2.3's ORM indictment made concrete.
- A long-running analytics query on the primary pins the xmin horizon, vacuum can't remove dead tuples, bloat grows unbounded, and eventually every query slows.
- Predicate lock escalation turning a moderate workload into an abort storm.
SELECT FOR UPDATEdeadlocks from inconsistent lock ordering across code paths.- Assuming "repeatable read" is repeatable read. It's snapshot isolation, and it does not prevent write skew.
- Transaction ID wraparound on a high-churn table with autovacuum tuned too conservatively.
7.2 MySQL / InnoDB (2PL for serializable, gap locks for phantoms)
Problem it solves. Serializability using the classical, well-understood locking approach, with next-key locks handling phantoms.
Why gap locks? §4.2's index-range locking: a predicate lock is too expensive to evaluate, so InnoDB locks the index record plus the gap before it — the concrete implementation of "approximating a predicate by matching a greater set of objects."
How it works internally. MVCC for consistent reads at REPEATABLE READ; shared/exclusive record locks plus gap locks and next-key locks (record + preceding gap) for locking reads and for SERIALIZABLE. At SERIALIZABLE, plain SELECT is implicitly SELECT … LOCK IN SHARE MODE.
Monitoring. SHOW ENGINE INNODB STATUS for the latest deadlock; Innodb_row_lock_time_avg and _max; lock wait timeouts (innodb_lock_wait_timeout, default 50 s — long enough to look like a hang); history list length (the MVCC undo backlog, InnoDB's analogue of Postgres bloat).
What actually breaks.
- Gap locks blocking inserts into ranges nobody has rows in — the classic "why is my INSERT waiting on a SELECT" mystery. Especially bad when a query has no useful index, because then it locks far more than intended (§4.2's "fallback to a table lock" in a milder form).
REPEATABLE READnot detecting lost updates — unlike Postgres/Oracle/SQL Server (§3.3). People assume MySQL RR ≡ Postgres RR. It isn't.- Deadlocks under 2PL far more frequent than under read committed, each one wasting all the transaction's work.
- A long-running read transaction growing the history list until purge can't keep up.
- Lock wait timeout treated as a permanent error rather than a retryable one.
7.3 VoltDB / H-Store (serial execution + stored procedures)
Problem it solves. Serializability with zero concurrency-control overhead, by removing concurrency.
Why viable now? §4.1: cheap RAM (whole active dataset in memory) and the realization that OLTP transactions are short.
How it works internally. One single-threaded execution engine per CPU core, each owning a shard; transactions are stored procedures submitted whole (no interactive round trips); replication by executing the same procedure on each replica — state machine replication, which requires determinism (special deterministic APIs for time and randomness).
Monitoring. Per-partition transaction latency; the fraction of transactions that are cross-shard (the metric that decides whether the architecture works at all); procedure execution time p99 (one slow procedure stalls its entire partition); snapshot completion.
Scaling. Linear in cores as long as transactions are single-shard. Cross-shard writes: ~1,000/s, orders of magnitude below single-shard, and NOT improvable by adding machines.
What actually breaks.
- One slow stored procedure stalls everything on that partition — §4.1's constraint #1, and it's absolute.
- A partitioning scheme that produces many cross-shard transactions collapses throughput by orders of magnitude.
- Nondeterminism in a procedure breaking replication (the same failure mode as Ch 5's durable execution and Ch 6's statement-based replication — the theme recurs everywhere replay is involved).
- The working set exceeding memory.
7.4 Spanner / CockroachDB / FoundationDB (distributed transactions done properly)
Problem it solves. ACID across shards and regions, without XA's single-point-of-failure coordinator.
Why not XA? All four problems in §5.4: coordinator SPOF, coordinator log on an app server's local disk, no direct coordinator↔participant communication, lowest-common-denominator API.
How they work internally. §5.5's four fixes. Each shard is a Raft/Paxos group (so a participant failing doesn't abort the transaction — the group fails over). The coordinator is itself replicated by consensus, so its commit-point log survives a node loss. Spanner uses TrueTime (GPS + atomic clocks, exposing a bounded uncertainty interval) to assign globally meaningful commit timestamps and get external consistency, at the cost of a commit-wait of a few ms. CockroachDB uses HLCs plus an uncertainty window and read refreshes. FoundationDB uses optimistic concurrency with a distributed conflict-detection tier — SSI, scaled out, which is why §4.3 cites it as the counter-example to "serializable can't scale."
Monitoring. Transaction retry rate by reason (write-write conflict, uncertainty restart, read-refresh failure); contention hotspots by key range — the single most useful distributed-transaction metric; commit latency broken down by phase; clock offset (in Spanner, TrueTime epsilon; in CockroachDB, a node exceeding max-offset shuts itself down, deliberately, to preserve correctness); range/leaseholder distribution.
Scaling. Horizontal, genuinely — but cross-shard transactions cost extra round trips, so schema design that keeps transactions within one range still matters enormously.
What actually breaks.
- Contention hotspots. A sequential primary key or a single counter row funnels every transaction through one range, and optimistic concurrency turns that into an abort storm (§4.3's "performs badly under high contention").
- Clock skew. A node whose clock drifts past the configured max offset is killed to protect correctness — availability sacrificed for safety, by design.
- Long-running read/write transactions conflicting and aborting repeatedly.
- Retries not implemented in the client, same as §7.1.
- Assuming cross-region transactions are cheap — commit-wait and consensus round trips make them tens to hundreds of ms.
7.5 XA / JTA heterogeneous transactions
Problem it solves. Atomic commit across a database and a message broker from different vendors — §5.3's exactly-once.
Why it's the wrong tool now. §5.4's four problems, plus the fact that §5.6 shows you can get exactly-once with idempotency and a message-ID table, using only local transactions.
Monitoring (if you must run it). Count of in-doubt/prepared transactions (pg_prepared_xacts in Postgres, XA RECOVER in MySQL) — this should be zero at rest, and a nonzero steady state means orphans are accumulating; age of the oldest prepared transaction; lock waits attributable to prepared transactions; coordinator log disk health.
What actually breaks.
- Orphaned in-doubt transactions holding locks forever, surviving database restarts by design, requiring manual administrator resolution under outage pressure.
max_prepared_transactionsset to 0 (the Postgres default) so XA silently doesn't work — or set high and forgotten, so a leaked prepared transaction blocks vacuum forever.- Heuristic decisions silently breaking atomicity and leaving two systems permanently disagreeing.
- The application server dying and taking the coordinator's log with it.
- No cross-system deadlock detection — the deadlock just hangs until a timeout somewhere.