Core idea (one-liner)
- Upsert → “Let the database resolve conflicts automatically.”
- Pessimistic locking → “Prevent conflicts by locking data in advance.”
They solve different problems.
Upsert
What it is
A single atomic write:
- Insert if row doesn’t exist
- Update if it does
- Based on a unique constraint
INSERT INTO orders (id, status)
VALUES (42, 'PAID')
ON CONFLICT (id)
DO UPDATE SET status = EXCLUDED.status;
Characteristics
✅ Lock-free at application level
✅ Naturally idempotent
✅ High throughput
✅ Works great with retries
❌ Limited business logic
❌ “Last write wins” unless carefully designed
Pessimistic locking
What it is
You explicitly lock rows before modifying them.
BEGIN;
SELECT * FROM accounts
WHERE id = 1
FOR UPDATE;
UPDATE accounts
SET balance = balance - 100
WHERE id = 1;
COMMIT;
Characteristics
✅ Strong consistency
✅ Good for state transitions
✅ Prevents lost updates
❌ Lower throughput
❌ Risk of deadlocks
❌ Requires careful transaction management
Side-by-side comparison (interview gold)
| Aspect | Upsert | Pessimistic Locking |
|---|---|---|
| Concurrency model | Optimistic | Defensive |
| Idempotence | Natural | Must be designed |
| Performance | High | Lower |
| Blocking | Minimal | Yes |
| Deadlocks | No | Possible |
| Retry safety | Excellent | Dangerous |
| Business logic | Limited | Complex allowed |
When to use Upsert
✔ Event ingestion
✔ Kafka consumers
✔ External system sync
✔ REST retries
✔ Deduplication
✔ Projections / read models
Example:
INSERT INTO processed_events (event_id)
VALUES ('evt-123')
ON CONFLICT DO NOTHING;
When to use Pessimistic Locking
✔ Money transfers
✔ Inventory decrement
✔ Stateful workflows
✔ Counters with invariants
✔ “Exactly once” financial logic
Example:
SELECT balance FROM account
WHERE id = 1
FOR UPDATE;
Why upsert cannot replace locking
❌ Wrong approach:
UPDATE account SET balance = balance - 100
ON CONFLICT DO UPDATE ...
This still allows double spending under retries.
Locks protect invariants, not existence.
Common real-world pattern (very senior)
Combine both
- Upsert to ensure idempotence
- Lock only when changing critical state
Example:
Request → idempotency key → upsert request record
→ SELECT ... FOR UPDATE
→ apply state transition
This is how payments systems are built.
Typical interview mistakes ❌
- “Upsert replaces locks” → ❌
- “Locks are slower so don’t use them” → ❌
- “Transactions are enough” → ❌
- “Just retry on failure” → ❌
Interview-ready answer (say this)
Upsert is an optimistic, lock-free way to insert or update data atomically and is ideal for idempotent writes under retries.
Pessimistic locking explicitly locks rows to protect business invariants and is necessary for state-dependent logic like balances or inventory.
In practice, systems often combine both: upserts for idempotence and locks for critical state transitions.