Write skew is a concurrency anomaly that arises under snapshot isolation when the following pattern occurs:
- Transaction A and Transaction B both read the same set of rows at their snapshot timestamp.
- Transaction A writes to row X based on what it read.
- Transaction B writes to row Y based on what it read.
- Neither transaction sees the other’s write, but the combination of both writes violates a database invariant.
Classic example — on-call scheduling
-- Invariant: at least one doctor must be on call at all times
-- Table: on_call(doctor_id, is_on_call)
-- Transaction A (Dr. Alice goes off call)
BEGIN;
SELECT count(*) FROM on_call WHERE is_on_call = true; -- sees 2: Alice and Bob
UPDATE on_call SET is_on_call = false WHERE doctor_id = 'alice';
COMMIT;
-- Transaction B (Dr. Bob goes off call — runs concurrently)
BEGIN;
SELECT count(*) FROM on_call WHERE is_on_call = true; -- sees 2: Alice and Bob (snapshot)
UPDATE on_call SET is_on_call = false WHERE doctor_id = 'bob';
COMMIT;
-- Result: no doctors on call — invariant violated
-- Neither transaction saw the other's write at snapshot time
Detection with UUID v7 audit logs
If rows have UUID v7 primary keys, the skew window is recoverable from the ID range:
-- Find rows inserted within the same 100ms window as a known anomalous row
SELECT *
FROM audit_log
WHERE id BETWEEN
uuid_from_timestamp(extract_ms(known_anomaly_id) - 100)
AND
uuid_from_timestamp(extract_ms(known_anomaly_id) + 100)
ORDER BY id;
UUID v7’s timestamp prefix makes this query possible without a secondary index on created_at. See UUID v7 in Prisma + PostgreSQL for uuid_extract_timestamp() usage.
Prevention strategies
| Strategy | Mechanism | Isolation level required |
|---|---|---|
| SERIALIZABLE | Detects read-write cycles | SERIALIZABLE |
| SELECT FOR UPDATE | Locks read rows | REPEATABLE READ |
| Materialized counter | Explicit counter row prevents phantom reads | READ COMMITTED |
| Application CAS | Compare-and-swap at application level | Any |
Write skew vs. related anomalies
| Anomaly | Reads | Writes | Prevented by |
|---|---|---|---|
| Dirty read | Uncommitted data | — | READ COMMITTED+ |
| Non-repeatable read | Same row twice, different values | — | REPEATABLE READ+ |
| Phantom read | Row count changes | — | SERIALIZABLE |
| Lost update | Overlapping | Same row | REPEATABLE READ+ |
| Write skew | Overlapping | Disjoint rows | SERIALIZABLE only |
UUID v7 and distributed write skew
In distributed systems, write skew can occur across shards. UUID v7 timestamps from different shards sort chronologically, enabling post-hoc correlation across shard-partitioned audit tables. See UUID v7 in Distributed Systems.
Further reading
- Idempotency Key — Preventing Duplicate Operations
- Hot Spot — Index Contention
- UUID v7 in Distributed Systems
- UUID v7 in Prisma + PostgreSQL
Frequently asked questions
What is the difference between write skew and lost update?
A lost update occurs when two transactions both write to the same row and one overwrites the other. Write skew occurs when two transactions read the same row, then write to different rows based on that read — neither transaction sees the other's write, but together they violate an invariant. Write skew is a weaker anomaly, possible under snapshot isolation, while lost updates are prevented by most isolation levels above READ COMMITTED.
How does UUID v7 help diagnose write skew in audit logs?
UUID v7 encodes a 48-bit Unix millisecond timestamp in its primary key. When a write-skew anomaly occurs, the conflicting rows were inserted within a short time window. Querying WHERE id BETWEEN skew_start_uuid AND skew_end_uuid extracts all rows from that window without a separate created_at index. This helps database administrators identify concurrent transactions that co-occurred without explicit lock contention. See Idempotency Key for prevention patterns.
Which isolation level prevents write skew?
SERIALIZABLE isolation prevents write skew by detecting the read-write dependency cycle and aborting one of the conflicting transactions. REPEATABLE READ and SNAPSHOT isolation do not prevent write skew. PostgreSQL's SERIALIZABLE uses Serializable Snapshot Isolation (SSI) which detects write-skew patterns efficiently. Most NoSQL and distributed databases require application-level COMPARE-AND-SET operations to prevent write skew.