TheHiddenBugThatLetsTwoDoctorsBothTaketheLastSickDay

The Setup: A Hospital Scheduling System That Seems Fine

Picture a hospital. Two doctors are on call together, and hospital policy says at least one doctor must always be on call. Dr. Alice and Dr. Chen both wake up sick on the same morning. Both of them open the scheduling app at almost exactly the same time. Both check: is there another doctor covering? Both see yes. Both approve their own sick day requests. Both go back to bed.

The hospital now has zero doctors on call.

No error was thrown. No transaction failed. The database dutifully wrote both records, confirmed success to both applications, and went about its business. From the database's perspective, everything went perfectly. From the hospital's perspective, a catastrophe just unfolded silently.

This is write skew. And I've found it to be one of the most misunderstood, hardest-to-spot bugs in software systems today.

What Write Skew Actually Is

Most developers know about race conditions in a general sense. Two things happen at the same time, they conflict, bad things happen. The usual fix people reach for is a transaction. Wrap your reads and writes in a transaction, and the database handles concurrency for you. Right?

Not exactly.

Write skew is what happens when two transactions both read an overlapping set of data, make decisions based on what they read, and write to different rows — but those writes together violate a constraint that was true when they started. Neither transaction wrote to the same row as the other. There's no classic "lost update" here. The database didn't overwrite anything. But the combination of the two writes breaks a rule that the application cares about.

Think of it like two people both walking toward the last seat on a train. They look down the aisle, see the seat empty, and both start walking. They're approaching from different doors, so they don't see each other. They both sit down at the same time — one in the seat, one on the other person's lap. Nobody blocked the other. They just both made a valid-looking decision at the same moment based on the same stale view of reality.

The key insight is this: write skew isn't about two transactions writing the same thing. It's about two transactions reading the same premise (at least one doctor is on call) and writing different things (Alice is off, Chen is off) that together shatter that premise.

Why Transactions Don't Save You By Default

Here's where a lot of developers get tripped up. They learn that ACID databases give you isolation, they assume isolation means safety, and they ship their code. But isolation has levels, and the default level at most databases is not as strong as you might think.

PostgreSQL's default isolation level is READ COMMITTED. MySQL (InnoDB) defaults to REPEATABLE READ. Neither of these prevents write skew by default. You need SERIALIZABLE isolation to get that protection — and even then, the way serializable isolation is implemented matters enormously.

REPEATABLE READ sounds reassuring. If you read a row, you'll get the same value if you read it again within your transaction. But it doesn't mean your view of the entire dataset is consistent with other concurrent transactions. Two transactions can both see "two doctors on call" at the start, and both leave the system with no doctors on call, without either transaction technically violating repeatability.

Let me show you what the code looks like. Here's a simplified version of the sick day approval logic, written naively in Python with psycopg2:

import psycopg2

def approve_sick_day(connection, doctor_id):
    cursor = connection.cursor()

    # Check if at least one other doctor is on call
    cursor.execute("""
        SELECT COUNT(*) FROM on_call_schedule
        WHERE date = CURRENT_DATE
          AND status = 'active'
          AND doctor_id != %s
    """, (doctor_id,))

    other_doctors_on_call = cursor.fetchone()[0]

    if other_doctors_on_call == 0:
        print("Cannot approve: no other doctors available.")
        connection.rollback()
        return False

    # Looks safe — approve the sick day
    cursor.execute("""
        UPDATE on_call_schedule
        SET status = 'sick_day'
        WHERE doctor_id = %s AND date = CURRENT_DATE
    """, (doctor_id,))

    connection.commit()
    print(f"Sick day approved for doctor {doctor_id}")
    return True

This code looks completely reasonable. It checks the constraint. It only proceeds if the constraint is satisfied. But if two connections call this function at almost the same moment, both will pass the check (both see one or more other doctors active), both will update their own row, and both will commit. The constraint is violated and nobody knows.

The Phantom of Phantom Reads

There's a related concept worth understanding here: phantom reads. A phantom read is when a transaction runs the same query twice and gets different rows back the second time — not different values in the same rows, but actually new or deleted rows appearing or disappearing.

Write skew can involve phantom reads, but it doesn't have to. In the doctor example, we're not necessarily reading new rows appearing. We're just reading an aggregate (a count) and acting on it. The "phantom" in our case is more subtle: the world changes between our read and our write, but we never re-read to notice.

SERIALIZABLE isolation is supposed to protect against this. In theory, serializable means that even though transactions run concurrently, the end result is as if they ran one at a time, in some sequential order. If they ran sequentially, the second doctor's request would correctly see that the first doctor already took the sick day, and the system would refuse.

But here's the painful reality I've encountered in production systems: many databases implement SERIALIZABLE as "snapshot isolation with some extra checks," and those checks are not always enough. True serializable execution is expensive. Database vendors make trade-offs.

How Serializable Isolation Tries to Fix This

Modern PostgreSQL uses an algorithm called Serializable Snapshot Isolation (SSI). The way it works is genuinely clever. Instead of locking rows (which kills performance), it tracks which transactions read what, and when it detects that two transactions have read-write dependencies that form a dangerous cycle, it aborts one of them.

The database is essentially asking: "Could these two transactions have run in some serial order and produced the same result?" If the answer is no, it kills one of them with a serialization error, and your application is expected to retry.

This is what properly protected code looks like:

import psycopg2
from psycopg2 import errors
import time

