Detect and handle write skew (concept)

Detect and handle write skew (concept)

Hard SQL Transactions 12 views
Explanation Complexity

Problem Statement

Two doctors on call table. Each transaction sees at least one on call and turns itself off, resulting none on call. Use SERIALIZABLE to prevent.

Input Format

SQL script

Output Format

Statements

Example

OnCall(doctor_id, on_call)
Serializable prevents skew

Constraints

Concept example

Input / Output Format

Input Format
SQL script
Output Format
Statements
Constraints
Concept example

Examples

Input:
OnCall(doctor_id, on_call)
Output:
Serializable prevents skew

Example Solution (Public)

SQL 
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; BEGIN; UPDATE OnCall SET on_call = 0 WHERE doctor_id = 1 AND (SELECT COUNT(*) FROM OnCall WHERE on_call = 1) > 1; COMMIT;

Official Solution Code

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; BEGIN; UPDATE OnCall SET on_call = 0 WHERE doctor_id = 1 AND (SELECT COUNT(*) FROM OnCall WHERE on_call = 1) > 1; COMMIT;
Please login to submit solutions.
Editor
Output

                                        

                                    

                                    

                                        

                                                                                    
Please login to submit solutions.