Serializable isolation pitfalls — write skew, retry storms, and when advisory locks beat SSI?

Q: Serializable isolation pitfalls — write skew, retry storms, and when advisory locks beat SSI?

**Target Audience:** Developers who know what a transaction is and have heard of isolation levels, meeting write skew and SERIALIZABLE retries for the first time; and candidates who must defend a locking strategy in a design interview. **Difficulty:** Hard **Engines:** PostgreSQL 16.15 (two real `psql` sessions for every anomaly and error, `pgbench` 16 for the retry numbers, the verifier for the examples) and SQLite via sql.js 1.10.2 for the runnable examples. MySQL is mentioned only where cited. > **How to read this doc:** Every idea is explained in plain language first, then tagged with `📌 Interview term:` so you also learn the exact words an interviewer expects. Every result, plan and error message below was produced by actually running it; the **Verified** section quotes the example output in full. ## 1. Why This Even Matters — A Story First A hospital rule says: every night at least one doctor must be on call. Tonight Alice and Bob are both on call. Both feel unwell. Alice ope

The problem

Question presented to candidate: "We moved a critical booking flow to SERIALIZABLE to stop a write-skew bug, and now under load we see lots of 'could not serialize access' errors and slow retries. Explain what write skew is, why SERIALIZABLE throws these errors, and when you would use an explicit lock such as an advisory lock instead."

What a strong answer should cover:

Write skew: two transactions read the same data, each writes a different row based on what it read, and together they break a rule neither broke alone; REPEATABLE READ (snapshot isolation) allows it
How PostgreSQL SERIALIZABLE (SSI) catches it: it tracks read/write dependencies with SIRead predicate locks and aborts one transaction with SQLSTATE 40001; it never blocks to do so
The contract SSI imposes: every transaction involved must run at SERIALIZABLE, and the application must retry the whole transaction on 40001
Pitfalls: false positives from coarse predicate locks (whole relation on a sequential scan, pages on small indexes), retry storms on hot rows, side effects repeated on retry
Alternatives: SELECT ... FOR UPDATE on the rows the decision depends on, pg_advisory_xact_lock on a key that names the rule (works even when the rows do not exist yet), or a constraint when the rule can be expressed as one
How to choose: SSI for many varied, rarely conflicting rules; explicit locks for a few known, hot invariants

Clarifying questions expected:

Which invariant is being protected, and does it depend on rows that might not exist yet (no row to lock)?
How hot is the contention: many writers on the same key, or occasional overlaps?
Are there side effects (emails, payments, external calls) inside the transaction that a retry would repeat?

Code / implementation expected: Yes — reproduce write skew under REPEATABLE READ, show SERIALIZABLE rejecting it, and show the same rule enforced with an advisory lock.

Schema used by the examples:

isolation
serializable
write-skew

Try it yourself

Sketch your approach before you read the solution. That is what the interview tests.

Stuck? Get a hint.

A short nudge toward the approach. It will not give the answer away.

Solution

Target Audience: Developers who know what a transaction is and have heard of isolation levels, meeting write skew and SERIALIZABLE retries for the first time; and candidates who must defend a locking strategy in a design interview. Difficulty: Hard Engines: PostgreSQL 16.15 (two real psql sessions for every anomaly and error, pgbench 16 for the retry numbers, the verifier for the examples) and SQLite via sql.js 1.10.2 for the runnable examples. MySQL is mentioned only where cited.

How to read this doc: Every idea is explained in plain language first, then tagged with 📌 Interview term: so you also learn the exact words an interviewer expects. Every result, plan and error message below was produced by actually running it; the Verified section quotes the example output in full.

1. Why This Even Matters — A Story First

A hospital rule says: every night at l

The solution is hidden so you can try first

18 min read · 11 sections

Run the code

Edit the query and run it against the tables for this question.

SQLThe rule to protect: at least one doctor on call per shift

SQLWhat write skew leaves behind: both updates applied, the rule broken

PostgreSQL: the SIRead predicate lock a SERIALIZABLE read takes (whole table on a seq scan)

BEGIN ISOLATION LEVEL SERIALIZABLE;
CREATE TEMP TABLE seen AS
  SELECT count(*) AS on_call FROM doctors
  WHERE shift_date = '2026-10-20' AND on_call = 1;
SELECT locktype, relation::regclass AS relation, page, tuple, mode
FROM pg_locks
WHERE mode = 'SIReadLock' AND pid = pg_backend_pid()
ORDER BY locktype, page, tuple;
COMMIT;

PostgreSQL: an advisory lock key that names the rule, taken for this transaction only

BEGIN;
SELECT hashtext('oncall:2026-10-20') AS lock_key,
       pg_try_advisory_xact_lock(hashtext('oncall:2026-10-20')) AS got_lock;
COMMIT;

Discussion

Other approaches, follow-ups and how it went in real interviews.

Sign in to join the discussion.

No comments yet. Share how you would answer it.

The solution is hidden so you can try first

Keep your momentum going.