Decision guide
Atomic Update vs Pessimistic vs Optimistic Locking
How should concurrent writes to the same data be controlled?
Side by side
| Criterion | Atomic conditional update | Pessimistic lock (SELECT … FOR UPDATE) | Optimistic version check | Constraint / upsert | Queue / single writer |
|---|---|---|---|---|---|
| Blocks other requests | Briefly (row lock for one statement) | Yes, until commit | No | Briefly | No (asynchronous) |
| Handles multi-step logic | No | Yes | Yes (with retry or user merge) | Limited | Yes |
| Behaviour under heavy contention | Best | Queues, risk of deadlocks | Many failed attempts | Good | Good, adds latency |
| Protects against other code paths | Only callers that use it | Only callers that lock | Only callers that check versions | Yes, always | Only producers using the queue |
When to choose each
Atomic conditional update
One UPDATE … SET x = x - n WHERE x >= n; check affected rows.
Choose when
- The rule fits in a WHERE clause on one row (stock, balances, quotas)
- Very high request rates
Avoid when
- Multi-step logic that needs data from several places before deciding
Pessimistic lock (SELECT … FOR UPDATE)
Lock rows when reading; others wait (or NOWAIT fails fast).
Choose when
- Multi-step business checks on a specific row (seat holds, transfers)
- Conflicts are likely and retries would be wasteful
Avoid when
- Slow work or external calls inside the transaction
- Very hot single rows (serializes everything)
Optimistic version check
UPDATE … WHERE version = :read_version; zero rows means someone else won.
Choose when
- Long think-time edits (admin forms, documents)
- Conflicts are rare
- Stateless APIs using ETag / If-Match
Avoid when
- High contention on the same row (retry storms)
Constraint / upsert
Unique or exclusion constraints and ON CONFLICT let the database arbitrate.
Choose when
- Uniqueness rules (one active subscription, one webhook processing)
- Insert-or-update counters
- Rules that must hold for every code path
Avoid when
- Rules the database can't express as a constraint
Queue / single writer
Funnel writes for a key through one consumer (partitioned queue).
Choose when
- Extremely hot keys
- Work that can be asynchronous
- Ordering per key matters
Avoid when
- The user needs an immediate, synchronous answer
Verdict
Start with an atomic conditional update whenever the rule fits one statement, and back it with a constraint. Use pessimistic locks for short multi-step logic on specific rows, optimistic versions for human edits and low-contention APIs, and a queue for extremely hot keys or asynchronous work.