Concurrency Control: Locks, Optimistic vs. Pessimistic

Here's a bug that never shows up in a demo and always shows up in production: an inventory count reads as 5, two customers hit "buy" within a few milliseconds of each other, and both requests see 5 in stock. Both decide there's enough. Both write back 4. The database now says 4, even though two units actually sold — one decrement simply vanished. Nothing crashed, no error was logged, and the numbers just quietly drift out of sync with reality.

This is the classic lost update. It isn't a bug in any single request's logic — read 5, subtract 1, write 4 is perfectly correct in isolation. The bug is in the assumption that nothing else touches the row between the read and the write. Concurrency control is the set of techniques for making that assumption safe, and it splits into two fundamentally different philosophies: stop the interleaving from happening at all, or let it happen and detect it after the fact.

Pessimistic locking: assume the worst, and wait

Pessimistic locking takes the direct approach — before a transaction touches a row, it acquires a lock on it. Any other transaction that wants the same row has to wait until the lock is released, or fail fast if it isn't willing to wait. In SQL terms this is a SELECT ... FOR UPDATE: the first transaction to run it owns the row exclusively until it commits or rolls back, and every competing transaction queues up behind it.

This is safe by construction — there's no window where two transactions can both believe they have exclusive access, because the lock physically prevents it. The cost is throughput. Under real contention, requests stop running concurrently and start running one at a time, queued behind whoever grabbed the lock first. If that transaction is slow — a network call, a slow downstream service, an accidental long-running block — everyone behind it in the queue pays for it too.

Optimistic concurrency control: assume the best, and verify

Optimistic concurrency control (OCC) flips the assumption: most of the time, nobody else is touching the same row at the same moment, so don't pay the cost of locking on every write. Instead, read the row along with a version number (a plain integer that increments on every update works just as well as a timestamp), do your work, and make the write conditional on that version still being current. If it is, the write proceeds and bumps the version. If it isn't — because someone else updated the row in the meantime — the write affects zero rows, and the caller knows to reread and retry.

-- Read: fetch the current value AND its version
SELECT qty, version FROM inventory WHERE id = 42;
-- => qty = 5, version = 7

-- Write: succeed ONLY if nobody else has changed it since we read it
UPDATE inventory
SET qty = qty - 1,
    version = version + 1
WHERE id = 42
  AND version = 7;

-- Check rows affected:
--   1 row  -> our write won the race, done
--   0 rows -> someone else updated first (version moved on);
--             reread the row and retry

The version check is what makes this safe without ever taking a lock. Both concurrent requests in the original example would read version = 7. The first one to commit succeeds and moves the row to version = 8. The second one's WHERE version = 7 no longer matches anything — zero rows affected — so instead of silently overwriting the first request's work, it fails visibly and gets a chance to reread the now-correct qty and try again. The lost update becomes an impossible outcome instead of an occasional one.

Because no lock is held while the transaction does its work, other requests against the same row are never blocked waiting on this one — they proceed immediately and take their own chances on the version check. When conflicts are actually rare, which is true for most real-world workloads, this means dramatically better throughput than pessimistic locking, at the cost of occasionally having to retry a request that lost a race.

Deadlocks: when pessimistic locking locks itself

Pessimistic locking introduces its own failure mode. Suppose transaction A locks row 1 and then needs row 2, while transaction B has already locked row 2 and now needs row 1. Neither can proceed — each is waiting for a lock the other is holding, and neither is willing to give up what it already has. That's a deadlock, and left alone, both transactions would wait forever.

Databases handle this in a few standard ways. Most run a deadlock detector that periodically looks for these circular wait cycles and forcibly aborts one of the transactions (rolling it back) so the other can proceed — the aborted side is expected to retry. Applications can reduce how often this happens by enforcing a consistent lock ordering — always acquiring row locks in the same order (say, by primary key) across every code path, so the circular wait pattern in the example above can't form in the first place. Lock timeouts add a second layer of defense: a transaction that waits too long for a lock gives up and fails fast rather than sitting in the queue indefinitely.

Which one to reach for

Neither approach is strictly better — they trade off differently depending on how often transactions actually collide and how expensive a retry is. Reach for pessimistic locking when conflicts are frequent or the cost of getting it wrong is high: a financial transfer debiting one account and crediting another should not be left to a retry loop, and heavy contention on a hot row often means transactions would just keep colliding and retrying under OCC anyway, wasting more work than a lock would have cost.

Reach for optimistic concurrency control when conflicts are rare and you want the throughput benefit of never blocking on the happy path — which describes most ordinary CRUD update paths, where two users editing the same record at the exact same moment is the exception, not the rule. The version column costs almost nothing to maintain, and the occasional retry is far cheaper than making every write queue behind a lock it usually didn't need.