What is "snapshot isolation" and how can it still permit write skew anomalies?
Correct! Well done.
Incorrect.
The correct answer is D) Snapshot isolation gives each transaction a consistent view of the database as of its start time, but two concurrent transactions can read the same data, each act on what they saw, and together violate an invariant neither broke alone
Correct Answer
Snapshot isolation gives each transaction a consistent view of the database as of its start time, but two concurrent transactions can read the same data, each act on what they saw, and together violate an invariant neither broke alone
Under snapshot isolation (used by PostgreSQL's REPEATABLE READ and Oracle's SERIALIZABLE), each transaction sees a consistent snapshot, so it never observes the other's uncommitted writes. However, two transactions can both read the same snapshot, each decide an action is safe based on it, and then commit writes that collectively break an invariant — the classic example being two on-call doctors each independently giving up their shift because the snapshot showed another doctor still on call.