Optimistic locking fails on the row you care about

Every benchmark that convinced you optimistic concurrency control is faster was run against uniformly distributed keys. Seed a million rows, pick one at random, update it, measure. Under that workload conflicts are rare, retries are rare, and the version column wins comfortably because it never waits for anything.

Production does not pick rows at random. Production has one merchant settlement account that every single payment touches, one SKU that a launch post just pointed half the internet at, one rate limit bucket belonging to the customer who put your API in a while loop, and one daily_totals row that every write in the system increments on its way past. The distribution has a long tail, and the tail is the business.

That is the entire argument. Optimistic and pessimistic are not a performance preference or a house style. Each is a claim about how your writes are spread across your keys, and on the rows that matter most, one of those claims is false.

Both are the same trick, played at different times

The thing being defended is always an invariant that spans a read and a write:

read:   seats_left is 1
decide: fine, sell it
write:  seats_left = 0

Two of those interleave and you have sold the same seat twice. Nothing here is specific to seats. It is the shape of every stock check, every quota check, every overdraft check, every "claim this job if nobody else has", and every edit form that loads a record and saves it back.

There are two ways to make the decision still true at the moment of the write.

Optimistic carries evidence about what was read and refuses the write if the evidence has gone stale. A version column, a timestamp, or the old value itself goes into the WHERE clause. Losers find out at write time and start again.

Pessimistic stops the interleaving from happening. The first reader takes a lock, the second reader waits at the read rather than failing at the write.

One correction before going further, because it drives most of the bad reasoning in this area: optimistic control is not lock free. An UPDATE takes a row lock in any database that supports transactions. What differs is how long the lock is held and what happens to whoever loses. Optimistic holds a lock for the duration of the write and makes the loser redo work. Pessimistic holds it for the duration of the thinking and makes the loser wait.

That last row is not a performance property and it settles more arguments than the rest of the table put together. You cannot hold a database transaction open while a human stares at an edit form for four minutes. If the read and the write are in different requests, optimistic is the only option available, and the version has to make a round trip through the client.

A conflict costs more than the write

The reason retry-based schemes degrade sharply rather than gently is that a retry throws away the transaction, not the statement.

If a transaction does 30ms of work before it reaches the contended UPDATE, then a conflict costs 30ms and you pay all of it again on the next attempt. The cost of a conflict is proportional to how much work precedes the conflicting write, so the retry rate and the transaction size multiply rather than add. This is why the same version column can be invisible in one service and pathological in another with an identical conflict rate.

Now put n writers on one row at the same instant. Roughly one succeeds per round, so the unluckiest of them expects on the order of n attempts, and the group burns O(n²) work to do n updates. Pessimistic does the same n updates with n waits, and a wait is not work: the thread is parked, the CPU is free, the database is doing something else.

The uglier property is fairness. Lock queues are approximately first in, first out. Retry loops are not ordered at all, and they are actively biased: the transaction that does the most work before its write is the most likely to be beaten to the commit, and it is the most likely to be beaten again on the retry. Under sustained load on a hot row, an optimistic scheme starves whichever operation is most expensive, which in a financial system is usually the settlement run, the month end job, or the one report the CFO looks at.

Nobody sees this in staging. Starvation needs a queue of real concurrent writers to select against.

Contention concentrates, and it concentrates on the important things

Your hottest key does not have the mean write rate. It has the maximum, and in a real system the ratio between the two is routinely two or three orders of magnitude.

The hot rows are always the same four kinds of thing:

• The aggregate. Fee income, the treasury account, orders_today, a per tenant usage counter. Every operation in a class of operations touches one row by construction.
• The scarce resource. The last four seats, the limited edition SKU, the licence pool, the username somebody is trying to reserve. Contention rises exactly as the resource runs out, so the worst behaviour lands at the most commercially interesting moment.
• The coordinator. The head of a job queue, a leader election row, a "current open batch" pointer, a sequence table somebody wrote by hand.
• The tenant. In a multi tenant system your largest customer is one row's worth of write traffic on their settings, their subscription, their balance. They are also the customer you can least afford to serve slowly.

