Pessimistic vs Optimistic Locking: Which One You Actually Need

Two users open the same record, both hit Save. Whose change wins, and does the other one know? That's the concurrency problem — and the database gives you two styles of answer.

Pessimistic locking

Acquire a row-level lock before you touch the data. No one else can modify that row until you commit.

BEGIN;
SELECT * FROM orders WHERE id = 42 FOR UPDATE;
-- row is exclusively locked until COMMIT / ROLLBACK
UPDATE orders SET status = 'shipped' WHERE id = 42;
COMMIT;

Use when: contention is high, the update is short, and correctness trumps throughput. Classic examples: seat reservations, inventory decrement, money transfers.

Watch out for: long-held locks (they queue everyone else), deadlocks (two tx grab rows in opposite order), and connection-pool starvation.

Optimistic locking

No lock. Every row carries a version number. On update, you check the version hasn't changed and bump it. If it has — retry.

-- Read
SELECT id, total, version FROM orders WHERE id = 42;
-- version = 7

-- Write (fails if someone else updated in between)
UPDATE orders
   SET total = 199, version = version + 1
 WHERE id = 42 AND version = 7;

-- rows_affected = 0 → conflict; re-read and retry

Use when: contention is low and you want high throughput. Most web apps (edit a blog post, update a profile) fit here. Hibernate and Entity Framework both have @Version annotations that automate this.

Side-by-side

	Pessimistic	Optimistic
Throughput	Lower	Higher
Contention behaviour	Queues	Retries
Deadlock risk	Yes	No
Data loss risk	None	None (retries surface conflict)
Best for	Money, inventory	User-edited content

Hybrid: advisory locks

Sometimes you want a short coordination primitive outside the row model — "only one worker processes this job at a time." Postgres advisory locks (pg_try_advisory_lock) and Redis locks (single-flight) shine here.

The rule

Start with optimistic locking and @Version everywhere you have user-editable data. Reach for pessimistic locks in the specific spots where a lost retry is worse than a brief queue. Don't mix the two on the same table without a very good reason.