Transaction isolation is often explained with dirty reads and lost updates, but another anomaly is especially important for multi-row business rules: write skew.

Write skew occurs when concurrent transactions read the same valid state, make decisions independently, and update different rows in a way that produces an invalid combined state. Because they do not overwrite the same row, ordinary write-conflict detection may not stop them.

A simple invariant

Imagine an on-call table where at least one doctor must remain available:

alice: on_call = true
bob:   on_call = true

Alice starts a transaction, sees that Bob is on call, and turns herself off. At the same time, Bob starts another transaction, sees Alice on call, and turns himself off.

Each transaction observed a valid snapshot and changed a different row. If both commit, nobody remains on call.

The invariant was not attached to one row. It depended on the relationship between rows.

Why row-level conflict checks can miss it

A lost update typically has both transactions writing the same row. The database can detect that direct conflict or serialize the writes.

Write skew is different:

T1: read Alice, Bob -> update Alice
T2: read Alice, Bob -> update Bob

The write sets do not overlap, even though both decisions depend on the same predicate: “someone else is on call.”

Isolation levels that provide a stable snapshot can therefore still allow the final invariant to fail unless they detect the broader dependency.

Express invariants as constraints when possible

The strongest solution is often to move the business rule into a database constraint.

Simple invariants such as uniqueness, referential integrity, and non-negative values can be enforced directly with UNIQUE, foreign keys, and CHECK constraints.

When the database can reject an invalid state regardless of application interleaving, correctness does not depend on every caller using the same locking convention.

Not every cross-row invariant maps cleanly to a declarative constraint, but it is worth checking before adding procedural coordination.

Lock the rows that define the decision

When a transaction makes a decision based on rows that can change concurrently, explicit locking can serialize the relevant operations.

Conceptually:

BEGIN;

SELECT doctor_id, on_call
FROM doctors
WHERE team_id = 7
FOR UPDATE;

-- verify invariant and update

COMMIT;

The exact locking behavior depends on the database engine and query shape. The important point is that transactions must contend on the data that defines the invariant, not only on the row they eventually modify.

Locking too much reduces concurrency, while locking too little leaves a race. Document the protected invariant so future code uses the same rule.

Serializable isolation targets the broader problem

Serializable transactions aim to produce results equivalent to some serial execution order.

A database may achieve that through locking, conflict detection, or another concurrency-control technique. Some serializable implementations abort one transaction when they detect a dangerous dependency cycle.

Applications using such systems must be prepared to retry transactions that fail for serialization reasons.

A retry loop should rerun the whole transaction against fresh state rather than blindly replaying only the final write.

Do not confuse repeatable reads with serialized business decisions

A stable snapshot is valuable: repeated reads can see a consistent view of data. But consistency within one transaction is not the same as preventing every invalid interleaving between transactions.

Ask a more precise question:

Can two transactions both make a valid decision from their own snapshots and then combine into an invalid final state?

If yes, the invariant needs stronger coordination.

Model the invariant before choosing the mechanism

Start with a sentence that describes what must always remain true:

For each support team, at least one active engineer must remain primary.

Then identify every row and predicate involved in checking that statement.

Possible enforcement mechanisms include:

  • a direct database constraint;
  • locking a parent or coordination row;
  • locking all rows that define the decision;
  • serializable isolation with retries;
  • redesigning the data model so the invariant becomes local.

The best option depends on contention, database capabilities, and how expensive retries are.

A coordination row can simplify locking

Sometimes the natural invariant spans many rows, making broad locking awkward. A dedicated parent row can provide one stable object to lock:

teams
  id = 7

doctors
  team_id = 7

Transactions that change on-call membership first lock the team row. That does not encode the invariant by itself, but it establishes a shared serialization point for all changes affecting the team.

This pattern is useful only if every relevant writer follows it.

Test concurrency, not only single transactions

A normal unit test that executes Alice’s update and then Bob’s update sequentially will not expose write skew.

Concurrency tests should deliberately coordinate two transactions so both read before either commits. Then verify whether the database or application prevents the invalid outcome.

Such tests are especially valuable for:

  • quotas;
  • booking capacity;
  • approval rules;
  • account balances spread across rows;
  • scheduling constraints;
  • uniqueness rules not represented by a unique index.

Common pitfalls

Assuming transactions automatically serialize logic

Transactions provide atomicity, but the isolation level determines what concurrent behavior is possible.

Locking only the row being updated

Write skew often involves different update rows. Protect the rows or coordination point that define the decision.

Using serializable isolation without retries

Some serializable implementations preserve correctness by aborting conflicting transactions. Treat retryable serialization failures as an expected concurrency outcome.

Keeping invariants only in application comments

If correctness depends on an invariant, encode it as close to the data as practical and test concurrent behavior explicitly.

Design around invariants

Isolation levels are easier to reason about when the discussion starts with business invariants instead of level names. Identify what must remain true, determine which concurrent decisions can violate it, and choose a constraint or coordination mechanism that closes that race.

Write skew is a reminder that transactions can each be internally consistent while the final database state is not. Correct concurrency control protects the relationship between decisions, not just individual row updates.

Database Transactions Isolation Concurrency