ConcurrencyDATABASE-SPECIFICGENERALSCALE-SPECIFIC

Pessimistic Locking

SELECT ... FOR UPDATE serialises access to a row — and holds a lock, a connection and a transaction while you do it.

The requirement, the obvious build, and why it breaks

Every lesson starts where the work starts: someone asked for something, and the first implementation that comes to mind has a problem.

The question

When is it right to stop other requests from touching a row rather than detecting that they did?

The requirement

Transferring money between accounts must read both balances, verify sufficient funds, and write both — with nothing else touching either account in between.

The obvious build

Wrap the reads and writes in a transaction. Transactions are atomic, so the sequence is protected.

Why it breaks

At read-committed, a plain SELECT inside a transaction takes no lock. A second transfer reads the same balance, and both commit. Atomicity is not isolation (Isolation Levels).

How it breaks in production

At read-committed, a plain SELECT inside a transaction takes no lock. A second transfer reads the same balance, and both commit. Atomicity is not isolation (Isolation Levels).
Raising the isolation level to serializable helps and changes the failure mode: transactions now abort with serialization errors that the application must catch and retry, and code that does not catch them fails outright (Isolation Levels: The Mechanism Behind Each).
Adding FOR UPDATE fixes the correctness and introduces a new class of problem: the lock is held until commit, so anything slow inside the transaction — an external API call, a large computation — blocks every other transfer on that account (External Calls Inside a Transaction).
Locking two accounts in whatever order they appear in the request produces deadlocks the moment two transfers run in opposite directions (Deadlocks in Application Code).
Under load, locked rows create a queue that consumes a connection per waiter, and the pool empties before the lock does (Connection Pools).

Requirement→API Contract→Application Logic→Data Access→External Deps→Concurrency→Failure→Security→Observability→Deployment→Scale

What is actually happening

SELECT ... FOR UPDATE takes an exclusive row lock at read time. Any other transaction attempting to lock the same row waits until the first commits or rolls back.
The lock is held for the remainder of the transaction, not for the statement. The transaction boundary is therefore the lock duration, which makes transaction scope the central design decision (Where the Transaction Boundary Goes).
Waiting consumes a database connection on the waiter's side and a backend process on the server's. A hundred requests queued on one row is a hundred connections held (Connection Pool Exhaustion).
Deadlock happens when two transactions hold locks the other wants. Databases detect it and abort one with an error; your application must expect that error and retry (Deadlock).
Consistent lock ordering prevents deadlock: if every transaction locks accounts in ascending id order, a cycle cannot form (Lock Ordering).
FOR UPDATE SKIP LOCKED changes the semantics from "wait" to "take a different row", which is what makes a database table usable as a work queue with many workers (Job Queues).
FOR UPDATE NOWAIT fails immediately instead of waiting, converting a latency problem into an error you can handle.

The lock lasts as long as the transaction

The single most consequential fact about row locks is that they are released at commit or rollback, not at the end of the statement. Whatever else the transaction does — call an API, serialize a large response, wait on a slow query — happens with the lock held.

This is why transaction scope is the real subject of this lesson. A correct locking implementation with a badly scoped transaction is a throughput disaster and, under load, an availability one: each waiter holds a connection, the pool has a fixed size, and requests unrelated to the locked row start failing because there are no connections left (Connection Pool Exhaustion).

The discipline is to do everything slow outside, and let the transaction contain only the read, the decision and the writes. Fetch exchange rates before it. Compute before it. Send the notification after it commits.

Transferring money between two accounts

Everything inside the lock

await db.tx(async (t) => {
  const from = await t.one(
    'SELECT * FROM accounts WHERE id = $1 FOR UPDATE', [fromId])
  const to = await t.one(
    'SELECT * FROM accounts WHERE id = $1 FOR UPDATE', [toId])

  const rate = await fx.getRate(from.currency, to.currency)  // network call
  await notify(from.userId, 'transfer started')              // network call

  await t.none('UPDATE accounts SET balance = $2 WHERE id = $1',
    [fromId, from.balance - amount])
  await t.none('UPDATE accounts SET balance = $2 WHERE id = $1',
    [toId, to.balance + amount * rate])
})

