The Staff Engineer Database Concurrency Interview: Isolation, Write Skew, and Safe Retries

Two release approvers each check that somebody else is available, remove themselves, and commit. Every transaction used a consistent snapshot. The release now has no approver. That pattern is write skew: concurrent transactions change different data while breaking a constraint they both relied on, as formalized in Berenson and colleagues’ isolation-level critique.

For a staff engineer database concurrency interview, practice making that failure visible before choosing a remedy. Draw the history, state the invariant, and show why your proposed transaction boundary protects it. A list of isolation levels leaves the central question unanswered: which concurrent execution have you ruled out?

Cost deserves evidence, too. The 2012 PostgreSQL SSI paper reported a performance cost below 7% relative to snapshot isolation in its transaction-processing and web benchmark experiments. Those results concern PostgreSQL 9.1, particular workloads, and the authors’ setup. They support testing the tradeoff; they do not predict your service’s overhead or justify a universal claim that serializable is cheap.

1. Start With the Invariant, Then Define the Transaction

Ask for the condition that must remain true after every accepted operation. For our illustrative release workflow: each active release must retain at least one assigned approver. Availability is represented by rows in one authoritative database, and leaving a release requires a transaction. These are explicit exercise assumptions, not a description of a production system.

Write down the transaction’s decision, read set, and write set. “Leave release” reads the set of active approvers and changes one assignment. Adding approvers, deleting assignments, closing a release, and administrative overrides belong in your inventory of interacting operations. A proof that excludes a writer is an incomplete proof.

Separate requirements that sound similar. “A release has an approver” differs from “an approver cannot work on overlapping releases.” PostgreSQL’s range-type documentation shows exclusion constraints for preventing overlapping reservations, including overlaps within the same resource. That suggests a direct database representation for one requirement, without solving the other.

Do not casually propose a CHECK that counts neighboring rows. PostgreSQL explicitly warns that CHECK constraints cannot guarantee conditions depending on other rows. The schema and the invariant must fit each other. In your answer, distinguish a rule the database can enforce declaratively from a rule your transaction protocol must enforce.

2. Draw the Write-Skew History Before Fixing It

Use a deliberately small history. Initially Ari and Bo are assigned to release R. Each may leave only after observing another assigned approver. Both transactions use snapshot isolation, and both establish their snapshots before either commits. The following values are illustrative; the anomaly follows the write-skew pattern in the original isolation paper.

Illustrative concurrent history: two assigned approvers become zero

Step	Transaction A: Ari leaves	Transaction B: Bo leaves	Committed state
0	Not started	Not started	Ari assigned; Bo assigned
1	Read both assignments; count = 2	—	Two approvers
2	—	Read both assignments; count = 2	Two approvers
3	Set Ari’s assignment inactive	—	Two approvers
4	—	Set Bo’s assignment inactive	Two approvers
5	Commit	—	Only Bo assigned
6	—	Commit	Zero approvers: invariant broken

Now try both serial orders. If A completes first, B observes only Bo and must refuse to leave. If B completes first, A must refuse. Neither serial execution produces zero. This counterexample is your reason for rejecting the original design; no throughput assumption is involved.

Describe the dependencies in plain language: A relied on the old Bo assignment, which B changed; B relied on the old Ari assignment, which A changed. Each would need to precede the other. Fekete and colleagues’ serializability analysis develops the dependency theory behind these snapshot-isolation anomalies. You can explain this cycle without reciting a theorem.

Then ask your practice partner to change the example to two inserts that exceed a shared allocation limit. Can you still identify the reads that justified the writes? This variation tests whether you understand the shared premise beyond a memorized two-row story.

3. Translate Isolation Names Into Engine-Specific Guarantees

Name the engine, version, and statements. PostgreSQL 18 documents Read Committed statement snapshots and Repeatable Read transaction snapshots; its Repeatable Read prevents phantom reads but still permits serialization anomalies. “No phantoms” therefore does not prove that our assignment rule survives concurrency.

MySQL 8.4 InnoDB defaults to Repeatable Read, but its documentation distinguishes consistent reads from locking reads. Locking depends on the search condition: a unique lookup and a range scan can lock differently. Do not transfer a PostgreSQL proof to InnoDB merely because both expose the same isolation-level label.

SQLite makes another useful comparison. Its isolation documentation describes snapshot reads in WAL mode while serializing writers. A reader trying to become a writer against an obsolete snapshot can receive SQLITE_BUSY_SNAPSHOT. “Uses snapshots” alone does not establish that SQLite admits our two-writer history.

Your answer can stay focused: “For this exercise I am using PostgreSQL 18 and ordinary reads under Repeatable Read. I will show the failure there, then evaluate a repair.” If the interviewer changes engines, revisit the behavior rather than bluffing portability. Reading guarantees, write conflict handling, and predicate protection are separate questions.

