Optimistic vs pessimistic locking: an interactive flash sale simulator

When two transactions read the same row and both write it back, one of the writes is lost. Pessimistic locking prevents that by locking the row when it is read, so others wait. Optimistic locking lets everyone read and checks at write time that nobody changed the row in between, retrying if they did. Which is better depends on contention, and often the best answer is neither: a single atomic update.

The playground runs a flash sale for 50 units. Buyers arrive at random for two seconds, and each spends some milliseconds between reading the stock and writing it. The same buyers go through four concurrency controls side by side.

The lost update

Two buyers read a stock of 7, both decide to buy, and both write 6. Two units left the warehouse; the count dropped by one. This read-modify-write race happens under the default isolation level of most databases, READ COMMITTED, because each statement is safe on its own and the gap between them is not. In the flash sale preset, with no locking, 193 units are sold from a stock of 50. Even on a quiet day, one sale goes missing from the count.

Pessimistic locking: wait your turn

SELECT … FOR UPDATE locks the row until the transaction ends, so the next buyer waits at the read. It is always correct, but the row now serves one transaction at a time, and how long each holds the lock decides throughput. At 100 buyers a second with 30 ms of work, the lock can serve about 31 a second, the queue grows to 100 buyers and the median buyer takes 414 ms. With 100 ms of work the median buyer takes 3.8 seconds. Holding a lock while calling another service, or waiting for a user, is how databases end up with long lock queues, timeouts and deadlocks.

Optimistic locking: retry if you lose

Read the row with its version number, do the work without holding anything, then write only if the version is unchanged. Nobody waits, which is ideal when conflicts are rare. Under contention it falls apart in a different way: everyone who read the same version does the work, one wins, and the rest retry together, so each round admits only one. In the flash sale that is 1,811 retries and 53 buyers who gave up after 20 attempts. With 100 ms of work it is worse still: 154 buyers give up and 11 units are left unsold. Each retry also repeats whatever the work was.

Atomic updates: no race to lose

If the change can be written as one statement, let the database apply it: UPDATE items SET stock = stock - 1 WHERE id = 7 AND stock > 0. There is no gap between read and write, the row is locked only for the instant of the update, and the condition makes overselling impossible. In every preset it sells exactly 50 with no waiting beyond the work itself. The same idea covers counters (SET likes = likes + 1), claiming a seat (WHERE held_by IS NULL), and Redis commands like INCR andDECR.

Which to use

Optimistic locking is also what HTTP offers with ETag and If-Match: a client updating a resource sends the version it read, and the server answers 412 Precondition Failed if it changed.

The three in SQL

-- pessimistic: lock the row from the read until the commit
BEGIN;
SELECT stock FROM items WHERE id = 7 FOR UPDATE;   -- others wait here
-- ... decide ...
UPDATE items SET stock = stock - 1 WHERE id = 7;
COMMIT;

-- optimistic: read a version, and only write if it has not changed
SELECT stock, version FROM items WHERE id = 7;     -- say version 41
-- ... decide ...
UPDATE items SET stock = stock - 1, version = version + 1
WHERE id = 7 AND version = 41;                     -- 0 rows: someone beat you, retry

-- atomic: no read-modify-write at all
UPDATE items SET stock = stock - 1
WHERE id = 7 AND stock > 0
RETURNING stock;                                   -- no row returned: sold out

In a system design interview

Contention is where these designs get tested, so say how the hot row is protected: “Seats are claimed with one conditional update: set the hold to this user where the seat is free or its hold has expired. If no row changes, someone else has it. There is no lock held while the user pays; the hold expires after ten minutes.” Expect the follow-ups: what happens with thousands of buyers for one item (split the stock into several rows, or queue the buyers), and how you would handle an edit that spans several records.

Read the guide to optimistic vs pessimistic locking, see fencing tokens for locks across machines, or watch a strong candidate design a ticketing system.

Questions people ask