The idea: two ways to stop a lost update

Many app requests do a read-modify-write: read the stock, do some work (price, payment call), write stock - 1. When two requests do this to the same row at the same time, one of the writes can be lost. There are two classic ways to prevent that.

Pessimistic locking assumes a conflict will happen. It locks the row before reading it and keeps the lock until it commits, so others must wait. Optimistic locking assumes a conflict is rare. It takes no lock, and checks at write time that nobody changed the row since it was read. If somebody did, it throws the work away and tries again.

Two timelines over ticks t1 to t8. Pessimistic: T1 locks and reads 10, works, writes 9 and commits at t4; T2's SELECT FOR UPDATE waits from t2 to t4, then reads 9 and writes 8. Optimistic: both read 10 with version 1; T1's UPDATE WHERE version = 1 matches 1 row and commits (9, v2); T2's matches 0 rows at t4, so it retries, reads 9 with v2, and writes (8, v3).
Both end with stock 8: pessimistic locking makes T2 wait for the lock, optimistic locking lets it run and redo its work after a version conflict.

The page runs the same buyers through both at once, on one clock. Left: pessimistic (or no locking at all). Right: optimistic. Each client runs one statement per tick. The timeline shows, per tick, whether a client holds a lock, waits, works, or retries.

The lost update

T1 and T2 both read stock = 10. Both compute 9. Both write 9. Two pens were sold, but the stock went down by one. Each statement was fine on its own: the bug is that the value written was computed from a read that was already stale. (Demo: no locking at all, left side.) The database's isolation level does not save you here when the read and the write are separate statements; see MVCC and Isolation Levels for how each level behaves.

T1 reads stock 10, T2 reads stock 10, T1 writes 9, T2 also writes 9 computed from its stale read; two pens were sold but stock dropped by one.
Without a lock or a version check, the second write overwrites the first, and T1's update is lost.

Pessimistic locking: SELECT … FOR UPDATE

BEGIN;
SELECT stock FROM items WHERE id = 1 FOR UPDATE;   -- X lock, others wait
-- work ...
UPDATE items SET stock = 9 WHERE id = 1;
COMMIT;                                             -- lock released

FOR UPDATE takes an exclusive row lock. Another FOR UPDATE (or UPDATE) on that row joins the row's lock queue and waits. At COMMIT the lock goes to the head of the queue, and that transaction reads the new value. Every piece of work is done exactly once. (Demo: two buyers, one row.)

The costs:

  • Waiting. Everyone behind the lock waits for the whole work of the one in front, not just for its write.
  • Long transactions. The transaction, and the database connection it uses, stay open through the work. Slow work means few connections left in the pool.
  • Lock wait timeout. InnoDB gives up after innodb_lock_wait_timeout (50 s by default; 8 ticks here) with ERROR 1205. The app must roll back and retry. (Demo: hot row, long work: T3 times out.)
  • Deadlocks. T1 locks pen then wants ink; T2 locks ink then wants pen. Each waits for the other. InnoDB finds the cycle in its wait-for graph at once and rolls one back with ERROR 1213. (Demo: crossed lock order.) The fix is to always lock rows in the same order, for example by id.

Optimistic locking: the version column

SELECT stock, version FROM items WHERE id = 1;     -- 10, v1, no lock
-- work ...
UPDATE items SET stock = 9, version = version + 1
 WHERE id = 1 AND version = 1;                      -- 1 row: done; 0 rows: retry

The row carries a version number (or a timestamp, or a hash of the row). The write only matches if the version is still the one that was read. If another transaction committed first, the version has moved on, the UPDATE reports 0 rows, and the app starts over from the read. Nothing is locked during the work, nobody waits, and deadlocks cannot happen. (Demo: two buyers, one row, right side.)

The cost is the retry: all the work of the failed attempt is thrown away. With many clients on one hot row and long work, most attempts fail and have to redo everything. The wasted work grows with each round. (Demo: hot row, long work: 3 retries and 18 wasted ticks on the right, against one queue and one timeout on the left.)

Frameworks do this for you: JPA / Hibernate @Version adds the AND version = ? and throws OptimisticLockException on 0 rows. Over HTTP the same idea is the ETag and If-Match header: a stale PUT gets 412 Precondition Failed.

Which one when

Pessimistic (FOR UPDATE)Optimistic (version)
Low contention: different rows, or arrivals spread in timeWorks, but holds locks and transactions for nothingBest: no waits, no retries, short transactions (Demos: different rows, arrivals spread out)
Hot row, long workBest: a queue, each work done onceRetry storm, wasted work grows
Read and write in different HTTP requests (edit form, then save)Not possible: you cannot hold a lock while the user thinksThe natural fit (version in a hidden field or ETag)
Failure modesWaits, lock wait timeout, deadlockConflict → retry, give up after N
Work with side effects (payment call)Done onceMust be safe to redo (idempotent), or done after the write

A common middle way: optimistic by default, and pessimistic (or a queue) only for the few rows that are known to be hot.

Retries, idempotence and backoff

Both sides retry: the optimistic side on 0 rows, the pessimistic side on a deadlock or a timeout. A retry must start from the read again, never reuse the old value. Real apps wait a short, random time before retrying (backoff with jitter), so the same clients do not collide again in the same way; the page retries on the next tick to keep the timeline easy to follow. After 5 attempts a client gives up (409 Conflict).

See also Saga vs Two-Phase Commit (locks held across services vs compensation), How MySQL Runs a Query, Dining Philosophers (deadlock from lock order), and Compare-and-Swap (the same version check done by one CPU instruction).

What the page leaves out

Shared locks (FOR SHARE), gap and next-key locks, and NOWAIT / SKIP LOCKED, which change what a waiter does. MVCC: plain readers never wait for a FOR UPDATE lock in InnoDB or PostgreSQL. The atomic alternative UPDATE items SET stock = stock - 1 WHERE id = 1 AND stock > 0, which needs no read at all when the new value does not depend on work done in the app. Real timings: a statement takes microseconds, the work takes milliseconds to seconds; here both take one tick. How InnoDB picks a deadlock victim (the transaction that changed fewer rows); here it is always the one that closed the cycle.