Dealing with Contention

Contention is when multiple operations try to touch the same piece of data at the same time, and the order matters. Two people buy the last concert ticket in the same millisecond; two withdrawals hit one account; two bids land on one auction. Handled naively — read, decide, write — both operations read "1 available," both decide "yes," and you've oversold. This pattern is about staying correct under that race.

Why read-then-write loses

The broken shape is everywhere:

A: read stock = 1   B: read stock = 1
A: 1 >= 1, ok       B: 1 >= 1, ok
A: write stock = 0  B: write stock = 0   ← both sold the last unit

The gap between read and write is where the race lives. Every fix below closes that gap.

Atomic operations

The cleanest fix: make the decrement a single operation the database performs atomically, so there's no gap to race in.

-- One statement. The DB serialises concurrent runs of it.
UPDATE products
SET stock = stock - 1
WHERE id = ? AND stock >= 1;
-- 0 rows affected → it was already sold out → fail the sale

Because the condition and the write are one statement, two buyers can't both succeed on the last unit. The "rows affected = 0" tells you to reject. This is the simplest correct answer and should be your default.

Optimistic locking

Assume conflicts are rare; detect them at write time instead of locking up front. Keep a version column; read it with the data; on write, require it to be unchanged:

UPDATE documents SET content = ?, version = version + 1
WHERE id = ? AND version = ?;   -- the version you read
-- 0 rows affected → someone else wrote first → reload and retry

Great for low-contention cases (editing a shared doc) — no locks held, so reads stay fast. The cost is that a conflict means the loser must retry.

Pessimistic locking

Assume conflicts are likely; lock the row up front so no one else can touch it until you're done:

BEGIN;
SELECT * FROM seats WHERE id = ? FOR UPDATE;  -- locks the row
-- ...check and book...
COMMIT;                                        -- releases the lock

Right when contention is high and a retry is expensive (booking the one seat everyone wants). The cost is that other transactions wait, which cuts throughput — so lock the smallest thing for the shortest time.

Hold the window, not the user

A common real-world need: reserve something while the user completes a multi-step flow (seat → payment). Don't hold a database lock for 90 seconds across a payment. Instead, write a short-lived reservation (a row, or a Redis key with a TTL) that expires if they don't finish. The reservation is the contention boundary; the lock is only held for the instant it's created.

Picking one

What to say in an interview

Name the race, then the mechanism that removes the read-write gap:

"The danger at checkout is two buyers taking the last seat. I'd make the booking a conditional atomic update — UPDATE ... WHERE seat_id = ? AND status = 'free' — and treat zero rows affected as 'already taken.' For the hold-while-paying step I'd write a reservation with a short TTL rather than lock the row through the whole payment."

Next up: Multi-step Processes — keeping a workflow correct when it spans several writes and services.