By Imran M · 26/09/2026 · 10 min read

Pessimistic Locking: Row Locks, Deadlocks, and Throughput Trade-offs

Pessimistic Locking holds a row until commit. See when row locks keep writes correct, and when they cause deadlocks, queues, and much lower throughput.

Executive Summary: When two writers can both read a balance and both save it, pessimistic locking holds the row so the second writer waits instead of racing — correct, but every waiting writer is capacity you don’t have during that hold. This guide covers when row locks are the right call over a retry-based approach, the lock-ordering discipline that prevents deadlocks, and how a held lock across a slow operation turns into a connection pool exhaustion incident.

Pessimistic Locking matters when two writers can both read a balance and then both save it. You lock the row first, so the second writer waits. That wait keeps the number correct. It also limits how much work the database can finish.

What Pessimistic Locking Is

Pessimistic locking means you block other writers before you change a row. You assume a clash is likely. Therefore you take the lock early. The database holds that lock until you commit or roll back.

In PostgreSQL, SELECT FOR UPDATE is the usual tool. In MySQL with InnoDB, the same clause locks the index record. After the lock is held, other updates on that row wait. Plain reads can still see the old row, because the engine keeps a snapshot.

Use this pattern when a wrong write is costly. A wallet balance, a seat, or a stock count fits well. If two orders can sell the last item, you want one of them to wait. A version check fails more often when many workers hit the same row.

Skip the row lock when clashes are rare. A profile edit is a good example. Two edits at the same time are uncommon.

A version column is cheaper there. You can read about optimistic locking when you want that path.

Why It Fails in Production

The lock can be correct and still harmful when the row is hot. Every worker lines up on one record. Wait time grows with the line.

Timeouts fire, and clients retry. Retries take the same lock, so the line gets longer.

I have seen this on a single counter row that every checkout touched. The statement looked cheap in a test with one user. Under load, the slow tail tracked the lock wait, not the disk. The fix was to split the counter, not to tune the lock.

Long work units make the failure worse. If you lock the row and then call a payment API, you hold the lock for the whole network trip. Other writers stall for seconds.

The pool fills with sessions that still own locks. New requests cannot get a connection, although the CPU is idle.

Lock scope also surprises people. A locked read without a tight index can lock more rows than you think. A missing filter can lock a wide range.

In addition, gap locks in MySQL can block inserts near that key. You meant to guard one row. You blocked a slice of the index.

How the Lock Path Works

A request starts a work unit. Then it reads the row with a lock clause. If no one else holds a conflicting lock, the read returns at once.

If someone does, your session waits. The wait ends when the holder commits, rolls back, or hits a lock timeout.

You then change the row and commit. The commit frees every lock from that work unit. Because the lock life matches the work unit, you should keep it short.

Read what you need, write what you need, and commit. Do not call other services while the lock is open.

The database also takes weaker locks for the plan. Next, a foreign key can lock the parent row. A unique index can lock a gap so two inserts cannot create the same key. These extra locks are why a simple update can stall a statement that looks unrelated.

Lock modes and timeouts

Set a lock timeout so a waiter fails fast. In PostgreSQL, lock_timeout aborts the statement when the wait is too long. deadlock_timeout is different.

It is how long the engine waits before it checks for a cycle. The PostgreSQL lock timeout settings spell out both knobs.

Choose the weakest mode that is still safe. FOR NO KEY UPDATE lets another work unit take a foreign key lock. FOR UPDATE blocks that.

If you only change non-key columns, the weaker mode cuts cycles. If you change the primary key, you need the stronger mode.

The PostgreSQL explicit locking guide lists FOR UPDATE, FOR SHARE, and the no-key forms. MySQL modes are documented in the InnoDB locking reference. Read the mode list before you copy a snippet from a blog.

Trade-offs You Should Weigh

A pessimistic lock buys a strict order. The first work unit to lock the row wins. Everyone else waits in line.

You get a clear story for support. You also get a queue you must size.

A version check lets both writers proceed. The second write fails the check and retries. That is faster when clashes are rare. It is worse when the same row is hot, because retries storm the database.

A single worker removes the race by design. Only one process touches the row. Throughput is then the speed of that one worker.

You trade latency and scale for a simple model. Race conditions in backend systems still exist if a second path skips the worker.

Approach	When it fits	Main cost	Failure mode
Pessimistic lock.	Clashes are common.	Wait time on the hot row.	Cycles and a full pool.
Version check.	Clashes are rare.	Retry work after a clash.	Lost updates if you skip the check.
Single writer queue.	Order must be total.	Speed capped by one worker.	Queue lag under a burst.

Compare this with how you handle deadlocks in databases and distributed systems. A lock that is safe on one node can still cycle when two services lock in opposite order.

Pitfalls and Failure Modes

First, lock order must be stable. If work unit A locks row 1 then row 2, work unit B must do the same. If B locks row 2 then row 1, you can deadlock.

