2026-09-10 · 6 min read · Rahul Chitturi
- sql
- concurrency
- databases
Two request handlers can read version 12, calculate different changes, and then update the same row. If each update matches only the row ID, the later commit silently overwrites the earlier one even though both requests appeared valid.
A version column prevents that lost update only when checking and incrementing the version happen atomically in the write statement.
Reproduce the stale writer
Open two transactions or test workers, read the same row and version, then release both updates together. Record the expected version, affected-row count, returned version, commit result, and sanitized business fields for each attempt.
- Confirm every mutable write path includes the version predicate.
- Check whether an ORM performs a hidden read before its update.
- Distinguish zero matched rows from transport or database failure.
- Verify bulk jobs and administrative tools follow the same contract.
Make compare-and-swap one statement
Use an update shaped like UPDATE items SET value = ?, version = version + 1 WHERE id = ? AND version = ?. Treat exactly one affected row as success and zero as a conflict or missing-row case that the application resolves deliberately.
Do not retry a stale business mutation blindly. Fetch current state, revalidate authorization and intent, and either merge under domain rules or return a conflict for the caller to resolve.
Review the transition safely
Use the SQL formatter to inspect the conditional statement, diff for sanitized old and proposed rows, JSON formatter for synthetic API conflicts, and timestamp converter for request timelines. Keep real customer records and credentials out of browser tools.
Test simultaneous updates, deletes, no-op writes, overflow policy, transaction retries, replica reads, ORM hooks, bulk operations, and external side effects that must not run when the conditional update loses.