Slow work outside, ordered locks inside

const rate = await fx.getRate(fromCurrency, toCurrency)   // before the tx

const [first, second] = [fromId, toId].sort()             // consistent order

await db.tx(async (t) => {
  await t.none('SET LOCAL lock_timeout = \'2s\'')          // fail, do not hang
  const rows = await t.many(
    'SELECT id, balance FROM accounts WHERE id IN ($1,$2) ORDER BY id FOR UPDATE',
    [first, second])

  const from = rows.find(r => r.id === fromId)!
  if (from.balance < amount) throw new InsufficientFunds()

  await t.none('UPDATE accounts SET balance = balance - $2 WHERE id = $1',
    [fromId, amount])
  await t.none('UPDATE accounts SET balance = balance + $2 WHERE id = $1',
    [toId, amount * rate])
})

await notify(fromUserId, 'transfer complete')             // after commit

On the left, two network calls happen with both account rows locked, so every other transfer touching either account waits for a third party, and the lock order follows request order so opposite-direction transfers deadlock. On the right the transaction contains only database work, the ids are sorted so a cycle cannot form, a lock timeout bounds the wait, and the balance arithmetic is done by the database rather than from values read into application memory.

Deadlock is an ordering bug with a database-generated error

Deadlock is not a mysterious database phenomenon. It is the entirely predictable result of two transactions acquiring the same locks in different orders. Transaction one holds A and wants B; transaction two holds B and wants A; neither can proceed and the database aborts one.

The fix is equally mechanical: define a total order over lockable resources and always acquire in that order. Primary key ascending is the usual choice because it is total, stable and obvious in code. Sorting the ids before locking looks superfluous in a function that usually receives them in order, and it is the entire prevention (Lock Ordering).

The second half is accepting that deadlocks will still occur — from a code path you have not audited, from a background job, from a migration — and that the database's abort is a normal, retryable outcome. Catch the specific error, back off with jitter, retry a bounded number of times.

UserLLMAgentToolDataDecisionHumanGuardrail

Locking failures and their responses

Trigger	Symptom	Cause	Response
Two transfers in opposite directions	Intermittent deadlock errors under load	Lock order follows request order	Sort ids before locking; retry the aborted transaction with jitter (Lock Ordering)
HTTP call inside the locked transaction	Lock held for seconds; pool waiters accumulate	Transaction scope includes external latency	Move the call outside the transaction (External Calls Inside a Transaction)
`FOR UPDATE` on an unindexed column (InnoDB)	Unrelated rows blocked	Locks follow the index scan, not the result set	Index the predicate; verify with the execution plan (Should I Add an Index?)
No lock timeout	Requests hang indefinitely; pool exhausted	Unbounded wait on a contended row	`lock_timeout` / `innodb_lock_wait_timeout`; fail fast and surface 409 or 503
Queue workers all `FOR UPDATE` the oldest pending row	Workers serialise; adding workers does not help	Every worker waits on the same row	`FOR UPDATE SKIP LOCKED` (Job Queues)
Redis lock with a 5 s TTL around 8 s of work	Two holders, corrupted state, no error	TTL shorter than the critical section	Extend via heartbeat, or use a row lock tied to the transaction (A Mutex on Server A Does Nothing About Server B)
Deadlock retried immediately	Repeated deadlocks between the same pair	Both retry in lockstep	Exponential backoff with jitter (Backoff and Jitter)

Wait, skip, or fail: three lock modes and three problems

DATABASE-SPECIFICSKIP LOCKED and NOWAIT are available in Postgres 9.5+ and MySQL 8.0+; earlier MySQL has neither, which is why older job-queue implementations on MySQL use a claim-by-update pattern (UPDATE ... SET worker = ? WHERE state = 'pending' LIMIT 1) instead. FOR SHARE is LOCK IN SHARE MODE in older MySQL syntax.

Plain FOR UPDATE waits. That is the right behaviour when you specifically need *this* row and the wait is expected to be brief. It is the wrong behaviour for a work queue, where any pending row will do, and for a user-facing request where waiting behind an unknown queue is worse than an error.

SKIP LOCKED changes the question from "give me this row when it is free" to "give me a row nobody else has". That single modifier is what makes a database table a viable job queue: N workers each take different rows and none of them wait (Job Queues).

