Optimistic vs Pessimistic Locking

The Problem: Two Riders Accept the Same Order

In our food delivery app, when food is ready, nearby riders (delivery partners) see the order and tap Accept. The first rider to accept should get it.

Two riders tap Accept at almost the same moment:

1Time   Rider A                          Rider B
2t1     read order 901 → status: ready
3t2                                      read order 901 → status: ready
4t3     set rider = A, status = assigned
5t4                                      set rider = B, status = assigned
6→ Rider A thinks they have the order
7→ Rider B overwrote it. Two riders drive to the restaurant.

This is a lost update: two requests read the same data, both change it, and the second write silently erases the first.

The problem is the gap between reading the row and writing it. There are two ways to protect that gap.

Two Ways to Fix It

Think about a shared meeting room:

• Pessimistic: you book the room before using it. Nobody else can enter until you leave. Safe, but others wait outside.
• Optimistic: you just walk in. If someone else got there first, you notice and try again. No waiting, but sometimes you redo your work.

Let's start with the pessimistic approach.

Pessimistic Locking

Pessimistic locking means you lock the row as you read it. Anyone else who tries to lock the same row must wait until your transaction ends.

In SQL, you add FOR UPDATE to the SELECT. It means "I'm going to change this row, so lock it for me".

1Time   Rider A                                   Rider B
2t1     BEGIN; SELECT ... FOR UPDATE → gets lock
3       status: ready
4t2                                               BEGIN; SELECT ... FOR UPDATE
5                                                 → WAITS (row is locked)
6t3     UPDATE rider = A, status = assigned
7       COMMIT → lock released
8t4                                               lock acquired, status: assigned
9                                                 → "Order already taken"

BEGIN;

SELECT status FROM orders
WHERE id = 901
FOR UPDATE;                         -- lock this row until COMMIT

-- app checks: status = 'ready'?
UPDATE orders
SET rider_id = 15, status = 'assigned'
WHERE id = 901;

COMMIT;                             -- lock released

MongoDB has no SELECT ... FOR UPDATE. Inside a transaction, if two transactions write the same document, one gets a write conflict error and must retry. That behaves more like optimistic locking, which we'll cover next.

The cost: waiting. If a lock is held for 20 ms and 100 requests hit the same row, they line up:

1100 requests × 20 ms each = 2,000 ms
2→ the last request waits about 2 seconds

Locks can also cause deadlocks: two transactions each holding a lock the other one needs. That gets its own page.

Waiting is wasteful when conflicts almost never happen. That's where optimistic locking helps.

Optimistic Locking

Optimistic locking means you don't lock anything. Instead, each row has a version number. When you write, you say: "update this row only if the version is still the one I read". If someone changed it in between, the version moved, your update matches 0 rows, and you know you lost.

1orders row 901: status = ready, version = 3
2 
3Time   Rider A                                 Rider B
4t1     read → version 3
5t2                                             read → version 3
6t3     UPDATE ... WHERE version = 3
7       → 1 row updated, version becomes 4
8t4                                             UPDATE ... WHERE version = 3
9                                               → 0 rows updated (version is 4 now)
10                                               → "Order already taken"

Nobody waited. Rider B simply found out their write didn't apply.

ALTER TABLE orders ADD COLUMN version INT NOT NULL DEFAULT 0;

-- 1. read the row and remember its version
SELECT status, version FROM orders WHERE id = 901;
-- status = 'ready', version = 3

-- 2. update only if the version hasn't changed
UPDATE orders
SET rider_id = 15, status = 'assigned', version = version + 1
WHERE id = 901 AND version = 3;
-- "UPDATE 1" = success, "UPDATE 0" = someone else changed it

The cost: retries. If many users fight over the same row, most of them lose and redo their work:

110 riders accept the same order at once
2→ 1 succeeds, 9 get "0 rows updated"
3→ fine here (they just see "already taken")
4 
5Same pattern on a wallet with constant top-ups and payments
6→ many retries, wasted work, slower than simply waiting

So which one should you pick?

Which One to Choose

Quick rule: use optimistic by default. Switch to pessimistic when the same row is updated very often and retries would waste a lot of work, or when a retry is costly (like calling a payment gateway again).

Interview Angle

Common mistakes:

• Forgetting to check the row count after an optimistic update. Without that check, the version column does nothing.
• Holding a pessimistic lock while calling a slow external service. The row stays locked the whole time.
• Thinking optimistic locking "prevents" conflicts. It detects them; your code still has to handle the failure.

Summary

1Problem       → lost update: read, read, write, write → first write erased
2Pessimistic   → SELECT ... FOR UPDATE → others wait → safe, but queues + deadlocks
3Optimistic    → version column → UPDATE ... WHERE version = ? → 0 rows = conflict → retry
4JPA           → @Lock(PESSIMISTIC_WRITE) / @Version
5MongoDB       → no FOR UPDATE; use version checks or write-conflict retries
6Choose        → optimistic by default, pessimistic for hot, high-conflict rows

Pessimistic locks make transactions wait for each other, and sometimes they wait forever. Next, we'll look at deadlocks: why they happen and how to prevent them.