Prevent Write Skew with Transaction Isolation

Prompt and Applicable Context

The table doctorshifts(teamid, shiftdate, doctorid, on_call) stores on-call status. The business rule says that each team and shift must retain at least 1 on-call doctor. Alice and Bob are both active, and two requests concurrently try to take different doctors off call. Each transaction first counts active doctors. If the count is greater than 1, it updates its own doctor's row.

This question is scoped to PostgreSQL 18. The initial active count is 2. Both transactions finish their reads before either commits, and they then update different rows. The numbers and schema are interview assumptions, not a universal healthcare data model. The core problem is the multi-row predicate count(on_call) >= 1; approval workflows, time zones, and authorization are out of scope.

The same pattern appears in rules such as inventory never becoming negative, an account retaining at least one approver, or a cluster retaining at least one primary. Saying “put it in a transaction” is incomplete. Atomicity makes one transaction succeed or fail as a unit. The isolation level determines what concurrent transactions can observe and whether the database rejects a result that has no valid serial explanation.

What the Interviewer Evaluates

The first signal is recognizing write skew. Both transactions read the same predicate but write different rows, so they do not create a normal same-row write conflict. Each transaction passes its check in isolation, yet their combined result leaves zero active doctors. Calling this a dirty read or a simple lost update leads to the wrong fix.

The second signal is separating standard names from database implementations. PostgreSQL Read Committed takes a new snapshot for each statement. Repeatable Read uses a stable transaction snapshot and is implemented as snapshot isolation, which still permits serialization anomalies. Serializable tracks read/write dependencies on top of similar snapshot-style reads and aborts a transaction when the outcome cannot match any serial order. An isolation level with the same name can use different locking and snapshot semantics in another database.

The third signal is choosing a concurrency boundary from the invariant. When the invariant spans rows, locking “the doctor I am about to update” does not make the two transactions meet. A safe design must make them contend on one lockable object, reduce the rule to an atomic condition on one row, or let Serializable detect the conflict.

Finally, the interviewer looks for failure handling. Both Serializable and explicit locking can abort transactions. The common PostgreSQL serialization code is 40001; inconsistent lock ordering can also produce 40P01. A strong answer retries the complete transaction, bounds the attempts, and moves email or other external effects to an idempotent path after a successful commit.

Questions to Clarify Before Answering

• Which database and version are in use? This answer targets PostgreSQL 18. MySQL, SQL Server, and distributed databases require a fresh check of their same-named isolation levels and retry errors.
• Does the rule cover exactly one shift? An invariant keyed by (teamid, shiftdate) can have one stable contention point per shift. A rule spanning regions or databases cannot be protected by a row lock in only one database.
• Which paths can change on-call status? Leave requests, swaps, imports, and administrative repair must all follow the same protocol. Fixing one API leaves bypass writes that can corrupt a counter or skip a lock.
• Are conflicts occasional or continuously hot? Serializable with retries is often clearest at low contention. A single-row conditional update can avoid wasted work on a hot shift, but it also turns that shift into a serialized hotspot.
• How long may a request wait? Pessimistic locks wait for the holder. A strict latency budget calls for short transactions, a lock-wait limit, and an explicit “state changed, retry” result.
• Does the transaction perform external side effects? A retry reruns transaction logic. Email, roster-service calls, or non-transactional messages must happen after commit or use a transactional outbox with idempotent consumption.

30-Second Answer Framework

“This is write skew: both transactions decide from the same on-call set but update different doctor rows, so even a stable Repeatable Read snapshot can let both commit. PostgreSQL Serializable tracks that read/write dependency and aborts at least one transaction; the application must rerun the complete decision on 40001. Without Serializable, I would map each shift to one roster row, atomically decrement under Read Committed only when active_count > 1, and update the doctor in the same transaction. Another option is locking the roster row before recounting. I would test with two connections and a barrier after both reads, proving that weak isolation reaches zero while the safe design retains exactly one doctor.”

Step-by-Step Deep Answer

Start with the concurrent history. Initially, Alice=true, Bob=true:

T1: read count(on_call) = 2
T2: read count(on_call) = 2
T1: update Alice to false
T2: update Bob to false
T1: commit
T2: commit

If T1 ran first in a serial execution, T2 would read 1 and reject the change. If T2 ran first, T1 would reject it. The actual result is zero, which is not equivalent to either serial order and is therefore a serialization anomaly. The key difference from a lost update is that T1 and T2 never overwrite the same row, so ordinary row-level write locks do not naturally conflict.

Under Read Committed, each count statement observes data committed when that statement begins. With the barrier in the prompt, both statements read 2 before either update. The UPDATE statements target different rows and can both commit. A later statement would receive a newer snapshot, but PostgreSQL does not retroactively invalidate the application decision already made.