NOWAIT converts contention into an immediate error, which is the right choice for an interactive request where a bounded failure is preferable to an unbounded wait — the caller can be told to retry, and no connection is held while it decides.

Which locking mode does this call site need?

If the row is already locked, what is the least bad thing that can happen?

`FOR UPDATE` (wait)

when You need this specific row and contention is brief and bounded.

cost A held connection per waiter; unbounded latency without a lock timeout (Connection Pool Exhaustion).

`FOR UPDATE SKIP LOCKED`

when Any eligible row will do: job queues, task claiming, batch partitioning.

cost No ordering guarantee across workers; a row can be skipped repeatedly if it is always locked.

`FOR UPDATE NOWAIT`

when Interactive requests where failing fast beats waiting.

cost The caller must handle the error and decide whether to retry.

`FOR SHARE`

when You must prevent the row changing while you read it, without excluding other readers.

cost Shared locks can escalate into deadlock when two holders both try to upgrade to exclusive.

No lock — one atomic statement

when The decision fits in a WHERE clause.

cost Logic constrained to SQL; usually the best available option (Atomic Operations).

No lock — optimistic version

when Conflicts are rare and the work is cheap to redo.

cost Retries, and a conflict surfaced to the caller (Optimistic Concurrency).

How to build it

Most important first.

Reach for a lock when conflicts are frequent enough that optimistic retries would dominate, or when the work between read and write is too expensive to redo (Optimistic vs Pessimistic).
Keep the locked transaction as short as physically possible. Do all the slow work — external calls, computation, serialization — before the transaction opens (External Calls Inside a Transaction).
Lock in a consistent, globally-defined order, usually by primary key ascending. Sort the ids before locking, always, even when it looks unnecessary (Lock Ordering).
Set a lock timeout so a waiter fails fast rather than holding a connection indefinitely. An unbounded wait is an outage waiting for a trigger.
Catch deadlock and lock-timeout errors explicitly and retry with jitter, bounded. They are expected outcomes, not bugs (Deadlocks in Application Code).
Use SKIP LOCKED for queue-like tables where any available row will do; it turns contention into parallelism instead of into waiting (Job Queues).
Lock the narrowest thing that works: one row, not a range, and never a whole table.
Prefer a single atomic statement where the logic allows it — it takes the same row lock for a far shorter time (Atomic Operations).

What can go wrong

Failure modes

An external API call inside the locked transaction, so a third party's latency becomes your lock duration and your pool's occupancy.
Inconsistent lock ordering across code paths, producing deadlocks that appear only when two specific endpoints run concurrently.
Lock waits with no timeout, so a single long transaction stalls every request touching that row until someone notices.
Locking a row and then discovering the work requires locking another, escalating a single-row lock into an implicit ordering dependency.
Long-running read transactions holding locks unnecessarily because the transaction was opened earlier than needed.
SELECT ... FOR UPDATE on a query without an index, which in MySQL locks far more rows than the ones returned, because locking follows the index scan and not the result set.
Retry-on-deadlock without jitter, so the two transactions collide again immediately (Backoff and Jitter).
A distributed lock in Redis used instead of a row lock, with a TTL shorter than the work, so two holders exist and the mechanism is worse than none (A Mutex on Server A Does Nothing About Server B).

What can race

Deadlock: two transactions each holding a lock the other needs — a race resolved by the database aborting one (Deadlock).
Lock acquisition order varying between code paths, so the cycle only forms for particular request pairs (Lock Ordering).
A distributed lock expiring mid-operation, producing two simultaneous holders with no error on either side.
A lock released at commit while a subsequent statement outside the transaction still assumes exclusivity (Lock Scope: What You Hold It Across).
Queue workers contending on the same pending row, which SKIP LOCKED converts from a race into a partition.

Security

Lock contention is a denial-of-service surface: an attacker who can trigger a long-held lock on a hot row stalls every legitimate request touching it. Lock timeouts bound the damage.
Do not let a caller influence lock scope — a filter parameter that widens a FOR UPDATE query from one row to a range is an amplification primitive (Query Parameters).
Locks are not authorization. A locked row is still readable by anyone permitted to read it; enforce permissions independently (Object-Level Authorization).