4. Choose a Mechanism You Can Prove

Represent a local invariant in the database

For a separate illustrative inventory problem, suppose one row holds the remaining stock for a SKU. A guarded decrement expresses the decision and modification together:

UPDATE inventory
SET remaining = remaining - 1
WHERE sku = :sku AND remaining > 0
RETURNING remaining;

PostgreSQL’s UPDATE reference defines WHERE filtering and RETURNING for updated rows. At Read Committed, a concurrent update waits and then rechecks its condition against the updated row. Under the stated single-row model, a request that finds no positive stock cannot decrement it.

Check the affected-row result and consider a CHECK (remaining >= 0) as a backstop, as supported by PostgreSQL’s constraint syntax. This repair addresses a local counter. It does not automatically protect an invariant spanning independent assignment rows.

Coordinate all mutations of the shared predicate

For the approver workflow, propose one existing coordinator row per release. In a Read Committed transaction: lock that release row first, read the current assignments in a subsequent statement, decide whether leaving is allowed, update the assignment, then commit. Every operation that could affect the invariant must acquire that same lock.

The mechanism rests on FOR UPDATE blocking competing lockers until transaction end. The subsequent Read Committed read supplies the fresh view. A waiting transaction using an already established Repeatable Read snapshot needs separate analysis; PostgreSQL’s application-level consistency guidance specifically warns about snapshots that predate acquired locks.

State the boundary: different releases use different coordinator rows, while operations on one release serialize through its row. This gives you a proposed contention unit to measure. It also creates a protocol obligation for scripts, support tools, and batch jobs; one bypass defeats the argument.

Locking only the assignment being removed leaves our original disjoint writes intact. Locking an empty PostgreSQL query result also gives you no returned assignment rows to protect: row locks protect returned rows. InnoDB’s range and gap locking is a different mechanism, not a portable assumption.

5. Explain What Serializable Changes—and What It Does Not

Serializable offers another repair: successfully committed transactions must have effects equivalent to some serial order. PostgreSQL describes this transaction-level guarantee. Given our invariant-preserving transaction and cooperating operations, both departures cannot successfully commit in the illustrated history.

In PostgreSQL, Serializable Snapshot Isolation monitors dependency patterns and may abort a transaction rather than admit an unsafe history. The SSI implementation paper explains this approach. Be precise about the operational consequence: an aborted departure must reread the assignments and might now become a legitimate business refusal.

Do not describe PostgreSQL’s SSI predicate locks as blocking every matching insert. Its documentation says these locks track dependencies without blocking. Ordinary write locks still exist. You are choosing a conflict-detection mechanism, not declaring that transactions never wait.

Include all relevant writers in the consistency argument. PostgreSQL’s serializable application guidance assumes consistent use for writes and reads needing a consistent view. An administrative change outside the protocol is a design issue to resolve, not a loophole to omit from your diagram.

6. Make the Retry Boundary Explicit and Bounded

A serialization failure is not permission to repeat only the final update. PostgreSQL’s retry guidance requires rerunning the complete transaction, including application logic that chose the SQL and values. Keep the user’s requested departure stable, but recompute whether that departure is allowed from fresh database reads.

For PostgreSQL, identify 40001 serialization failures and consider 40P01 deadlocks for whole-transaction retries. The same documentation distinguishes these errors from constraint violations, which may express persistent problems. “Retry every database error” would conceal invalid input and genuine business conflicts.

1. Begin a fresh attempt. Start a new transaction and execute all reads, decisions, and writes.
2. Finish the attempt. Commit on success; roll back on failure. Return a business refusal directly.
3. Classify the error. Retry only failures covered by an explicit policy; preserve unknown outcomes for reconciliation.
4. Enforce a budget. Apply an attempt cap, an overall deadline, and cancellation. Back off with jitter between eligible attempts.
5. Report exhaustion. Return a recoverable failure and record the operation, contention key, attempts, and elapsed time.

Here is illustrative budget arithmetic, not a performance estimate: with three total attempts, 80 milliseconds allowed per attempt, and waits capped at 20 and 40 milliseconds, the planned components sum to 3 × 80 + 20 + 40 = 300 milliseconds. Connection checkout, scheduling, and cleanup also consume the overall deadline. An attempt cap alone is not a wall-clock guarantee.

PostgreSQL warns that retries may repeatedly fail under contention. Treat the cap and deadline as deliberate service policy. If the final approver cannot leave after a fresh read, another retry will not make that business decision transient.

7. Separate Database Commit From External Effects

Extend the prompt: leaving a release must notify a staffing service. A network call inside the retryable transaction is not protected by the database’s rollback. A call placed after commit introduces another failure point: the process could stop before sending it. AWS’s transactional outbox guidance describes both sides of this dual-write problem.

