Write skew is one of the most subtle and dangerous concurrency anomalies in distributed databases. Unlike dirty reads or lost updates, write skew doesn’t involve transactions reading uncommitted data or overwriting each other’s changes. Instead, it allows two concurrent transactions to make decisions based on overlapping reads, then write to different locations in ways that collectively violate an application-level constraint.

The classic example: an on-call scheduling system requires at least one doctor on duty at all times. Two doctors, Alice and Bob, are currently on call. Both simultaneously request time off. Each transaction reads the current on-call list, sees two doctors available, confirms the constraint will still be satisfied if one leaves, and commits. Both transactions succeed. Now zero doctors are on call, violating the business rule.

Each transaction in isolation appears correct. The constraint is only violated when you consider both writes together. This is write skew: concurrent transactions read overlapping data, make decisions based on those reads, then write to disjoint sets of rows in ways that create an inconsistent state.

Why Snapshot Isolation Doesn’t Prevent It

Most modern databases default to snapshot isolation or read committed isolation levels. Snapshot isolation gives each transaction a consistent view of the database at a point in time, preventing dirty reads, non-repeatable reads, and phantom reads. This sounds robust, but snapshot isolation explicitly allows write skew.

Under snapshot isolation, transactions only conflict if they write to the same rows. In the on-call example, Alice’s transaction updates her record and Bob’s transaction updates his record. Different rows, no write-write conflict, both commits succeed. The database has no visibility into the application-level constraint that connects these two writes.

This is why write skew is so insidious in production systems. Teams adopt snapshot isolation thinking they have strong consistency guarantees, then discover constraint violations weeks or months later when edge cases trigger simultaneous updates.

Detecting Write Skew in Your System

Write skew appears in patterns where:

• A transaction reads multiple rows to check a constraint
• Based on that constraint, it writes to a subset of those rows
• Another transaction concurrently does the same, writing to a different subset
• Together, the writes violate the constraint

Common scenarios include resource allocation systems, inventory management with safety stock requirements, double-booking prevention in scheduling systems, and financial systems enforcing balance constraints across accounts.

If your application validates multi-row constraints before writes, and those constraints can be checked by concurrent transactions, you’re vulnerable. Look for SELECT queries followed by INSERT or UPDATE statements where the logic involves counting, summing, or checking the existence of related rows.

Mitigation Strategies

The most reliable solution is serializable isolation, the strongest isolation level defined by SQL standards. True serializable isolation prevents write skew by detecting conflicts between read and write sets across transactions, not just write-write conflicts. PostgreSQL’s serializable snapshot isolation and CockroachDB’s default serialization both handle this correctly, aborting one of the conflicting transactions.

The performance cost is real but often overstated. Modern serializable implementations use optimistic concurrency control and only abort transactions when actual conflicts occur, not speculatively.

If serializable isolation isn’t available or practical, explicit locking works. In the on-call example, each transaction could SELECT FOR UPDATE on all doctor records before making decisions, forcing concurrent transactions to wait. This creates a write-write conflict even though the transactions update different rows.

Application-level constraint enforcement is another approach: materialize the constraint into a single row that all transactions must update. Instead of checking if two doctors remain on-call, maintain a dedicated counter row that every schedule change must lock and update. This converts a multi-row constraint into a single-row write conflict that snapshot isolation can detect.

The Distributed Systems Angle

Write skew becomes even more challenging in distributed databases. When data is sharded across nodes, the database may not have a global view needed to detect conflicts. A transaction reading and writing on node A and another on node B might both commit without either node realizing a constraint was violated.

Distributed serializable isolation requires coordination protocols like Spanner’s TrueTime or Calvin’s deterministic ordering. These add latency but provide the consistency guarantees that prevent write skew across data centers.

Understanding write skew matters because it represents a fundamental gap between what databases guarantee and what applications need. Recognizing the pattern helps you choose the right isolation level, design safer schemas, and avoid subtle data corruption that only appears under concurrent load.