PostgreSQL Repeatable Read keeps one snapshot for the transaction. It prevents nonrepeatable reads and phantom reads within that implementation, but permits serialization anomalies. The two transactions update different version chains, so no same-row concurrent update triggers a rollback and both may commit. MVCC explains how readers avoid blocking writers. It is not synonymous with Serializable, and it does not infer the business rule “at least one doctor remains on call.”

The first safe option is Serializable:

BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;

SELECT count(*) AS active_count
FROM doctor_shifts
WHERE team_id = 42
  AND shift_date = DATE '2026-07-20'
  AND on_call;

-- Reject when active_count <= 1.
UPDATE doctor_shifts
SET on_call = false
WHERE team_id = 42
  AND shift_date = DATE '2026-07-20'
  AND doctor_id = 101
  AND on_call;

COMMIT;

When the requests overlap, PostgreSQL detects that their predicate reads and concurrent writes form dependencies that cannot be serialized. It cannot allow both transactions to commit. The failed transaction returns SQLSTATE 40001. A retry must start before BEGIN and rerun the count and the decision to update. Retrying only the final UPDATE preserves a stale decision. Use jittered backoff, a maximum attempt count, and an overall deadline because a retry can conflict again under high contention.

Serializable lets the application express the rule around its real predicate instead of manually inventing a lock for every invariant. Its cost is dependency-tracking overhead and conflict retries. A larger read set, a longer transaction, and concentrated contention generally create more opportunity for overlap. An index on (teamid, shiftdate), short transactions, and no network wait before commit reduce that window.

The second option projects the multi-row invariant onto one row, oncallrosters(teamid, shiftdate, active_count), and uses an atomic update under Read Committed:

BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;

UPDATE on_call_rosters
SET active_count = active_count - 1
WHERE team_id = 42
  AND shift_date = DATE '2026-07-20'
  AND active_count > 1
RETURNING active_count;

-- Continue only when exactly one roster row was returned.
UPDATE doctor_shifts
SET on_call = false
WHERE team_id = 42
  AND shift_date = DATE '2026-07-20'
  AND doctor_id = 101
  AND on_call;

-- Commit only when both updates changed exactly one row; otherwise roll back.
COMMIT;

Both requests now contend on the same roster row. After waiting for a concurrent update under PostgreSQL Read Committed, an updater rechecks activecount > 1 against the new row version. The first request changes 2 to 1; the second condition fails and returns no row. A row-level CHECK (activecount >= 1) can provide a final guard. The cost is that every activation, deactivation, import, and repair path must maintain the counter in the same transaction. The system should also reconcile the counter against detail rows and alert on drift instead of silently overwriting it.

If maintaining a counter is undesirable, keep a stable roster row for every shift. As the first step in a Read Committed transaction, run SELECT ... FOR UPDATE on that row, then count the detail rows and update the doctor. After the second request waits, its next SELECT gets a snapshot that includes the first commit. Every write path must lock the same roster row first, and the transaction must remain short. A subtle unsafe variant is establishing an old Repeatable Read snapshot and then locking a guard row that nobody modifies: acquiring FOR UPDATE does not refresh that transaction snapshot.

Locking all currently active doctor rows directly also needs care. The lock set comes from a predicate, and new rows, different query orders, or bypass writes can change the safety argument. Inconsistent order across multiple row locks can deadlock. If this design is required, acquire locks in stable primary-key order and rerun the complete transaction on 40P01. Locking only “the doctor I am taking off call” is definitely insufficient because the requests still lock different rows.

Do not validate this by calling two APIs sequentially. Use two independent database connections and a test barrier. Both sessions begin Repeatable Read, count the rows, assert that each observed 2, and only then proceed to their updates and commits. The baseline should reliably produce two commits and a final count of zero. Under Serializable, assert that two successes are impossible: one transaction receives 40001, and its complete retry observes 1 and rejects the change. Under the roster design, assert that exactly one conditional update returns a row and that both the details and active_count end at 1.

Repeat the concurrent case with shuffled commit order. Add duplicate requests for the same doctor, a third doctor joining, rollback between the two updates, lock-wait timeout, and deadlock cases. In production, monitor 40001, 40P01, retry attempts, lock waits, transaction duration, terminal failure rate, and differences between roster counters and detail rows. A sudden conflict-rate increase usually signals a hotspot, long transaction, or new write path entering the boundary; unlimited retries only hide and amplify it.

High-Quality Sample Answer

“I would first identify this as write skew. Both transactions check the same cross-row rule, but Alice and Bob update their own rows, so row-level write locks do not conflict. Given the schedule where both read 2 first, Read Committed can let both commit. PostgreSQL Repeatable Read gives each transaction a stable snapshot, yet can still produce the final value zero, which no serial ordering can explain.

