Two doctors can go off call only if at least one remains. Both check the rule at the same moment, both see two doctors on call, and both go off call. What isolation problem is this, and what actually fixes it?
This is write skew: two transactions read the same set, each writes a different row, and the pair commits a state neither would have produced alone. No row-level constraint can catch it because the invariant is a property of the set, so the fix is either serializable isolation, a lock on something both transactions must touch, or remodelling the invariant onto a single row.
Answer
Short answer
This is write skew. Two transactions read the same set, each updates a different row, and the pair commits a state that violates an invariant neither would have broken alone. Read committed permits it because there is no write-write conflict to detect — the rows differ. The fix is serializable isolation, a lock on something both transactions must touch, or remodelling the invariant so it lives on one row.
Was this answer helpful?
Likely follow-ups
- Under serializable, what does your application code have to do that it did not have to do before?
- Both transactions lock their own doctor row before updating. Does that fix it, and why not?
- How would you write a test that reliably reproduces this, given that it depends on interleaving?
- Your production database is MySQL rather than Postgres. Does anything in your answer change?
- When would you push the invariant into a single row with a counter, and what do you give up by doing that?
- The rule becomes "at least two doctors on call". Does any of your reasoning change?
Related questions
- You are getting a handful of deadlock errors an hour under load. How do you find the cause, and how do you stop them?hardAlso on locking and transactions7 min
- Walk me through the transaction isolation levels and which anomalies each one permits.hardAlso on transactions and write-skew6 min
- Two transactions each check a rule, then both commit changes that break it, and neither overwrote the other's row. What happened?hardAlso on write-skew and transactions5 min
- A method with REQUIRES_NEW was meant to save an audit row even when the main transaction fails. When does it work, and when does it deadlock?hardAlso on transactions5 min