Notice what these have in common. A row gets hot because it represents something shared, and things are shared because they are central to the domain. Uniform contention is not a workload, it is an artefact of generated test data. The rows that concentrate writes are the rows the business is made of, and that is not bad luck you can engineer away by choosing better keys.

So the question to ask about any locking decision is never "how many writes per second does this table take". It is "what does the busiest single row take, and what is the shape of a transaction that writes it".

Wide keyspace, low contention: optimistic, and check the row count

For the ordinary case, one customer editing their own profile, or a transfer between two unremarkable wallets, optimistic control is correct and it is not close. Conflicts are rare, so you are paying almost nothing for the protection, and you get lost update detection across HTTP requests thrown in.

update profiles
   set display_name = $2,
       version      = version + 1
 where id = $1
   and version = $3;

The trap is not in the SQL. It is that in most drivers and most ORMs, this statement matching zero rows is a completely successful query. No exception is raised. If nobody inspects the affected row count, the update silently vanishes, and it vanishes precisely when two people were editing the same record, which is precisely the case the version column was added to catch.

const { rowCount } = await tx.query(sql, [id, name, expectedVersion]);
if (rowCount === 0) throw new StaleWriteError(id, expectedVersion);

Three more things that separate a version column that works from one that just looks reassuring:

• A retry has to re-read. Retrying with the same expected version is an infinite loop that produces the same conflict forever. The loop is read, compute, write, and all three are inside it.
• Retries are bounded and the give-up path is a real path. Three attempts then a 409 is fine. Three attempts then a 500 and a stack trace means the hot row will page you.
• The version travels with the data. If the API returns an entity without its version, no client can ever participate in this scheme, and you have built optimistic locking that only defends against your own background jobs.

The hot row: pessimistic, and the number to watch is hold time

On the row that everything touches, waiting beats retrying, because waiting does not redo work and does not starve the expensive transaction.

select balance
  from accounts
 where id = $1
   for update;

The moment you write that, the throughput of everything touching that row becomes 1 divided by the lock hold time. Two milliseconds of hold gives you 500 per second on that account. Twenty milliseconds gives you 50. That is now a hard ceiling on a business metric, and there is no configuration flag that raises it.

Which makes the engineering work obvious. It is not "should we lock", it is "how short can the critical section be".

• Take the lock as late as possible. Validation, pricing, fraud checks, fetching the customer record: all of it belongs before the FOR UPDATE, not after.
• Never do network I/O while holding it. If a provider call sits inside the lock, the provider's p99 becomes your lock hold time and their timeout becomes your outage. That is also how a call that times out after committing turns a single ambiguous payment into a stalled queue of unrelated ones.
• Nothing else goes in there either. No queue publish, no email, no cache invalidation, no sleep somebody added while debugging.

Three details worth knowing about the statement itself, at least in Postgres:

for no key update is the weaker lock you usually want when the row is a foreign key parent. A plain for update conflicts with the for key share lock that foreign key checks take, so locking a parent row blocks inserts of children that have nothing to do with your invariant.

skip locked turns the same primitive into a work queue. select ... limit 1 for update skip locked hands each worker a different row instead of a queue of workers all waiting on the head.

nowait fails immediately instead of waiting, which is the right choice when there is something useful to tell the user quickly.

And set a lock_timeout. The default in Postgres is to wait forever, and forever is longer than your HTTP timeout, your load balancer's patience, and your connection pool's capacity.

When the check must hold at commit, put it in the write

Read the balance, decide it is sufficient, write the new balance. This is the single most common shape in financial code, and split across three statements it is wrong at read committed, which is the default almost everywhere. The read hands you a snapshot that is already historical by the time the write lands, and nothing in the write refers back to what you checked. Repeatable read does rescue you, but it rescues you by raising 40001 under load, which only helps if somebody wrote the retry.

The fix is often smaller than either locking strategy:

update accounts
   set balance = balance - $2
 where id = $1
   and balance >= $2
returning balance;

Zero rows affected means insufficient funds. One row means it worked, and returning hands back the new balance so the caller does not need a second read. There is no version column, no explicit lock, and one round trip.