My first choice is to express the rule in a Serializable transaction. I count active doctors for the shift and update only when the count is greater than 1. PostgreSQL tracks the dependency between the predicate read and concurrent writes. In this case, both transactions cannot commit; one receives 40001. The application catches that error and reruns the count and update from the beginning with a bounded retry policy. I keep notifications outside the retryable transaction and process them from an idempotent outbox after commit.

For a shift with sustained contention, I would consider an oncallrosters row. Under Read Committed, the leave transaction uses UPDATE ... SET activecount = activecount - 1 WHERE active_count > 1 RETURNING ... to contend on that one row, then updates the doctor detail. If either statement fails to change exactly one row, the whole transaction rolls back. The second request rechecks its condition on the latest row version and fails. The tradeoff is that every write path must maintain the counter.

I would verify the design with two connections and a barrier that fixes the interleaving. First I would prove that Repeatable Read can let both sessions read 2 and reach zero. Then I would prove that Serializable aborts exactly one transaction and that its retry rejects the change. The counter design should permit only one conditional update. Finally, I would monitor serialization failures, deadlocks, lock waits, and counter drift so the safety claim does not depend on a lucky test schedule.”

Common Mistakes

• “It is inside a transaction, so it is safe” → Atomicity says nothing about visibility and commit ordering across concurrent transactions → Name the isolation level and write the interleaving that breaks the invariant.
• Treating write skew as a lost update → The transactions write different rows, so a version check on one row does not cover their shared predicate → Identify the cross-row invariant and use a common contention point or Serializable.
• Claiming Repeatable Read equals Serializable → A stable snapshot can still produce a combined result with no serial explanation → Describe the serialization anomaly allowed by PostgreSQL Repeatable Read.
• Locking each transaction's doctor row → Alice's and Bob's locks do not conflict → Lock one roster row, update one counter row, or let SSI detect the predicate dependency.
• Adding FOR UPDATE after an old Repeatable Read snapshot → Locking an unchanged guard row does not refresh the transaction snapshot → Lock and reread under Read Committed, or use Serializable or a mutable counter row.
• Retrying only COMMIT or the last SQL statement after 40001 → The business decision still came from the stale snapshot → Rerun the complete transaction and all application logic that chooses the SQL.
• Retrying immediately without a bound → Hot requests collide in sync and amplify database load → Use jittered backoff, a maximum attempt count, and an overall deadline.
• Sending a notification before commit → A serialization retry can send the notification more than once → Defer external effects and use a transactional outbox with idempotent consumers.
• Maintaining a counter while allowing bypass detail writes → active_count drifts from the true on-call count and the condition loses meaning → Make every write path use the same transaction protocol and continuously reconcile it.

Follow-Up Questions and Responses

Follow-up 1: Why can one atomic UPDATE prevent negative inventory but not automatically solve this problem?

A single inventory row can use UPDATE inventory SET stock = stock - 1 WHERE stock > 0. The condition and write target are the same row, so concurrent updaters recheck the condition on the latest version. Here, the condition is a count across rows while the write touches one doctor. To obtain the same property, materialize the count on one roster row or use Serializable to detect predicate dependencies.

Follow-up 2: What if the Serializable failure rate is high?

First shorten the transaction, index the predicate, remove network calls from the transaction, and measure conflicts per shift. If a few hot shifts account for most 40001 errors, move only those writes to the roster conditional update so contention queues on one row. If every shift is highly contended, revisit batch operations and transaction boundaries. A larger retry limit is not a substitute for capacity analysis.

Follow-up 3: Can a CHECK constraint alone guarantee that one doctor remains on call?

A check placed on one doctorshifts row can validate fields on that row, but that row cannot independently prove that another doctor remains active for the same shift. After projecting the invariant to a roster activecount, CHECK (active_count >= 1) becomes a database-level guard. The detail and counter still need to change in the same transaction and be reconciled.

Follow-up 4: What happens if the service crashes after decrementing the roster count?

If the counter and doctor status are in one database transaction, a disconnected session rolls back an uncommitted transaction, so only half the change cannot persist. If the commit succeeded but the client missed the response, the outcome is unknown. A retry should carry a business operation ID and inspect the doctor's current state before counting the same leave action again.

Follow-up 5: What if the rule covers doctors stored in two databases?

Single-database Serializable and row locks protect only data visible to that database. Choose one authoritative write boundary, such as a strongly consistent roster service whose state is projected asynchronously to other databases, or introduce a distributed transaction and accept its coordination, availability, and latency costs. If both databases decide locally and merge asynchronously, the design must explicitly permit temporary violations and define compensation; the single-database safety proof no longer applies.