2026-09-08 · 6 min read · Rahul Chitturi
- sql
- concurrency
- databases
An upsert combines an insert attempt with conflict handling, but it does not automatically make every read-modify-write workflow atomic. Two requests can target the same business entity while disagreeing about which columns may change or which event is newer.
Database engines differ in MERGE, ON CONFLICT, duplicate-key update, locking, trigger, and return-value behavior. Diagnose the exact statement and version instead of treating upsert as one portable operation.
Define the conflict identity
Choose a unique constraint that represents the business identity and confirm all writers use it. A conflict on one index may not protect a second invariant involving another row or table.
- List columns updated on the conflict path
- Separate immutable identity from mutable attributes
- Check null and collation behavior in unique keys
- Inspect triggers and generated values on both paths
Prevent stale writes
If updates are conditional on a version, timestamp, or state transition, put that condition in the atomic statement and verify whether a row was actually changed. A prior application read can become stale before the write.
Use transaction isolation and explicit locks only with a documented invariant and contention plan. Classify deadlocks and serialization failures for bounded retries with idempotent inputs.
Exercise real concurrency
Use the SQL formatter to review each statement, diff for before-and-after rows, JSON formatter for sanitized result metadata, timestamp converter for event ordering, and UUID generator for synthetic keys.
Test simultaneous creates, simultaneous updates, stale versions, null keys, trigger failures, returned-row semantics, deadlock retries, replicas, bulk operations, and every supported database engine.