What is the Write Skew Anomaly?
Understand the write skew anomaly in databases, how it differs from lost updates, and how Serializable isolation prevents it.
Expected Interview Answer
Write skew is a concurrency anomaly where two transactions each read overlapping data, independently decide their own write is safe based on what they read, and both commit successfully, but the combination of both writes together violates a constraint that neither transaction saw on its own.
Unlike a lost update, where two transactions write to the same row and one overwrites the other, write skew involves each transaction writing to a different row while depending on a shared invariant computed from both rows — for example two on-call doctors each independently checking that at least one doctor besides themself is on call before going off-call. Under Snapshot Isolation, each transaction sees a snapshot where the invariant still holds, so both commit, but the moment both writes land, the invariant is broken because neither transaction saw the other’s in-flight change. The fix is either Serializable isolation (which detects the conflicting read-write dependency) or explicit locking (e.g. SELECT FOR UPDATE) that forces the transactions to serialize.
- Explains a subtle bug that pure locking on one row cannot catch
- Distinguishes write skew from the simpler lost-update anomaly
- Motivates when Serializable isolation is genuinely needed
- Highlights the risk of cross-row invariants under Snapshot Isolation
AI Mentor Explanation
Imagine a rule that at least one of two designated wicketkeepers must be listed as available for the next match. Keeper A checks the roster, sees Keeper B is marked available, and marks himself unavailable. At nearly the same moment, Keeper B checks the roster, sees Keeper A still marked available (his change has not landed yet), and marks himself unavailable too. Both checks were individually valid at the time, but the combined result leaves zero keepers available — a write skew, because each write depended on a shared condition that the other transaction was simultaneously invalidating.
Step-by-Step Explanation
Step 1
Two transactions read overlapping data
Each transaction reads a shared invariant computed from two or more rows (e.g. "at least one doctor on call").
Step 2
Each transaction validates against its own snapshot
Under Snapshot Isolation, each transaction sees the invariant as still holding, unaware of the other’s in-flight write.
Step 3
Each transaction writes to a different row
Transaction A updates row X, Transaction B updates row Y; since they touch different rows, no write-write conflict is detected.
Step 4
Both commit and the invariant breaks
The combined effect of both commits violates the constraint that neither transaction individually saw being broken.
What Interviewer Expects
- Clear distinction between write skew and a simple lost update
- Recognition that write skew involves different rows but a shared invariant
- Knowledge that Snapshot Isolation permits write skew while Serializable prevents it
- A concrete two-transaction example (e.g. on-call doctors)
Common Mistakes
- Confusing write skew with a lost update on the same row
- Assuming row-level locking on one table alone prevents write skew
- Not knowing that Serializable isolation (or SELECT FOR UPDATE) is the fix
- Failing to explain why each individual transaction looked valid in isolation
Best Answer (HR Friendly)
“Write skew happens when two transactions each check a shared rule, both see it as satisfied, and each makes a different change that individually looks fine — but together, their changes break the rule neither one saw being broken. The classic example is two on-call doctors each independently going off-call because they each see the other still on call. Serializable isolation or explicit locking is needed to prevent it.”
Code Example
-- Both transactions run concurrently under SNAPSHOT ISOLATION
-- Transaction A (Doctor 1)
BEGIN;
SELECT COUNT(*) FROM Doctors WHERE on_call = true; -- sees 2
UPDATE Doctors SET on_call = false WHERE doctor_id = 1;
COMMIT;
-- Transaction B (Doctor 2), running concurrently
BEGIN;
SELECT COUNT(*) FROM Doctors WHERE on_call = true; -- also sees 2
UPDATE Doctors SET on_call = false WHERE doctor_id = 2;
COMMIT;
-- Result: on_call count is now 0, violating "at least 1 on call"
-- Fix: use SERIALIZABLE isolation, or
-- SELECT ... FOR UPDATE to force the transactions to serialize.Follow-up Questions
- How does Serializable isolation detect and prevent write skew?
- How is write skew different from a lost update anomaly?
- How would SELECT FOR UPDATE prevent this specific on-call example?
- What is the performance trade-off of using Serializable isolation to avoid write skew?
MCQ Practice
1. Write skew is best described as:
Write skew occurs when independent writes to different rows each look valid alone but together break a constraint spanning both.
2. Which isolation level is guaranteed to prevent write skew?
Serializable isolation detects the conflicting read-write dependency between the transactions and forces them to serialize, unlike Snapshot Isolation.
3. The classic write-skew example involves:
This is the textbook write-skew scenario: each doctor sees the other still on call and independently removes themself, breaking the shared invariant.
Flash Cards
What is write skew? — Two transactions write to different rows, each looking valid alone, but together they violate a shared invariant.
Write skew vs lost update? — Lost update overwrites the same row; write skew involves different rows and a cross-row constraint.
Does Snapshot Isolation prevent write skew? — No — it is the classic anomaly Snapshot Isolation fails to catch.
How to prevent write skew? — Use Serializable isolation, or explicit locking such as SELECT FOR UPDATE.