def approve_sick_day_safe(doctor_id, max_retries=3):
    for attempt in range(max_retries):
        connection = psycopg2.connect("dbname=hospital")
        try:
            # Critical: set isolation level BEFORE any statements
            connection.set_isolation_level(
                psycopg2.extensions.ISOLATION_LEVEL_SERIALIZABLE
            )
            cursor = connection.cursor()

            cursor.execute("""
                SELECT COUNT(*) FROM on_call_schedule
                WHERE date = CURRENT_DATE
                  AND status = 'active'
                  AND doctor_id != %s
            """, (doctor_id,))

            other_doctors_on_call = cursor.fetchone()[0]

            if other_doctors_on_call == 0:
                connection.rollback()
                return False, "No other doctors available"

            cursor.execute("""
                UPDATE on_call_schedule
                SET status = 'sick_day'
                WHERE doctor_id = %s AND date = CURRENT_DATE
            """, (doctor_id,))

            connection.commit()
            return True, "Approved"

        except errors.SerializationFailure:
            connection.rollback()
            # Retry with exponential backoff
            wait = (2 ** attempt) * 0.1
            time.sleep(wait)
            continue

        finally:
            connection.close()

    return False, "Could not complete request after retries"

Notice two critical things here. First, the isolation level is set before any statements execute in the transaction. Second, there's retry logic for SerializationFailure errors. If the database detects a dangerous conflict, it raises this error and expects you to retry. If your application doesn't retry, you've just silently failed the request. The database did its job; your code has to do its job too.

Explicit Locking as an Alternative

Sometimes you don't want to deal with retries. Sometimes the operation is too important to risk even a retry loop. In those cases, you can reach for explicit locking with SELECT FOR UPDATE. This is a more pessimistic approach — it acquires a lock at read time, preventing other transactions from modifying the rows you've read until you commit.

BEGIN;

-- Lock the rows we're making decisions about
SELECT doctor_id, status
FROM on_call_schedule
WHERE date = CURRENT_DATE
  AND status = 'active'
FOR UPDATE;

-- Now check the count in application code
-- ...if safe, proceed with update
-- The lock ensures no other transaction can change these rows under us

UPDATE on_call_schedule
SET status = 'sick_day'
WHERE doctor_id = 1 AND date = CURRENT_DATE;

COMMIT;

The downside is obvious: you're serializing access to those rows, which reduces throughput. But for operations where correctness is absolutely non-negotiable — booking the last seat on a flight, allocating the last unit of inventory, approving the last sick day — a small performance penalty is worth it.

In my experience, SELECT FOR UPDATE is the right choice when you can clearly identify the set of rows you're making decisions about. SSI is better when you want the database to handle concurrency automatically and your workload has low contention (meaning conflicts are rare).

The Materialized Conflict Trick

There's one more technique worth knowing about: materializing conflicts. This is a workaround for situations where the constraint you're checking doesn't correspond to actual rows in the database — specifically when you're checking for the absence of something, which is hard to lock.

Consider a meeting room booking system. You want to ensure no two meetings overlap in the same room. There's no row to lock for "the gap between 2pm and 3pm." That gap doesn't exist as a record.

The materialization trick is to create explicit lock rows. You'd insert a row into a time_slots table representing every possible 15-minute slot, even before any meetings are booked. Now when you want to book a room from 2pm to 4pm, you SELECT FOR UPDATE the relevant time slot rows. Suddenly there's something concrete to lock, even when the "availability" concept was previously just an absence of data.

It's a bit of an ugly hack, honestly. But it works, and sometimes pragmatic ugly solutions beat elegant ones that don't exist yet.

Why This Bug Hides So Well

Write skew hides because it requires concurrency to manifest. Your unit tests run sequentially. Your integration tests probably run sequentially. Your QA environment probably has one person clicking at a time. You can test this system a thousand times and never see the bug, because the bug lives in the gap between two simultaneous operations.

The only reliable way to catch write skew in testing is to deliberately introduce concurrent requests in your tests, and to run them with enough parallelism that the timing window opens up. That's not a test most teams write by default.

It also hides because the database reports success. There's no error, no exception, no log message. Everything committed cleanly. The corruption is only visible if you query the data and notice that it violates your business rule — and only if you know to look.

I've seen teams discover write skew bugs through customer complaints months after deployment. A double-booking here, an oversold ticket there. The code looked fine. The logs showed no errors. The bug was invisible until someone was sitting on someone else's lap.

Choosing the Right Defense

The right solution depends on your situation. Here's how I think about it.

If you're using PostgreSQL and your workload has low contention, enabling SERIALIZABLE isolation globally and building retry logic into your transaction wrapper is the cleanest approach. It's optimistic — it assumes conflicts are rare, proceeds without locking, and only cleans up when a conflict is detected. This scales well.

If you have high contention on specific operations, or if the correctness requirement is absolute and retries aren't acceptable, use SELECT FOR UPDATE on the rows that form the basis of your decision. It's more targeted, more predictable, but it does reduce concurrency for those specific operations.

And regardless of which approach you choose, test for concurrency explicitly. Write a test that fires two requests simultaneously and asserts that exactly one succeeds. Do it with a real database, not mocks. Mock databases don't have concurrency bugs, because they don't have concurrency.

The uncomfortable truth is that most web applications today have write skew vulnerabilities somewhere. The constraint-checking-then-writing pattern is extremely common, and most developers don't know they need to think about it. The database won't warn you. The application won't crash. Your users will just slowly encounter data that doesn't make sense.

Understanding write skew isn't just an academic exercise. It's the difference between a system that works correctly and one that fails silently at the worst possible moments — like leaving a hospital with no doctors on call on a busy Monday morning.