2026-09-09 · 8 min read
- sql
- transactions
- concurrency
Write skew occurs when concurrent transactions read a state that satisfies an invariant, write different rows, and commit a combined state that violates that invariant. Because neither transaction overwrites the other's row, ordinary conflict detection may not stop them.
Snapshot-based isolation can provide stable reads without producing a serial execution for predicate-based business rules. The exact guarantees depend on the database engine and isolation implementation.
Express the invariant precisely
Describe the rule as a predicate over database state, including tenant filters, active-status conditions, null behavior, and rows that do not yet exist. Then list every transaction that can change the predicate.
- Prefer a declarative constraint when the database can express the rule.
- Keep decision reads on the authoritative database.
- Include background jobs and administrative write paths.
- Distinguish event time from transaction commit order.
Choose a shared conflict point
When a direct constraint is not possible, a stable parent or coordination row can serialize writers that affect the same invariant. Every participating transaction must lock or update that point in a consistent order.
Locking only rows currently returned by a query may not protect an empty range or a future insert. Advisory locks can work when their keying, ownership, timeout, and connection behavior are documented, but they are not a substitute for forgotten write paths.
Use serializable isolation with bounded retries
A true serializable mode can abort one transaction when the database detects a dangerous dependency. Classify only documented serialization failures as retryable, restart the entire transaction from fresh reads, and bound attempts with backoff.
- Make request inputs and external effects idempotent.
- Do not retry an unknown commit blindly.
- Measure abort and lock-wait rates under production-shaped load.
- Keep transaction bodies short and deterministic.
Prove behavior with controlled concurrency
Use Flashman's SQL formatter for statements, timestamp converter for timelines, UUID generator for synthetic rows and operations, JSON formatter for sanitized events, and diff for expected state transitions.
Synchronize two transactions immediately after reads, release writes together, and assert the invariant after each commit. Cover inserts, updates, empty predicates, retries, deadlocks, timeouts, failover, pooled connections, replicas, maintenance jobs, and engine upgrades.