The database will abort one side. Your code must retry the whole unit, not only the last statement.

Second, do not lock rows from a random map walk. Sort the ids before you lock them. I have seen a cycle that only showed up when a batch had more than two accounts.

The sort removed it. Still, the retry path has to stay in place.

Third, retries need a limit and a small random delay. A tight retry on deadlock will stampede the same rows. After a few tries, fail the request.

Let the client decide. Also log the lock waits so you can see which statement blocked.

Fourth, a lock on a missing row does not always block the insert you fear. In PostgreSQL, two inserts can both pass a read and then one hits a unique error. Catch that error and treat it as a clash. The row lock was never the whole story.

Fifth, a replica does not hold your lock. A check on a replica, then a lock on the primary, can use stale data. When you need a lock, run the locked read on the primary. If you must read extra rows, lock them in the same sorted pass.

A Practical Lock Example

The pattern below moves money between two accounts. It locks the lower id first, so every transfer takes locks in the same order. It also refuses to hold the lock while it talks to any other service. Run this with a lock timeout set on the session.

BEGIN;
SET LOCAL lock_timeout = '500ms';
SELECT account_id, balance
FROM accounts
WHERE account_id IN (10, 42)
ORDER BY account_id
FOR UPDATE;

UPDATE accounts
SET balance = balance - 100
WHERE account_id = 10 AND balance >= 100;

UPDATE accounts
SET balance = balance + 100
WHERE account_id = 42;
COMMIT;

If the first update changes zero rows, roll back. The balance was too low. If the statement hits a lock timeout, roll back and return a busy error.

Do not keep the work unit open while you wait to retry. A common mistake I have seen is to catch the timeout and then sleep inside the same open unit.

What to check in code review

When you review a lock change, walk the statements in order. Confirm the filter uses an index. Confirm the ids are sorted.

Confirm no HTTP call sits between the lock and the commit. If any of those fail, the review should block the change.

Start the work unit on the primary.
Lock rows in id order with a timeout.
Apply the balance checks and writes.
Commit, or roll back on a zero-row update.
Retry the whole unit only outside the lock.

Performance, Scale, and Cost

Lock waits show up as time spent blocked, not as CPU. Watch lock wait charts and the count of sessions stuck on a lock. In an illustrative production range, a hot row can hold waits of tens to hundreds of milliseconds once a few dozen writers pile up.

Your numbers will differ. Measure them.

Speed on a single locked row is about one divided by the time you hold the lock. If you hold it for 5 ms, you get on the order of a couple hundred commits per second on that row. If you hold it for 200 ms because you called another service, you drop to a handful. Therefore the cheapest scale move is to shorten the critical section.

When one row is not enough, split the counter. Keep a small set of rows and sum them for the true total. Each writer picks a shard, often by hashing the request id.

Clashes drop. The read of the total gets a bit more work. That trade is usually worth it for a hot balance.

Cost shows up in the database bill when waits keep connections open. You pay for hosts that sit blocked. You also pay in user-facing errors when the pool is full.

A lock timeout that fails fast is cheaper than a pile of idle sessions. Still, a timeout is a product choice. The user must see a clear retry, not a partial charge.

If you change code that takes these locks, ship it with rolling deployments so old and new workers agree on lock order. A mixed fleet that locks in two orders will cycle until the rollout ends. Pause the rollout if deadlock errors spike.

Key Takeaways

Lock the row before you write when a clash would corrupt state.
Keep the work unit short so the lock does not cover network calls.
Always take multi-row locks in a sorted order.
Set a lock timeout and retry with a limit and jitter.
Use a weaker lock mode when you do not change the key.
Split hot rows when wait time dominates your latency.
Prefer a version check when clashes are rare.

FAQ

When should you pick pessimistic locking?

Pick it when a lost update is costly and clashes are common. A balance, a coupon count, or a seat map fits. If clashes are rare, a version column is simpler and faster. Also measure the wait before you spread the pattern to every table.

Does a row lock stop every read?

No. With snapshot reads, a plain read can still see the prior version. The lock stops other writers, and it stops reads that also ask for a lock.

Check your read mode before you assume readers block. When you need a stable read, take the lock on the primary.

What happens when two locks deadlock?

The database aborts one work unit and returns an error. You should retry the whole unit of work. If you retry without a delay, you can livelock and burn CPU.

Cap the attempts. Then return a busy error so the client can back off.

Can the app lock in memory instead?

A process mutex only works inside one process. A second host will not see it. Use the database lock, or a lock service that all hosts share.

For a single row, the database is the simpler place. If you add an in-memory lock as well, you can deadlock with the database.

Start by listing every statement that reads a row and then writes it back. Add a pessimistic lock on the hot ones, with a sorted lock order and a short timeout. Then measure wait time before you tune anything else. If the wait stays high, split the hot row or move rare clashes to a version check.

Last updated on 19 September 2026.