In this lesson, you will learn how Write Skew occurs when two concurrent transactions modify different rows while inadvertently violating a shared business constraint.
The Concept
When we talk about isolation levels (like preventing dirty or phantom reads), we usually focus on how one transaction sees another's data. Write Skew is a subtler anomaly: it happens when two transactions read the same data to make a decision, but then proceed to change different rows in a way that, combined, violates a rule.
Because each transaction only updates its own specific row, the database doesn't see a "conflict." It assumes the transactions are independent, even though the logic behind them is mutually exclusive.
A Concrete Illustration: The Doctor On-Call
Imagine a hospital rule: "At least one doctor must be on call at all times." We have two doctors, Alice and Bob, both currently on call. They both decide to take the night off.
- Transaction A (Alice): Checks the database:
SELECT count(*) FROM doctors WHERE on_call = true;(Result: 2). Since 2 > 1, Alice feels safe to seton_call = falsefor herself. - Transaction B (Bob): Simultaneously checks the database:
SELECT count(*) FROM doctors WHERE on_call = true;(Result: 2). Since 2 > 1, Bob also feels safe to seton_call = falsefor himself.
-- Both transactions run these steps independently:
-- 1. Read: How many doctors are on call?
-- 2. Logic: If count > 1, proceed.
-- 3. Write: UPDATE doctors SET on_call = false WHERE name = '...';
Both transactions commit successfully. The database never sees a row conflict because Alice updated her record and Bob updated his. However, the business constraint is now violated: zero doctors are on call. The database perceived these updates as safe because it didn't realize they were both relying on the same aggregate state to validate their actions.
This is a "Write Skew" because the data state was "skewed" by two transactions that both thought they were acting within the rules. They didn't overwrite each other, but they invalidated the global constraint.
Key Takeaway: Write Skew occurs when multiple transactions perform independent updates based on a shared state, causing them to unknowingly violate a business constraint that the database treats as non-conflicting.