Misreads

"A transaction locks the rows it reads." Only if you ask. A plain SELECT at read-committed takes no row lock in either Postgres or InnoDB (Isolation Levels).
"Serializable isolation means I do not need FOR UPDATE." It means conflicts abort instead of corrupting, and your code must catch and retry those aborts — which many codebases do not.
"Locks are slow." Taking a lock is cheap. Holding one across a network call is what is expensive, and that is a scope decision, not a lock cost.
"Optimistic is modern, pessimistic is legacy." They solve the same problem at different conflict rates. Under heavy contention, pessimistic wins clearly (Optimistic vs Pessimistic).
"Redis locks are the same as row locks." A row lock is tied to a transaction that commits or aborts atomically with your write. A Redis lock has a TTL, no relationship to your transaction, and can expire while you still believe you hold it.

Operating it

How you see it in production

Lock wait time and the count of waiting sessions. In Postgres, pg_locks joined to pg_stat_activity shows who waits on whom; InnoDB exposes the equivalent through its lock tables (Hold Time, Wait Time, and the Ratio Between Them).
Deadlock count as a metric with an alert on any sustained rate. Databases log deadlocks with both statements, which usually identifies the ordering bug immediately.
Transaction duration distribution, and specifically time spent idle in transaction — that state means a lock is held while nothing is happening, which is almost always an external call.
Connection pool wait time, which rises before lock contention becomes visible as errors (Connection Pool Saturation: Waiting in Front of an Idle Database).

What changes at 10x and 100x

Locking serialises access to a row, so throughput on that row is bounded by transaction duration regardless of how many instances you run. That is the ceiling, and adding capacity does not raise it (Little's Law as Working Intuition).
At 10x, a lock held for tens of milliseconds becomes a visible queue; at 100x it is the system's bottleneck and the fix is a data model change, not tuning.
Waiters consume connections, so lock contention converts into pool exhaustion, which converts into failures on endpoints that have nothing to do with the locked row (Cascading Failure).
SKIP LOCKED scales with workers because it removes waiting entirely; plain FOR UPDATE on a queue table does not.

What this costs

Locking gives you a straightforward mental model — inside the lock, you are alone — and pays for it with throughput on the locked row and a held connection per waiter.
It avoids the wasted work of optimistic retries and creates deadlock risk, which optimistic concurrency does not have.
Short transactions reduce contention and force restructuring: fetch first, compute first, lock last.
Lock timeouts turn indefinite waits into errors that must be handled — better behaviour, more code.

Where this applies

Backend advice is context-sensitive. These labels say what each claim is specific to, and where a different stack or scale would differ.

DATABASE-SPECIFICPostgres locks exactly the rows returned by the query and detects deadlocks by waiting deadlock_timeout (1 s by default) before running a cycle check, so a deadlock costs at least that delay. InnoDB locks index records and, under repeatable-read, the gaps between them, so a FOR UPDATE on a non-indexed column can lock far more rows than it returns — occasionally the whole table — and it detects deadlocks immediately from a wait-for graph. The same statement therefore has different blocking scope and different deadlock latency on the two engines.
GENERALThe principle — exclusive access held for the transaction's duration, ordered acquisition to prevent cycles — holds for any locking system.
SCALE-SPECIFICBelow meaningful contention, pessimistic and optimistic are indistinguishable in behaviour and the choice is stylistic. Above it they diverge sharply: optimistic burns CPU on retries, pessimistic builds a queue. The crossover is a property of your conflict rate and transaction duration, not a fixed request rate.

Where the depth lives

This domain teaches the application-side mechanism and hands the rest off.

Databaselocks-and-deadlocks lock-manager-internals deadlock-detection-internals isolation-levels mvcc concurrency-control-internals

Performancelock-contention connection-pool-saturation littles-law

Concurrencypessimistic-concurrency optimistic-vs-pessimistic mutexes deadlock deadlock-prevention lock-ordering lock-scope local-lock-not-distributed lock-wait-metrics read-write-locks

Domains that do not exist yet

— Distributed Systems — why a lock in an external store is not equivalent to a lock inside the transaction that owns the write.