2026-09-08 · 8 min read
- sql
- concurrency
- databases
An upsert asks a database to insert a row or take another action when a uniqueness conflict occurs. It can remove a race between a separate existence check and insert, but it does not automatically protect unrelated invariants, prevent stale updates, or make retries harmless.
PostgreSQL ON CONFLICT, MySQL duplicate-key updates, SQLite conflict clauses, and vendor MERGE statements differ in matching, locking, trigger, generated-value, and return behavior. Treat the exact engine and version as part of the contract.
Choose the conflict target from the domain
Back the business identity with a database unique constraint and target that constraint deliberately. Confirm null handling, collation, partial predicates, tenant keys, and soft-delete rules match how every writer identifies the row.
- Keep immutable identity separate from mutable data.
- List each column changed on the conflict path.
- Avoid updating a conflict key without a migration plan.
- Use another transaction strategy for cross-row invariants.
Make stale-write policy atomic
When only a newer version or valid state transition may update the row, include that predicate in the upsert or an adjacent locked transaction. Then inspect the affected-row or returned-row semantics to distinguish inserted, updated, and rejected cases.
Do not read a version in application code and assume it remains current until a later statement. Use a version counter, expected state, or event sequence in the database condition.
Plan triggers, isolation, and retries
Document which insert and update triggers run, how defaults and generated columns behave, and whether the engine can report the final row reliably. Observe lock waits and deadlocks under the chosen isolation level.
- Retry only classified transient database failures.
- Bound attempts and add jitter under contention.
- Reuse a durable operation key for external side effects.
- Never hide constraint failures with an unconditional retry loop.
Load-test competing writers
Use Flashman's SQL formatter to review statements, diff for expected row transitions, JSON formatter for sanitized result events, timestamp converter for timelines, and UUID generator for synthetic operation and entity keys.
Test simultaneous inserts, conflicting updates, stale versions, null and collation edges, partial indexes, triggers, deadlocks, serialization failures, retries after unknown outcomes, bulk writes, replicas, and engine upgrades.