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
Nicely written and is very easy to understand. thanks for taking time to write this article in a beginner friendly manner.
Thank you very much, 100% recommend, just sent this to all my backend engineers.