I would like to point out that Snapshot Isolation (SI) prevents Phantom reads but write skew may still occur unless the isolation level is fully serializable.
I would like to know some examples of phantom reads in SI if they occur.
Example of Write Skew under Snapshot Isolation
------------------------------------------------
Scenario: Doctor On-Call Scheduling System
Suppose a hospital enforces a rule that at least one doctor must be on call at any time. The system stores on-call doctors in a database:
CREATE TABLE on_call_doctors (
doctor_id INT PRIMARY KEY,
is_on_call BOOLEAN
);
Currently, there are two doctors, both on call:
doctor_id => is_on_call
1 => true
2 => true
Step-by-Step Breakdown of Write Skew:
-----------------------------------------
Two transactions (T1 and T2) attempt to remove themselves from on-call duty at the same time:
T1 begins (snapshot taken).
T2 begins (snapshot taken at the same time as T1).
T1 reads the table → sees both doctors 1 and 2 are on call.
T2 reads the table → sees both doctors 1 and 2 are on call.
T1 updates the database to set is_on_call = false for doctor_id = 1.
T2 updates the database to set is_on_call = false for doctor_id = 2.
T1 commits.
T2 commits.
Problem: Now, no doctors are on call, violating the business rule.
Thanks, Ashish for this article.
I would like to point out that Snapshot Isolation (SI) prevents Phantom reads but write skew may still occur unless the isolation level is fully serializable.
I would like to know some examples of phantom reads in SI if they occur.
Example of Write Skew under Snapshot Isolation
------------------------------------------------
Scenario: Doctor On-Call Scheduling System
Suppose a hospital enforces a rule that at least one doctor must be on call at any time. The system stores on-call doctors in a database:
CREATE TABLE on_call_doctors (
doctor_id INT PRIMARY KEY,
is_on_call BOOLEAN
);
Currently, there are two doctors, both on call:
doctor_id => is_on_call
1 => true
2 => true
Step-by-Step Breakdown of Write Skew:
-----------------------------------------
Two transactions (T1 and T2) attempt to remove themselves from on-call duty at the same time:
T1 begins (snapshot taken).
T2 begins (snapshot taken at the same time as T1).
T1 reads the table → sees both doctors 1 and 2 are on call.
T2 reads the table → sees both doctors 1 and 2 are on call.
T1 updates the database to set is_on_call = false for doctor_id = 1.
T2 updates the database to set is_on_call = false for doctor_id = 2.
T1 commits.
T2 commits.
Problem: Now, no doctors are on call, violating the business rule.
Thanks for sharing your detailed insight. Very informative.
Awesome post! Really helpful for interview prep
Thank you very much, 100% recommend, just sent this to all my backend engineers.