2026-09-09 · 6 min read · Rahul Chitturi
- sql
- transactions
- concurrency
Two transactions can each read a valid snapshot, update different rows, and commit a combined state that violates a business rule. This write-skew pattern may not create a direct row-level write conflict, so ordinary update locking or repeatable reads can appear successful.
Examples include two on-call operators both going off duty or two reservations each consuming the last shared capacity under separate rows.
State the invariant in database terms
Write the rule over exact rows, predicates, and tenant boundaries. Record each transaction's reads, writes, start and commit times, isolation level, retry behavior, and database engine version.
- Distinguish lost updates from write skew
- Include predicate reads that return no rows
- Check whether replicas or caches serve decision reads
- Keep external side effects outside uncertain retries
Choose protection for the invariant
A constraint is preferable when the rule can be represented directly. Otherwise, lock a stable parent or coordination row that every writer touches, or use true serializable isolation and retry classified serialization failures.
Locking only rows returned by a predicate may not protect future inserts or updates to other rows. Engine-specific predicate locking and snapshot-isolation behavior must be verified rather than inferred from an isolation-level name.
Run a synchronized concurrency test
Use the SQL formatter to compare statements, timestamp converter for the transaction timeline, UUID generator for fixture identities, JSON formatter for sanitized events, and diff for expected versus actual states.
Pause both transactions after their reads, release both writes together, and repeat under the production isolation setting. Test serialization retries, deadlocks, timeouts, failover, connection-pool defaults, and invariant checks after every commit.