SQL Row Locking: SELECT FOR UPDATE, SKIP LOCKED, and NOWAIT
How FOR UPDATE locks rows until commit, why the plan decides the lock scope, and the SKIP LOCKED claim-queue pattern that scales to many workers.

Quick Summary
SELECT ... FOR UPDATE takes a row-level exclusive lock so that two transactions cannot read-modify-write the same row and silently lose one update. The statement itself is simple; everything difficult about it is the lock's scope and lifetime. The lock is held until the transaction ends — not until the statement ends — so a FOR UPDATE inside a long transaction blocks every other writer and every FOR UPDATE reader for as long as the application keeps the transaction open.
The lock scope follows the execution plan, not the WHERE clause as written. WHERE status = 'queued' FOR UPDATE locks exactly the rows the query returns; if that predicate is served by a sequential scan with a LIMIT 10, PostgreSQL evaluates rows until it has ten and locks those, but under load concurrent workers re-scan the same pages and block on the same ten rows. Adding SKIP LOCKED turns the statement into a work-queue primitive: each worker takes the next batch nobody else holds, and no worker ever waits for a peer.
The three options you will use are NOWAIT (fail immediately instead of waiting), SKIP LOCKED (take only unlocked rows), and a bare FOR UPDATE (wait, up to lock_timeout). Treat FOR UPDATE as pessimistic and deliberate: short transactions, indexes that bound the row set, a consistent lock ordering across code paths, and a retry path for 40P01 deadlocks.
Basic syntax and query signatures
-- Wait for the lock (default)
SELECT * FROM jobs WHERE id = $1 FOR UPDATE;
-- Fail fast: 55P03 lock_not_available instead of waiting
SELECT * FROM jobs WHERE id = $1 FOR UPDATE NOWAIT;
-- Batch claim: only rows nobody else has locked
SELECT id FROM jobs
WHERE status = 'queued'
ORDER BY created_at
LIMIT 10
FOR UPDATE SKIP LOCKED;
-- Weaker modes
SELECT * FROM accounts WHERE id = $1 FOR NO KEY UPDATE; -- allows FK-referencing inserts
SELECT * FROM accounts WHERE id = $1 FOR SHARE; -- many readers, no writerLock lifetime is the transaction. The shortest correct shape is a single statement that claims and returns, or a RETURNING update that does not need a separate read.
BEGIN;
UPDATE jobs
SET status = 'running', started_at = now()
WHERE id = (SELECT id FROM jobs
WHERE status = 'queued'
ORDER BY created_at
LIMIT 1
FOR UPDATE SKIP LOCKED)
RETURNING id, payload;
COMMIT;MySQL and SQL Server express the same idea with different syntax.
-- MySQL 8.0
SELECT id FROM jobs
WHERE status = 'queued' ORDER BY created_at LIMIT 10
FOR UPDATE SKIP LOCKED;
-- SQL Server: row lock hint plus READPAST to skip locked rows
SELECT TOP (10) id
FROM jobs WITH (UPDLOCK, ROWLOCK, READPAST)
WHERE status = 'queued'
ORDER BY created_at;Key comparison table
| Clause | Behaviour on a locked row | Raises | Use when |
|---|---|---|---|
FOR UPDATE | Waits (bounded by lock_timeout) | 55P03 on timeout, 40P01 on deadlock | Critical section that must serialise |
FOR UPDATE NOWAIT | Fails immediately | 55P03 | Contended single row, fail-fast API |
FOR UPDATE SKIP LOCKED | Row is invisible to this query | None | Queue workers, batch claim |
FOR NO KEY UPDATE | Waits, weaker than FOR UPDATE | 55P03 | Updating rows referenced by foreign keys |
FOR SHARE | Other readers proceed, writers wait | 55P03 | Read-modify-write with no row change |
FOR KEY SHARE | Only key-changing writers wait | 55P03 | Referential integrity only |
| Engine | SKIP LOCKED | NOWAIT | Row lock hints | Lock timeout |
|---|---|---|---|---|
| PostgreSQL | 9.5+ | Yes | None needed | SET lock_timeout |
| MySQL / InnoDB | 8.0+ | Yes | None needed | innodb_lock_wait_timeout |
| SQL Server | READPAST hint | NOWAIT hint | ROWLOCK/UPDLOCK | LOCK_TIMEOUT |
| Oracle | 11g+ | Yes | None | ddl_lock_timeout, profile |
| SQLite | No | No | No | Busy timeout |
| Symptom in production | Mechanism | Correct remedy |
|---|---|---|
| Workers block each other | No SKIP LOCKED | Add it, or serialise at a different level |
| Whole table appears locked | Lock scope widened by a sequential scan | Index the queue predicate |
| Idle-in-transaction holds locks | Application waits on I/O inside the transaction | Move I/O outside, bound the transaction |
Random 40P01 under load | Two code paths lock rows in opposite order | Sort the lock set, then lock |
| Duplicate processing | Lock released before the write committed | Keep read and write in one transaction |
Practical edge cases and deep dives
1. The claim-queue pattern done properly
The safe pattern is a subquery with SKIP LOCKED feeding a single UPDATE ... RETURNING. One round trip, one transaction, no gap between claiming and marking.
WITH claimed AS (
SELECT id FROM jobs
WHERE status = 'queued'
AND run_at <= now()
ORDER BY priority DESC, created_at
LIMIT 25
FOR UPDATE SKIP LOCKED
)
UPDATE jobs j
SET status = 'running',
attempts = j.attempts + 1,
locked_at = now()
FROM claimed c
WHERE j.id = c.id
RETURNING j.id, j.payload, j.attempts;The ORDER BY matters: without it, workers take rows in physical order and a stale low-priority job can starve. With it, ordering happens before the lock is applied to the returned rows. Never split this into SELECT ... FOR UPDATE followed by a separate UPDATE in a different transaction — the lock evaporates at commit and two workers can process the same job.
2. A missing index silently widens the lock
EXPLAIN SELECT id FROM jobs WHERE status = 'queued' LIMIT 10 FOR UPDATE SKIP LOCKED;
-- Seq Scan on jobs (cost=0.00..18320.00 rows=10)With a sequential scan, every worker reads the same leading pages and, even with SKIP LOCKED, throughput collapses because they all start at the same place. The fix is a partial index shaped exactly like the claim predicate.
CREATE INDEX idx_jobs_claim ON jobs (priority DESC, created_at)
WHERE status = 'queued';
-- PostgreSQL 11+ returns the claim order in the plan
EXPLAIN (ANALYZE) SELECT id FROM jobs
WHERE status = 'queued'
ORDER BY priority DESC, created_at
LIMIT 25 FOR UPDATE SKIP LOCKED;
-- Index Scan using idx_jobs_claim3. FOR UPDATE cannot be combined with GROUP BY, DISTINCT or aggregates
-- ERROR: FOR UPDATE is not allowed with GROUP BY clause
SELECT customer_id, count(*) FROM orders GROUP BY customer_id FOR UPDATE;PostgreSQL also rejects FOR UPDATE on the nullable side of an outer join: FOR UPDATE cannot be applied to the nullable side of an outer join. Restructure with a subquery that selects primary keys first.
SELECT * FROM orders o
WHERE o.id IN (SELECT id FROM orders WHERE customer_id = $1)
FOR UPDATE;4. Deadlocks: sort the keys, then lock them
-- Transaction A: locks 10 then 20 Transaction B: locks 20 then 10 -> deadlock
BEGIN;
SELECT * FROM accounts WHERE id = ANY($1::int[]) ORDER BY id FOR UPDATE;A single statement with ORDER BY id inside a subquery, or ORDER BY before NOWAIT, gives every transaction the same acquisition order and removes the cycle. If deadlocks still surface, they are usually caused by two different statements touching overlapping sets. Log deadlock detected with both queries from log_line_prefix and fix the ordering, not the timeout.
5. SKIP LOCKED changes read semantics, including inside the same transaction
Rows skipped by one statement stay skipped for the rest of that statement, but a later statement in the same transaction sees them again if the peer committed. A worker that reads "the count of remaining jobs" with SKIP LOCKED will get a number that disagrees with a plain count.
-- Counter for a progress bar: deliberately NOT skip-locked
SELECT count(*) FROM jobs WHERE status = 'queued';Also remember that SKIP LOCKED on READ COMMITTED sees the newest committed row, while REPEATABLE READ sees the snapshot — a row updated and unlocked by a peer can still be skipped or re-read differently depending on the isolation level.
6. Timeouts, retries and idle transactions
BEGIN;
SET LOCAL lock_timeout = '3s';
SET LOCAL statement_timeout = '5s';
SELECT * FROM accounts WHERE id = $1 FOR UPDATE;
-- application maps 55P03 to HTTP 409 and retries with jitter
COMMIT;SET LOCAL scopes the timeout to the transaction, which is exactly the right blast radius. A connection pool full of transactions that sit in idle in transaction while the application calls a payment API will hold row locks for seconds and produce queue stalls that look like database problems. Bound the transaction to database work only.
7. When a lock is the wrong tool
For low-contention counters, UPDATE ... SET n = n + 1 is atomic and needs no explicit lock. For long-running domain operations, an explicit application-level state machine (UPDATE ... WHERE status = 'pending' RETURNING) gives the same serialisation with a smaller lock window. Advisory locks (pg_advisory_xact_lock) cover "only one process should do this at a time" without locking rows at all.
Common mistakes and runtime errors
| Error / symptom | Cause | Fix |
|---|---|---|
55P03 could not obtain lock on row | NOWAIT or lock_timeout | Retry with backoff, or drop NOWAIT |
| Worker pool throughput flatlines | SKIP LOCKED missing | Add SKIP LOCKED |
| Same job processed twice | Claim and update in separate transactions | One UPDATE ... RETURNING |
| Locks held for minutes | Transaction wraps network calls | Shrink the transaction |
40P01 deadlock detected | Inconsistent lock ordering | ORDER BY id before locking |
FOR UPDATE cannot be applied to the nullable side of an outer join | Outer join in the locking query | Separate key selection from locking |
FOR UPDATE is not allowed with GROUP BY | Aggregate plus row lock | Subquery of primary keys |
Blocker shows idle in transaction | Application bug | idle_in_transaction_session_timeout |
| MySQL locks far more rows than expected | No index on the predicate, gap locks at REPEATABLE READ | Index the predicate; consider READ COMMITTED |
| SQL Server ignores row granularity | Page/table escalation | Recheck indexes; keep batches small |
Try matching browser tools
All toolsSmall utilities that fit SQL work — they run entirely in this browser tab, so nothing you paste leaves your device.
Related SQL guides
All SQL guidesSQL Cursors vs Set-Based Operations: Syntax, Memory, and Performance
Why a row-by-row cursor costs minutes where one UPDATE takes seconds, how server cursors spool to disk, and the set-based replacements for each loop.
SQL String Functions: CONCAT, SUBSTRING, REPLACE, and Pattern Matching
The concatenation and slice functions that differ between engines, why LIKE 'x%' uses an index and '%x%' cannot, plus escaping and NULL rules.
SQL Date and Time Functions: INTERVAL Arithmetic, Time Zones, and DST
timestamptz versus timestamp, half-open ranges that keep an index, and the daylight saving gaps that make local scheduling wrong once a year.
SQL JSON and JSONB: Functions, Operators, and Indexing Strategies
Why jsonb is the default for queryable documents, when containment can use a GIN index, and the expression-index fixes for scalar lookups.
More in SQL
All SQL guidesThe guides filed next to this one in SQL, in library order.