Contention and locking

Contention is many operations trying to change the same piece of state. The database will serialize them for you if you ask correctly. It will also fall over if you ask it to serialize tens of thousands of updates to one row. Name which situation you are in.

One row, modest concurrency

Make the write conditional so it can only succeed from the state you observed:

UPDATE seats
SET status = 'sold', owner = :user
WHERE id = :id AND status = 'open';

If the update count is zero, you lost. Return a conflict and let the user pick another seat. An integer version column is the same trick for richer edits: WHERE version = :expected, then increment it. That is optimistic concurrency. It is the right default for profiles, documents, and carts.

Pessimistic locking (SELECT … FOR UPDATE) is for the case where you must read several rows, decide, and write, and a retry would be expensive or incorrect. Keep the transaction short. Do not call a payment provider while you hold the row lock. Record the intent, commit, then call out.

Leases instead of immortal locks

If a human or a worker holds the right to edit, store lease_until and a holder id. Heartbeat to extend. Anyone may take it after expiry. This is how you avoid a crashed client pinning a ticket forever. See the task-routing problem for the full shape.

When the row itself is too hot

A flash sale that decrements one counter will serialize on that counter no matter how many app servers you add. Options, from least to most machinery:

• Batch updates in memory and flush a sum a few times a second, if you can tolerate a short oversell window and correct it.
• Shard the counter into N rows and sum on read. Oversell is still possible unless the sum is itself guarded.
• Put requests on a queue and let a single consumer apply them in order. Throughput becomes the consumer’s throughput, which is the honest capacity. The HTTP call returns “queued” or waits with a timeout.
• Change the product so you do not need a synchronous global count (reserve in bulk per worker, reconcile later).

A distributed lock around the hot key does not increase the key’s capacity. It only adds a failure mode. Use it when you truly have one critical section across services, not as a reflex.