FOR UPDATE vs FOR UPDATE NOWAIT vs FOR UPDATE SKIP LOCKED — which to use when?

Q: FOR UPDATE vs FOR UPDATE NOWAIT vs FOR UPDATE SKIP LOCKED — which to use when?

**Target Audience:** Developers who know basic transactions and want to understand locking reads from zero; candidates who need a crisp "which one when" answer. **Difficulty:** Medium **Engines:** PostgreSQL 16.15 (two real `psql` sessions for every locking behaviour, the verifier for the examples), SQLite via sql.js 1.10.2 for the runnable query and to confirm it has no `FOR UPDATE`. MySQL and SQL Server facts are cited, not run. > **How to read this doc:** Every idea is explained in plain language first, then tagged with `📌 Interview term:` so you also learn the exact words an interviewer expects. Every result, plan and error message below was produced by actually running it; the **Verified** section quotes the example output in full. ## 1. Why This Even Matters — A Story First Three shoppers walk up to a market stall where one customer is already holding the last melon and thinking. - The first shopper **waits** patiently behind them. If the customer puts the melon back, she ta

The problem

Question presented to candidate: "You need to lock a row before you change it. PostgreSQL gives you FOR UPDATE, FOR UPDATE NOWAIT and FOR UPDATE SKIP LOCKED. What does each one do when the row is already locked, and which would you pick for a seat-booking page, a background job queue and a money transfer?"

What a strong answer should cover:

FOR UPDATE locks the selected rows until the transaction ends; a second locker waits for the first to commit or roll back
NOWAIT turns that wait into an immediate error, SQLSTATE 55P03; SKIP LOCKED silently leaves locked rows out of the result
Plain SELECTs are never blocked by any of them (MVCC); only other locking reads and writes are
Use cases: plain FOR UPDATE when every caller must see and process this exact row (transfers, with a consistent lock order); NOWAIT for "someone else is editing, try later" in interactive flows; SKIP LOCKED for queues and pools where any free row will do
Details: lock_timeout as the middle ground between waiting forever and failing at once; after waiting, READ COMMITTED re-checks the WHERE clause on the new row version; FOR NO KEY UPDATE is weaker and does not block foreign-key inserts
Engines: PostgreSQL has had SKIP LOCKED since 9.5, MySQL 8.0 has NOWAIT and SKIP LOCKED, SQL Server uses UPDLOCK, READPAST hints, SQLite has no row locks at all

Code / implementation expected: Yes — the three clauses on the same locked row, with what each one returns.

Schema used by the examples:

locking
skip-locked
nowait

Try it yourself

Sketch your approach before you read the solution. That is what the interview tests.

Stuck? Get a hint.

A short nudge toward the approach. It will not give the answer away.

Solution

Target Audience: Developers who know basic transactions and want to understand locking reads from zero; candidates who need a crisp "which one when" answer. Difficulty: Medium Engines: PostgreSQL 16.15 (two real psql sessions for every locking behaviour, the verifier for the examples), SQLite via sql.js 1.10.2 for the runnable query and to confirm it has no FOR UPDATE. MySQL and SQL Server facts are cited, not run.

How to read this doc: Every idea is explained in plain language first, then tagged with 📌 Interview term: so you also learn the exact words an interviewer expects. Every result, plan and error message below was produced by actually running it; the Verified section quotes the example output in full.

1. Why This Even Matters — A Story First

Three shoppers walk up to a market stall where one customer is already holding the last melon and thinkin

The solution is hidden so you can try first

13 min read · 10 sections

Run the code

Edit the query and run it against the tables for this question.

SQLThe rows you would lock: free seats on flight CP101

PostgreSQL: take two free seats with FOR UPDATE SKIP LOCKED (nobody else holds a lock here)

SELECT id, seat_no, status
FROM seats
WHERE flight = 'CP101' AND status = 'free'
ORDER BY seat_no
LIMIT 2
FOR UPDATE SKIP LOCKED;

PostgreSQL: the four row-lock strengths, seen with pgrowlocks inside one transaction

CREATE EXTENSION IF NOT EXISTS pgrowlocks;
BEGIN;
DO $$ BEGIN
  PERFORM 1 FROM seats WHERE id IN (1, 2) FOR UPDATE;
  PERFORM 1 FROM seats WHERE id = 4 FOR NO KEY UPDATE;
  PERFORM 1 FROM seats WHERE id = 5 FOR SHARE;
END $$;
SELECT s.id, s.seat_no, r.modes
FROM pgrowlocks('seats') AS r
JOIN seats AS s ON s.ctid = r.locked_row
ORDER BY s.id;
ROLLBACK;

Discussion

Other approaches, follow-ups and how it went in real interviews.

Sign in to join the discussion.

No comments yet. Share how you would answer it.

The solution is hidden so you can try first

Keep your momentum going.