What makes this safe is a detail people rarely have to think about explicitly. At read committed, when this UPDATE collides with a concurrent writer, Postgres waits for that writer to commit and then re-evaluates the WHERE clause against the new version of the row. The balance >= $2 test is applied to the row as it is at write time, not as it was in your snapshot. The invariant holds at commit, which is the only moment it has to.

Two warnings, because this is the pattern most likely to be cargo culted into a place it does not fit.

At repeatable read or serializable, that same statement does not silently re-check. It raises a serialization failure, 40001, and your code must retry it. Code written and tested at read committed that gets moved to a stricter isolation level starts throwing under load, which is a memorably bad afternoon.

And it only works when the invariant is a predicate over the row being updated. "This account must not go below zero" fits. "The sum of these three accounts must not go below zero", or "no more than five active subscriptions across the household", does not fit at all, and no amount of clever WHERE clause construction will make it fit.

Deadlocks are an ordering bug, not a locking bug

Transaction A locks account 1 then account 2. Transaction B locks account 2 then account 1. Neither can proceed, and the database eventually kills one of them.

The fix has nothing to do with which strategy you chose. Acquire in a deterministic order, every time, everywhere:

select id
  from accounts
 where id = any($1)
 order by id
   for update;

Optimistic schemes are not exempt from this. A transaction that updates two rows with version checks still takes two row locks, in whatever order the statements happen to run, and two such transactions in opposite orders deadlock exactly the same way. The version column has no opinion about acquisition order.

Worth knowing: deadlock detection is a timer, not an interrupt. Postgres waits deadlock_timeout, one second by default, before it even looks for a cycle. So a low rate of deadlocks does not present as errors in a dashboard. It presents as a handful of requests a day that mysteriously took just over a second, which everyone ignores for a year.

And whatever gets retried after a deadlock or a serialization failure has to be safe to redo. If the transaction sent an email, published an event, or called a provider, the retry does it again. Side effects belong outside the retryable unit, behind the contract that makes redoing them harmless.

The third answer: stop having a contended row

Both strategies assume there is a row that everybody updates. That assumption is worth attacking directly, because inserts do not conflict with each other.

Instead of updating a balance, append an entry and derive the balance from the entries. Two writers appending to the same account touch different rows, so there is nothing to wait for and nothing to retry. This is the same append only ledger that auditability wants anyway, which is why it is the default in this domain rather than a contention trick.

It is not free, and the cost lands in a specific place: you have converted a contention problem into an invariant problem. balance >= 0 used to be a predicate over one row. Now it is a property of a set, and an INSERT cannot check it without reading the set, which puts the contention back.

Three ways out, and the right one depends on what the invariant is protecting:

Check it late and compensate. Where a small breach is recoverable, let the insert through, detect the breach asynchronously, and fix it with another entry. This sounds reckless right up until you notice it is how card authorisation has always worked: an available balance, a reservation, and a settlement that can go the other way.

Give the scarce thing rows. Rather than a seats_left counter, have a row per seat, or per licence, or per ticket. Claiming becomes an UPDATE ... WHERE claimed_by IS NULL or a SKIP LOCKED select, and 200 concurrent buyers touch 200 different rows. The last seat still serialises, because the last seat is genuinely contended, but the first 199 stop pretending they are.

Shard the counter. Split one logical account into N rows, have each writer pick a shard, and read the balance as a sum of N. This re-spreads the distribution by force. The catch is that the invariant shards too: a non-negative constraint on each shard is not a non-negative constraint on the account, and the moment a shard runs dry you are back to rebalancing, which is its own small distributed systems problem.

The honest summary of all three is the same sentence: you are not removing the serialisation, you are choosing which invariant you are willing to check late. Do this because you measured contention on a specific row, not because it sounds more scalable. A counter taking five writes a second does not need any of it.

Setting SERIALIZABLE is choosing optimistic for everything

"We run at serializable so we do not have to think about this" is a position I have heard several times, and it is precisely backwards.

