Debugging Write Skew Under Serializable Isolation

A ledger service reports balanced books in every unit test, then silently overdraws an account in production once concurrency exceeds a handful of transactions per second. The transactions never violate a foreign key, never throw a deadlock, and each individually reads consistent data. The root cause is write skew, and it survives precisely because the team assumed REPEATABLE READ or naive snapshot isolation was equivalent to true serializability. It is not. This article covers how serializable snapshot isolation (SSI) closes that gap, why it still produces confusing false-positive aborts, and how to tune it without collapsing throughput.

#The Precise Bottleneck: Snapshot Isolation Is Not Serializability

Under classic snapshot isolation, each transaction reads from a consistent snapshot taken at start time and writes are checked only for direct write-write conflicts. Two transactions that read overlapping data and each write to disjoint rows based on that read will both commit successfully, even though no serial execution of the two transactions would have produced that combined result. This is the textbook write skew

Financial systems hit this constantly with balance checks. Consider two withdrawals against a joint account with a non-negative balance constraint enforced in application logic rather than a database constraint:

1-- Transaction A
2BEGIN;
3SELECT balance FROM accounts WHERE id = 42; -- returns 100
4-- application checks 100 - 60 >= 0, proceeds
5UPDATE accounts SET balance = balance - 60 WHERE id = 42;
6COMMIT;
7
8-- Transaction B, concurrent, same snapshot
9BEGIN;
10SELECT balance FROM accounts WHERE id = 42; -- also returns 100
11-- application checks 100 - 70 >= 0, proceeds
12UPDATE accounts SET balance = balance - 70 WHERE id = 42;
13COMMIT;

The exponential backoff is not optional. Under contention, retrying immediately without a jittered delay reproduces the same rw-conflict pattern and produces a retry storm that looks identical to a livelock from the outside.

#Configuration Tuning

1# postgresql.conf - sizing for high-concurrency SSI workloads
2max_pred_locks_per_transaction = 256
3max_pred_locks_per_relation = -2   # 2x max_connections heuristic
4max_pred_locks_per_page = 4
5default_transaction_isolation = 'read committed'  # keep default cheap; opt in explicitly

Leaving default_transaction_isolation at read committed and opting into serializable per transaction avoids paying the SIREAD bookkeeping cost on every connection pool query, including migrations and health checks.

#Failure Modes and Edge Cases

The certifier’s SIREAD lock table is finite. When max_pred_locks_per_relation is exhausted, PostgreSQL escalates locks from per-tuple to per-page to per-relation granularity. This escalation dramatically increases false-positive aborts because a relation-level SIREAD lock conflicts with almost any concurrent write to that table, regardless of actual row overlap. Under sustained high-concurrency load with insufficient lock table sizing, you will observe serialization failure rates climbing non-linearly even though the underlying data contention hasn’t changed — the symptom is a saw-tooth throughput graph as the system periodically restarts transactions.

Long-running serializable transactions are the other major failure mode. Because SIREAD locks persist until the oldest concurrent serializable transaction commits, a single analytical query left open under SERIALIZABLE for minutes will pin the certifier’s dependency graph, preventing garbage collection of predicate locks and inflating memory use in pg_stat_activity-visible backends. Idle-in-transaction timeouts become mandatory operational hygiene, not an optional safeguard, once serializable snapshot isolation is in play.

A subtler edge case: read-only transactions still participate in the dependency graph and can still be the one aborted, unless explicitly marked READ ONLY DEFERRABLE, which allows PostgreSQL to instead delay the snapshot until a safe one is available rather than aborting later.

#Isolation Level Comparison

#Scaling and Security Trade-offs

Adopting serializable snapshot isolation is an explicit trade of raw throughput for correctness guarantees, and the trade-off surface widens as you scale horizontally. When designing the surrounding architectural patterns for a service layer sitting on top of a serializable database, weigh the following:

• Connection pool sizing — SIREAD bookkeeping grows with concurrent serializable transactions; PgBouncer transaction-mode pooling amplifies short-lived SSI transactions well, but session-mode pooling leaves predicate locks held longer than necessary.
• Retry amplification as a DoS surface — an attacker who can trigger high-contention write paths (e.g. a public “claim voucher” endpoint) can deliberately induce serialization failures across unrelated transactions sharing the same hot predicate, effectively a low-cost denial-of-service against write availability.
• Read replica divergence — SSI guarantees apply only within a single serializable cluster; if reads are served from asynchronous replicas for the invariant check while writes commit on the primary, the anomaly reappears because the replica’s snapshot is stale relative to the certifier’s view.
• Cross-service transactions — serializable snapshot isolation solves single-database write skew; it does nothing for invariants spanning two microservices’ separate databases, which still require sagas or two-phase commit
  The KBY Lexicon

  Two-Phase Commit (2PC)

  A blocking atomic commitment protocol that ensures all participants in a distributed transaction either commit or abort together, coordinated by a single transaction manager.

  Read Full Context
  with their own distinct failure modes.
• Observability cost — tracking pg_stat_database.xact_rollback against serialization failure counters specifically (rather than generic rollback counts) is necessary to distinguish application bugs from expected SSI aborts during capacity planning.

None of this argues against using serializable snapshot isolation for genuinely invariant-sensitive write paths — it argues for scoping it precisely. Apply it to the transactions that actually enforce cross-row invariants, leave everything else on cheaper isolation levels, and instrument serialization failures as a first-class metric rather than folding them into generic error rates. Treated that way, SSI becomes a targeted correctness tool rather than a blanket performance tax across the whole write path.