hardSystem Design

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?

`SERIALIZABLE` (SSI) prevents write-skew by aborting one of two concurrent txns that together violate an invariant (`ERROR 40001`). The app *must* retry the whole txn with backoff. For hot rows, `FOR UPDATE` or `pg_advisory_xact_lock` is faster than retry storms. Serializable pitfalls: write skew, retry loops, and advisory locks as escape hatch Write Skew Example 2 doctors, on-call constraint each txn reads other is on-call both go off-call → violates rule Se

1.1k views

Understand the problem

Handle 40001 serialization failures with idempotent retries and choose pessimistic locking for hot spots.

isolationserializablewrite-skew

Attempt it yourself

Sketch your approach before reading the solution — that's what interviews test.

Nudge consolestandby

Stuck? Beam a request up — the console returns a conceptual nudge that guides your logic without spoiling the implementation.

Study the solution

SERIALIZABLE (SSI) prevents write-skew by aborting one of two concurrent txns that together violate an invariant (ERROR 40001). The app must retry the whole txn with backoff. For hot rows, FOR UPDATE or pg_advisory_xact_lock is faster than retry storms.

Solution ready — 2 min read

Classified // press E to declassify

Join the discussion

Discussion (0)

No responses yet. Be the first to share what you think.

Transmission complete // awaiting log

KEEP THE
STREAK ALIVE.

Dossier 52 of 70 decoded in the SQL track. One more won't hurt.

Back to track

Next up→

JSONB vs normalized columns vs EAV — how do you model flexible attributes without killing query performance?

Continue