Concurrency
Pessimistic locking: interview questions and how to answer them
Pessimistic locking prevents conflict rather than detecting it: the reader takes an exclusive row lock up front, so no other writer can reach the row until the transaction ends.
Written and reviewed by Sahil Srivastav
What it actually is
`SELECT ... FOR UPDATE` acquires the same exclusive row lock an `UPDATE` would, but at read time and without changing anything. Any other transaction that tries to lock or modify those rows blocks until yours commits or rolls back. The guarantee you buy is that the values you read are the values you will write against — nobody can move them underneath you.
It is worth being exact about what is locked. `FOR UPDATE` locks the rows the query returns, which depends on the plan: a sequential scan with a filter locks the rows that survive the filter, not the whole table, but it may still touch and lock more rows than an index scan would. In MySQL InnoDB, `FOR UPDATE` on a non-unique index also takes gap locks under repeatable read, so it can block inserts into ranges you never selected — a behaviour that surprises people migrating queries from Postgres.
The weaker sibling, `FOR SHARE` (`LOCK IN SHARE MODE` in MySQL), allows other readers to hold the same lock but blocks writers. It is the right tool when you need a row to stay put while you read related data, and the wrong tool for read-modify-write: two transactions can both hold the share lock, both try to upgrade, and deadlock.
Why it matters in production
Because some invariants cannot be expressed as a single statement. Transferring money requires reading two balances, checking one is sufficient, and writing both. Between the check and the write, a concurrent transfer could drain the source account. A single atomic `UPDATE` with a guard handles the simple case, but as soon as the decision depends on more than one row you need the rows held still, and that is what pessimistic locking does.
It matters in the other direction too: the most common self-inflicted database outage is a lock held too long. A transaction that opens, takes `FOR UPDATE`, then makes an HTTP call to a payment provider holds that lock for the provider's latency — and if the provider is slow, every request touching that row queues behind it until the connection pool is empty and the whole service returns errors. The lock did its job perfectly; the scope was the bug.
Interviewers ask about it because the answer reveals whether a candidate thinks about lock *duration* and *ordering*, which are the two properties that decide whether a locking design survives traffic.
How it works
The lock lives for the transaction, not the statement
There is no way to release a row lock early; it ends at commit or rollback. So the design question is not "should I lock?" but "how short can the transaction be?" Everything that does not need the lock — validation, serialisation, remote calls, logging — belongs outside it.
Consistent lock ordering is what prevents deadlock
Two transfers, A→B and B→A, each lock their source first and then wait forever for the other's. Locking in a canonical order — always the lower account id first, regardless of transfer direction — makes the cycle impossible, because every transaction requests locks in the same sequence. This is the single most reliable deadlock fix and the one candidates most often miss.
NOWAIT and SKIP LOCKED turn blocking into a choice
`FOR UPDATE NOWAIT` errors immediately instead of queueing, which is what you want when a fast failure is better than an unbounded wait. `FOR UPDATE SKIP LOCKED` silently omits locked rows, which is what makes a database-backed work queue possible: each worker claims rows nobody else holds, with no coordination and no lost items.
Lock upgrades deadlock, so take the strong lock first
Reading with a shared lock and later upgrading to exclusive means two transactions can both hold shared and both wait to upgrade — a textbook deadlock that appears only under concurrency and is invisible in tests. If you will write, take `FOR UPDATE` on the first read.
Lock timeouts are a safety net, not a design
Setting `lock_timeout` (Postgres) or `innodb_lock_wait_timeout` (MySQL) converts an indefinite hang into a failed request, which is strictly better for the caller and for your connection pool. It does not make the contention go away — it makes it visible and bounded.
Implementing it
Keep the transaction to database work only. If an external call is required, split the flow: commit an intent row, release the lock, make the call, then reconcile in a second transaction. That is the transactional outbox shape, and it exists precisely because locks and remote calls must not overlap.
Order lock acquisition by a stable key and document it next to the code, because the invariant is global and cannot be enforced locally. `ORDER BY id FOR UPDATE` in a single statement gives you the ordering for free when you lock a set.
Set an explicit `lock_timeout` on paths that take row locks, and alert on the resulting errors. Silence here means you are waiting somewhere without knowing it.
For queue-like workloads, use `SELECT ... FOR UPDATE SKIP LOCKED LIMIT n` rather than a status column updated in a separate statement — the latter races and hands the same job to two workers.
-- Money transfer: canonical lock order kills the deadlock cycle
BEGIN;
SET LOCAL lock_timeout = '2s';
SELECT id, balance_minor FROM accounts
WHERE id IN ($1, $2)
ORDER BY id -- every transaction locks in the same order
FOR UPDATE;
UPDATE accounts SET balance_minor = balance_minor - $3 WHERE id = $1;
UPDATE accounts SET balance_minor = balance_minor + $3 WHERE id = $2;
COMMIT;
-- Claiming work without two workers taking the same job
UPDATE jobs SET status = 'RUNNING', claimed_at = now()
WHERE id IN (
SELECT id FROM jobs
WHERE status = 'QUEUED'
ORDER BY created_at
LIMIT 10
FOR UPDATE SKIP LOCKED
)
RETURNING id, payload;Interview questions and how to answer them
Design a money transfer between two accounts. Where do the locks go?
Open a transaction, lock both account rows with a single `SELECT ... WHERE id IN (a, b) ORDER BY id FOR UPDATE`, verify the source balance covers the amount, write both updates, commit. Two things carry the correctness: locking both rows before deciding, so the balance cannot move between check and write; and the `ORDER BY id`, so A→B and B→A request locks in the same sequence and cannot form a cycle. I would also set a lock timeout so a pathological wait fails fast rather than consuming a pool connection.
What is the cost of `SELECT FOR UPDATE` in a web request handler?
The lock lasts until commit, so it lasts as long as the transaction, so it lasts as long as whatever else is in the transaction. If the handler validates input, calls an external API, and renders a response inside the transaction, the row is locked for all of that. Under load, requests for the same row serialise behind the slowest one and the pool drains. In Postgres there is a second, less obvious cost: a long-lived transaction holds back the oldest-xmin horizon, so autovacuum cannot remove dead tuples anywhere in the database and tables start to bloat.
How would you build a job queue on top of Postgres?
`SELECT id FROM jobs WHERE status = 'QUEUED' ORDER BY created_at LIMIT n FOR UPDATE SKIP LOCKED`, then update those rows to RUNNING and return them — ideally as one statement with a subquery so the claim is atomic. `SKIP LOCKED` is the whole trick: each worker walks past rows another worker has locked instead of blocking on them, so N workers claim N disjoint batches with no coordinator. Without it, either all workers queue behind the first, or you use a separate select-then-update and two workers claim the same job.
You are seeing deadlocks between two code paths. What do you do first?
Read the deadlock report, because it names the two statements and the rows involved — `SHOW ENGINE INNODB STATUS` in MySQL, the `deadlock detected` log line with its detail in Postgres. Then find the lock ordering difference, because a deadlock is always a cycle and a cycle always means two paths requested the same resources in different sequences. The fix is to impose one order. Retrying the victim is a mitigation, not a fix, and it degrades badly under load.
Optimistic or pessimistic locking for a seat booking flow where the user picks a seat and then pays?
Neither alone, because the flow spans a user interaction and no lock should live that long. The shape that works is a short pessimistic lock to create a *reservation* row with an expiry — that is machine-scale and bounded — then release, let the user pay, and confirm against the reservation. The reservation is the durable hold; the lock only protects the moment of creating it. Holding `FOR UPDATE` across the payment step would be the wrong answer even though it looks simpler.
Answers that lose the round
- Holding the transaction open across an HTTP call or a message publish, which converts a third party's latency into your outage
- Locking rows in whatever order the business operation happens to name them, which is how the transfer deadlock is born
- Taking `FOR SHARE` on a row you intend to update, then deadlocking on the upgrade
- Saying "SELECT FOR UPDATE locks the table" — it locks rows, though the plan and the isolation level decide which rows and whether gaps are locked too
- Relying on an application-level mutex or a distributed lock to protect a database invariant while other writers reach the same row directly
- Retrying a deadlock victim without fixing the ordering, so the system spends its capacity on retries under load
- Forgetting that in MySQL InnoDB under repeatable read, a range `FOR UPDATE` takes gap locks and blocks inserts that no existing row matched
Practise pessimistic locking in a real repository
Gronex ships this as a runnable repository: a transfer service that deadlocks when transfers run in opposing directions. The tests drive concurrent A→B and B→A transfers and fail on deadlock, so a retry loop does not pass — only a canonical lock order does.
FAQ
Does `FOR UPDATE` block readers?
In Postgres and in MySQL InnoDB, plain `SELECT` readers are not blocked — MVCC lets them read the prior committed version. What blocks is another `FOR UPDATE`, a `FOR SHARE`, an `UPDATE`, or a `DELETE` on the same rows. That is why "locking blocks all reads" is a wrong answer on any MVCC engine.
Is a distributed lock a substitute?
Only if every writer goes through it, which is rarely true — a migration script, an admin console, or a second service reaching the same table bypasses it entirely. A distributed lock is for coordinating work outside the database, such as ensuring one instance runs a scheduled job. Database invariants belong to the database.
What about `SELECT FOR UPDATE OF table`?
In a join, `FOR UPDATE` locks rows from every table involved by default, which is usually more than you meant. `FOR UPDATE OF orders` restricts the lock to that table's rows. It is a useful precision tool when you join a reference table you have no intention of writing.