"Optimistic vs pessimistic locking"

Two ways to stop concurrent updates from overwriting each other: pessimistic locks with SELECT FOR UPDATE, optimistic concurrency with version numbers and compare-and-set, lost updates, deadlocks, retries, contention levels, HTTP ETags, and which to use for bookings, inventory, documents and counters.

Reading is half of it. See this used in a real interview: walk through Design Ticketmaster →

Two users edit the same record at the same time. Without care, the second save silently overwrites the first (a lost update): a seat is sold twice, a stock count goes wrong, a wiki edit disappears. Databases offer two families of protection: pessimistic locking (block others while you work) and optimistic concurrency (work freely, detect conflicts at save time). Knowing when to use each is a frequent follow-up in booking and inventory questions.

The lost update

A reads stock = 10
B reads stock = 10
A writes stock = 9
B writes stock = 9     -- one sale lost

The fix is to make the read-modify-write safe, by locking or by checking.

Pessimistic locking

Lock the row when you read it, so others wait until you commit:

BEGIN;
SELECT stock FROM items WHERE id = 42 FOR UPDATE;
-- compute
UPDATE items SET stock = stock - 1 WHERE id = 42;
COMMIT;

• Correct by construction: nobody else can change the row in between.
• Blocking: other transactions wait, which hurts throughput under contention and adds latency.
• Deadlocks when transactions lock rows in different orders; the database aborts one, which must be retried. Lock in a consistent order to avoid them.
• Locks must not be held across user think time or slow network calls; keep transactions short.

Variants: FOR UPDATE NOWAIT (fail immediately if locked) and SKIP LOCKED (take the next unlocked row), the latter being ideal for job queues in a table. See transactions and isolation.

Optimistic concurrency

Read without locking, remember a version, and make the write conditional on the version not having changed:

SELECT stock, version FROM items WHERE id = 42;   -- 10, v7
UPDATE items SET stock = 9, version = 8
WHERE id = 42 AND version = 7;                    -- 0 rows means someone else won

If zero rows were updated, re-read and retry (or report a conflict to the user).

• No blocking: readers and writers never wait.
• Cheap when conflicts are rare; under heavy contention, many retries waste work.
• Works across long user interactions (edit a form for five minutes, then save) because nothing is held in between.
• Works across services and over HTTP via ETags and If-Match: the server rejects updates with 412 Precondition Failed if the resource changed. See API design.

Key-value and document stores offer the same through conditional writes (DynamoDB condition expressions, compare-and-set in etcd and Redis WATCH).

Atomic updates: often the simplest answer

Many cases need neither: let the database do the arithmetic in one statement with a guard.

UPDATE items SET stock = stock - 1 WHERE id = 42 AND stock > 0;

One row updated means success; zero means sold out. No read-modify-write, no lock held across application code, correct under any concurrency. See inventory and flash sales.

Choosing

For seats and bookings, a conditional update or unique constraint per seat or night is usually best. See bookings and reservations and Design Ticketmaster.

Very high contention

When thousands of requests fight over one row (a flash sale item), both approaches struggle: pessimistic locks queue up, optimistic retries thrash. Options: split the inventory into buckets, pre-allocate tokens in Redis, or serialise requests through a queue with one consumer per item. See hot keys and skew.

Across services

Database locks do not span services. For cross-service consistency, use optimistic checks with versions on each service's own data, idempotency keys and sagas, or (rarely) distributed locks with fencing tokens. See distributed locks and leases and distributed transactions and idempotency.

In the interview

"Booking claims each night with a conditional update in one transaction, so concurrent attempts fail cleanly; profile edits use optimistic concurrency with a version column and return a conflict if it changed; the stock counter uses an atomic decrement with a non-negative guard." See Design Airbnb and Design a Payment System.

Checklist

• Identify read-modify-write paths that can lose updates.
• Prefer atomic conditional updates and constraints where possible.
• Optimistic versions or ETags for low-contention and long-lived edits.
• Pessimistic locks for short, high-contention critical sections, in a consistent order.
• SKIP LOCKED for table-based queues.
• Buckets or single-writer queues for extreme contention.