Start with the decision#
When concurrent actors want the same state, choose whether they may retry, wait, serialize, partition, or merge. The business invariant determines the control; lock syntax does not.
Mechanism and state flow#
- Name the contested invariant and the smallest key that owns it.
- Use optimistic revision checks when conflicts are uncommon and drafts can be retried or merged.
- Use row or advisory locks when one short transaction must serialize a critical state transition.
- Queue or partition operations when high sustained contention makes retries or locks unstable.
- Acquire multiple locks in a stable order, bound transaction duration, and expose conflict/timeout rates.
Concrete example#
Reserve the last seat
A transaction conditionally moves inventory from one to zero and creates the reservation under the same ownership key. Competing requests get a clear unavailable result rather than both reading one and committing.
Architecture consequence: Keep payment outside the inventory lock; reserve first, then use an expiring workflow for the external side effect.
Trade-offs#
| Option | Choose when | Cost or limit |
|---|---|---|
| Optimistic CAS | Conflicts are rare and callers can refresh or merge. | Hot keys waste work through repeated conflicts. |
| Pessimistic lock | The critical section is short and waiting is preferable to retry. | Long holders, deadlocks, and pool exhaustion threaten unrelated work. |
| Single-key queue | Commands can wait and strict ordering is valuable. | Backlog becomes latency and the consumer is a serialization bottleneck. |
Failure diagnosis#
- Failure
- Two transaction paths lock account and ledger rows in opposite order.
- Impact
- Deadlocks abort transactions and retries amplify pressure.
- Detection
- Deadlock diagnostics and lock-wait graphs identify the reversed acquisition order.
- Mitigation
- Use one stable order, keep the transaction bounded, and retry aborted transactions with jitter and an overall deadline.
- Trade-off
- A universal ordering constraint can reduce parallelism or require refactoring.
Design checkpoint#
Work the scenario before revealing the checklist
- Write the invariant and exact ownership key.
- Estimate conflict probability and maximum safe wait.
- Choose CAS, lock, queue, partition, or merge and explain rejected alternatives.
- Draw lock order and the user-visible retry/conflict state.
Use these prompts to extend the design. The useful answer is a reasoned change, not a memorized sentence.
- Can the operation be commutative?
- What is held while calling an external service?
- How are deadlocks retried safely?
- Would partitioning eliminate shared ownership?
Related practice problems
Use the manifest-backed prompts below to apply this lesson in a complete design.
- PostgreSQL transaction isolationPostgreSQL Documentation
- PostgreSQL explicit lockingPostgreSQL Documentation
Sources reviewed 2026-09-04. Service limits and product-specific guarantees still require current verification.