ORIGINAL TECHNICAL PRACTICE
Distinguish snapshot reads from serializable behavior
Two transactions each read that two staff members are on call and each removes a different member. Can individually atomic transactions still violate the rule that someone remains on call?
Written for independent practice. The model answer presents one approach; clarify constraints and explain alternatives.
A REASONED APPROACH
Model answer.
Yes. Under some isolation levels, each transaction can make its decision from a snapshot that excludes the other’s change. This write-skew pattern can violate a cross-row invariant without either transaction partially committing.
Work through it
- Specify the database and isolation level rather than assuming all snapshot-based implementations behave identically. Identify the rows or predicate representing the invariant.
- Consider a serializable transaction with required retry handling, or a protocol that locks a shared coordinating record before checking and changing membership.
- Test the interleaving explicitly and inspect committed outcomes. Keep the transaction short and make retries safe at the application boundary.
Tradeoffs to discuss
- A shared lock can simplify the invariant but create contention. Serializable execution may reject a transaction, requiring the application to restart it rather than merely retry one statement.
Common pitfalls
- Row-level atomicity alone does not protect a rule spanning different rows. A successful test with sequential requests does not exercise the conflicting interleaving.