Postgres implements serializable as SSI, which tracks read and write dependencies between transactions and aborts one at commit time when it finds a dangerous pattern. That is optimistic control, applied to every transaction in the system, including the ones queuing up on your hottest row. The abort rate there climbs with concurrency exactly as version conflicts do, plus you pay for predicate lock tracking, plus SSI is allowed to abort transactions that would have been fine, and it does.

This is not an argument against serializable. It catches the cross row invariants that no per row scheme can see, and as a default it is a good one. But it does not answer the contention question, it just moves the retry loop into a 40001 handler. Two things follow. If you turn it on without a retry loop, you have shipped a bug that only appears under load. And explicit SELECT ... FOR UPDATE still works at serializable and is still the right answer on the hot row, so the two compose rather than compete.

What each failure mode looks like from the outside

Optimistic control fails quietly and then all at once:

• The retry rate climbs well before latency does, so instrument it as a counter per operation. A debug log line is not instrumentation.
• One endpoint's p99 goes bad while its p50 stays flat, because only the writers that hit the hot key are suffering.
• The bounded retry turns into user visible errors at peak, which is the one time the operation was worth the most.
• The worst case is not an error at all. It is the missing row count check, silently dropping writes, discovered weeks later during a support investigation.

Pessimistic locking fails loudly, but not where you expect:

• Lock wait time is the leading indicator, and pg_stat_activity with wait_event_type = 'Lock' will tell you which relation.
• The blast radius is the connection pool, not the endpoint. Forty threads blocked on one row are holding forty connections, and the endpoints that time out are the ones with nothing whatsoever to do with that row. A hot account can take down your health check.
• lock_timeout converts that into a fast, attributable error. Almost always the better trade.

The general point: a lock strategy under stress presents as an unrelated outage. Which means the alert has to be on the lock metric and the retry metric, not on the endpoint that happens to fall over first.

What this doesn't buy you

• Not safety for anything outside the database. A retried transaction that already charged a card has charged it twice, whichever strategy produced the retry.
• Not cross row invariants. Per row optimistic control has no opinion about a rule that spans rows, and neither does FOR UPDATE on one of them.
• Not protection from an API that omits the version. If clients cannot echo it back, the scheme only defends you against yourself.
• Not a substitute for fixing the model. A single row that a thousand writers need is sometimes a modelling mistake wearing a concurrency costume.
• Not anything at all across systems. A database and a cache, or two services with their own stores, put the invariant somewhere no row lock reaches.

The short version

• Optimistic versus pessimistic is a claim about the distribution of writes over keys, not a performance preference.
• Uniform contention is a property of test data. Real systems concentrate writes on aggregates, scarce resources, coordinators and big tenants, and those are the rows that matter most.
• Optimistic control is not lock free. It holds the lock for the write; pessimistic holds it for the thinking.
• A conflict costs the whole transaction, so retry cost scales with how much work precedes the contended write.
• Retry loops starve the most expensive operation. Lock queues are roughly fair.
• Wide keyspace: use a version column, and check the affected row count, because zero rows updated is not an error in your driver.
• Optimistic is the only option when the read and the write are in different requests. Never hold a transaction open across user think time.
• Hot row: take the lock, then make the critical section short. Throughput is one over hold time, and no I/O goes inside it.
• When the invariant is a predicate over one row, put it in the WHERE clause of the update and check the row count. It holds at commit, which is the only moment that counts.
• Deadlocks are unordered acquisition. Sort by primary key. Optimistic schemes deadlock too.
• Serializable is optimistic control applied to everything. Without a 40001 retry loop it is a load-triggered bug.
• The third answer is to delete the contended row: append entries, give the scarce thing rows, or shard the counter. Each trades contention for a harder invariant.

Two things to look at this week. Take a day of your busiest table's audit or entry rows, group by the entity ID, and sort descending. If the top row has a hundred times the write rate of the median, you have a hot row, and whatever strategy is in place was almost certainly chosen for the median. Then grep for every WHERE version = in the codebase and follow each one to the line that inspects the affected row count. The ones with no such line are not doing optimistic locking. They are doing normal updates with an extra column and a comforting name.