A concrete proposal is to commit the assignment change and an outbox event together. A separate publisher sends committed events. Delivery can repeat, so the consumer must handle duplicates; the same AWS guidance calls out that requirement. Explain the event identity and retention policy rather than promising exactly-once external execution.

Provider guarantees need their own scope. Stripe, for example, documents cached responses for repeated idempotency keys, including failures, parameter checks, and key removal after at least 24 hours. A database transaction’s serializability does not expand that provider contract. Use stable operation identities and account for the documented retention window.

Also distinguish an explicit transaction abort from losing the connection while receiving the commit result. In the latter case, your exercise design should reconcile a durable operation record before issuing another logically new departure or notification. Tell your practice partner what evidence would establish completion; “the caller timed out” is not enough evidence by itself.

8. Rehearse a Concrete Interview Prompt Sequence

Use one evolving problem for a practice session. Keeping the same invariant lets you compare mechanisms without hiding behind a new architecture each time. The following sequence is a suggested drill, not a claim about any company’s interview format.

1. Specify: “Design leave-release for two assigned approvers. At least one must remain.” Ask which operations exist, where authoritative state lives, and how refusals reach the caller.
2. Break: “Both users click leave concurrently under PostgreSQL Repeatable Read.” Draw snapshot establishment, predicate reads, disjoint updates, and commits. Explain both possible serial orders.
3. Repair: “Offer two designs.” Compare a coordinator lock with fresh Read Committed checks against Serializable with complete retries. Identify all cooperating writers in each design.
4. Stress: “A single release receives many simultaneous departures.” Choose the contention unit, budget, cancellation behavior, and admission policy. Name measurements you would need before changing mechanisms.
5. Extend: “Support staff can bulk-edit assignments, and departures emit notifications.” Revisit the protocol inventory, commit boundary, outbox event, and duplicate handling.
6. Verify: “Prove the original history cannot violate the invariant.” Describe a controlled two-session test, then explain what additional histories remain untested.

Turn the diagram into a controlled test

Use two database sessions and an explicit barrier after both predicate reads. Avoid relying on a convenient sleep to create the schedule. For the original design, release the barrier, complete the disjoint updates, and check the committed assignment count. You should be able to reproduce the counterexample rather than hoping a stress test happens to encounter it.

For the coordinator design, pause A while it holds the release lock and assert that B cannot reach its decision read yet. After A commits, let B proceed and verify its refusal. For the serializable design, arrange the conflicting reads and require an abort rather than two successful departures; then replay the aborted operation with fresh reads. Assert the final invariant and the caller-visible outcome. Include rollback cleanup so a failed attempt cannot leak uncommitted changes into your observation.

Record your explanation and review the parts where your vocabulary outran your proof. Did “atomic” mean one statement, one transaction, or a business operation? Did you say “lock the rows” without identifying which rows? Did you specify the fresh read after a wait? Rewrite those sentences before adding more database terminology.

For another challenge, use a queue claim. MySQL’s locking-read documentation warns that SKIP LOCKED returns an inconsistent view, while recognizing queue-like uses. Explain why skipping busy work items might fit a queue contract but cannot prove that every approver is absent.

9. Answer the Skeptical Follow-Ups With a Smaller Proof

“What if the transactions lock resources in opposite orders?”

Draw the wait cycle. PostgreSQL documents deadlock detection and aborting a participant, and recommends consistent lock ordering. Propose a stable resource order for your protocol and retain deadlock handling. Do not promise that adding explicit locks eliminates all concurrency failures.

“Does serializable mean the latest wall-clock state?”

Specify the read and ordering requirements separately. Google’s Spanner documentation distinguishes serializability from external consistency, which additionally respects observed commit ordering. Avoid using one consistency term as shorthand for transaction isolation, replica freshness, and real-time ordering at once.

“Why not use weaker isolation everywhere?”

Require an application-specific argument. Fekete and colleagues show how application structure can make snapshot-isolation executions serializable, including their TPC-C analysis. That is evidence that a proof can justify weaker isolation, not evidence that your unexamined workload is safe.

Propose measurements tied to your decision: aborted attempts per accepted operation, lock wait time, retry exhaustion, and completed-operation latency. Keep refusals separate from infrastructure failures. For correctness, check the invariant after coordinated competing transactions and include inserts, deletes, and administrative paths. An outcome you never observe in a load test is not automatically an impossible history.

10. Make Your Concurrency Reasoning Reviewable

Prepare one page containing the invariant, a failing history, two candidate repairs, and the retry boundary. Add the assumptions each repair requires and a counterexample that would invalidate it. This gives your practice partner something concrete to challenge and gives you a way to improve an answer beyond memorizing definitions.

The release example ends with a precise claim: either departures coordinate around the same release before making fresh decisions, or an appropriate serializable protocol rejects an unsafe execution and reruns the decision. The proof applies only to the writers and state inside its stated boundary. Carry that habit into the next prompt: make the dangerous history visible, then explain why it can no longer succeed.