Interactive Socratic Dialogue & Conceptual Quiz Generator

### Socratic Diagnostic Scenario 1: The Midnight Doctor On-Call Dilemma **Scenario:** A hospital database has an active rule: *'At least one doctor must be active on-call at all times.'* Doctors Alice and Bob are currently on call. Both simultaneously feel sick and open separate web sessions at 11:59 PM to take themselves off-call. - Transaction 1 (Alice): Queries `SELECT count(*) FROM on_call WHERE active = true`. Result is 2. Updates `Alice -> inactive`. Commits. - Transaction 2 (Bob): Queries `SELECT count(*) FROM on_call WHERE active = true`. Result is 2. Updates `Bob -> inactive`. Commits. Both transactions run under **Repeatable Read** isolation level. **Question:** What is the state of the database after both commit? - A) One transaction fails with a serialization error; one doctor remains active. - B) Both transactions commit successfully; zero doctors are now active on call. - C) The database deadlocks and rolls back both transactions. - D) Repeatable Read prevents this because of row-level lock escalation. **Answer & Socratic Teardown:** **Correct Answer: B.** Both commit successfully, leaving ZERO doctors on call. *Why this happens:* This is the classic **Write Skew anomaly**. Repeatable Read guarantees that a transaction sees a consistent snapshot of rows it reads, but because Alice modified row 'Alice' and Bob modified row 'Bob', their write sets did NOT overlap! Neither transaction experienced a write conflict, so neither aborted. Only true **Serializable** isolation level (or explicit `SELECT ... FOR UPDATE` row locks) detects write skew and prevents this catastrophic invariant violation.