Pages

Topics

Tools

↑↓navigate↵openescclose
All guides

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.

~5 min readSQL
#SQL#Transactions#Concurrency
3D illustration of database rows guarded by lock markers while worker queues skip locked rows and claim free ones

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

sql
-- 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 writer

Lock 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.

sql
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.

sql
-- 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

ClauseBehaviour on a locked rowRaisesUse when
FOR UPDATEWaits (bounded by lock_timeout)55P03 on timeout, 40P01 on deadlockCritical section that must serialise
FOR UPDATE NOWAITFails immediately55P03Contended single row, fail-fast API
FOR UPDATE SKIP LOCKEDRow is invisible to this queryNoneQueue workers, batch claim
FOR NO KEY UPDATEWaits, weaker than FOR UPDATE55P03Updating rows referenced by foreign keys
FOR SHAREOther readers proceed, writers wait55P03Read-modify-write with no row change
FOR KEY SHAREOnly key-changing writers wait55P03Referential integrity only
EngineSKIP LOCKEDNOWAITRow lock hintsLock timeout
PostgreSQL9.5+YesNone neededSET lock_timeout
MySQL / InnoDB8.0+YesNone neededinnodb_lock_wait_timeout
SQL ServerREADPAST hintNOWAIT hintROWLOCK/UPDLOCKLOCK_TIMEOUT
Oracle11g+YesNoneddl_lock_timeout, profile
SQLiteNoNoNoBusy timeout
Symptom in productionMechanismCorrect remedy
Workers block each otherNo SKIP LOCKEDAdd it, or serialise at a different level
Whole table appears lockedLock scope widened by a sequential scanIndex the queue predicate
Idle-in-transaction holds locksApplication waits on I/O inside the transactionMove I/O outside, bound the transaction
Random 40P01 under loadTwo code paths lock rows in opposite orderSort the lock set, then lock
Duplicate processingLock released before the write committedKeep 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.

sql
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

sql
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.

sql
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_claim

3. FOR UPDATE cannot be combined with GROUP BY, DISTINCT or aggregates

sql
-- 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.

sql
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

sql
-- 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.

sql
-- 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

sql
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 / symptomCauseFix
55P03 could not obtain lock on rowNOWAIT or lock_timeoutRetry with backoff, or drop NOWAIT
Worker pool throughput flatlinesSKIP LOCKED missingAdd SKIP LOCKED
Same job processed twiceClaim and update in separate transactionsOne UPDATE ... RETURNING
Locks held for minutesTransaction wraps network callsShrink the transaction
40P01 deadlock detectedInconsistent lock orderingORDER BY id before locking
FOR UPDATE cannot be applied to the nullable side of an outer joinOuter join in the locking querySeparate key selection from locking
FOR UPDATE is not allowed with GROUP BYAggregate plus row lockSubquery of primary keys
Blocker shows idle in transactionApplication bugidle_in_transaction_session_timeout
MySQL locks far more rows than expectedNo index on the predicate, gap locks at REPEATABLE READIndex the predicate; consider READ COMMITTED
SQL Server ignores row granularityPage/table escalationRecheck indexes; keep batches small

Try matching browser tools

All tools

Small utilities that fit SQL work — they run entirely in this browser tab, so nothing you paste leaves your device.

All SQL guides

More in SQL

All SQL guides

The guides filed next to this one in SQL, in library order.