Claim Queue Rows with FOR UPDATE SKIP LOCKED in PostgreSQL

calendar Oct 24, 2026 / · 11 min read · skip locked row locking queue table delete returning ctid mvcc concurrency batch delete  ·
Share on: linkedin copy

Claim Queue Rows with FOR UPDATE SKIP LOCKED in PostgreSQL

Several workers poll the same job table with SELECT ... LIMIT 10, and a plain LIMIT does nothing to stop two of them from getting the same list of items to process. Add FOR UPDATE and the second worker hangs until the first one commits. Put FOR UPDATE SKIP LOCKED in the CTE of a batch DELETE ... RETURNING and rows that cannot be locked immediately are skipped, so each worker deletes and receives its own batch in one statement.

Purpose and Overview

A queue table is an ordinary PostgreSQL table that holds work items for several worker processes. New jobs go in with plain INSERT statements, and processed jobs come out with DELETE statements. The sample job_queue table has three columns: a status text that defaults to 'ready', a creation_date timestamp that defaults to now(), and a text payload.

The claim statement locks, deletes and hands off the rows in one pass. A CTE picks up to 10 ready rows, oldest first, and locks them with FOR UPDATE SKIP LOCKED. The DELETE then joins to that CTE on ctid. RETURNING computes values based on each row deleted, so RETURNING dj.* gives the worker status, creation_date and payload for every row it claimed. That result set is the worker's batch.

The cost is consistency. Skipping locked rows provides an inconsistent view of the data. That is acceptable for multiple consumers accessing a queue-like table but not for general reads.

Sample Code

 1-- Test setup only: a minimal job queue table
 2CREATE TABLE IF NOT EXISTS job_queue (
 3  status        text        NOT NULL DEFAULT 'ready',
 4  creation_date timestamptz NOT NULL DEFAULT now(),
 5  payload       text
 6);
 7
 8INSERT INTO job_queue (status, creation_date, payload)
 9SELECT CASE WHEN g % 5 = 0 THEN 'held' ELSE 'ready' END,
10       now() - g * interval '1 minute',
11       'job-' || g
12FROM generate_series(1, 1000) AS g;
13
14-- Basic form: batch delete that waits on locked rows
15WITH delete_batch AS (
16  SELECT j.ctid FROM job_queue AS j
17    WHERE j.status = 'ready'
18    ORDER BY j.creation_date
19    FOR UPDATE
20    LIMIT 10
21)
22DELETE FROM job_queue AS dj
23  USING delete_batch AS del
24  WHERE dj.ctid = del.ctid;

 1-- Test setup only: a minimal job queue table
 2CREATE TABLE IF NOT EXISTS job_queue (
 3  status        text        NOT NULL DEFAULT 'ready',
 4  creation_date timestamptz NOT NULL DEFAULT now(),
 5  payload       text
 6);
 7
 8INSERT INTO job_queue (status, creation_date, payload)
 9SELECT CASE WHEN g % 5 = 0 THEN 'held' ELSE 'ready' END,
10       now() - g * interval '1 minute',
11       'job-' || g
12FROM generate_series(1, 1000) AS g;
13
14-- Claim form: skip rows other workers hold and return the batch
15WITH claim_batch AS (
16  SELECT j.ctid FROM job_queue AS j
17    WHERE j.status = 'ready'
18    ORDER BY j.creation_date
19    FOR UPDATE SKIP LOCKED
20    LIMIT 10
21)
22DELETE FROM job_queue AS dj
23  USING claim_batch AS cb
24  WHERE dj.ctid = cb.ctid
25RETURNING dj.*;

Both blocks build a 1,000-row job table. One row in five is marked held, and the status filter leaves those rows alone. The first block is a batch delete with a 10-row LIMIT. It waits on any candidate row another session holds and returns only a row count. The second block is the claim. It uses the same CTE with SKIP LOCKED added, plus a RETURNING list that hands the deleted rows to the worker. Each run deletes and returns up to 10 of the oldest ready rows that no other session has locked.

Notes: The claim form needs PostgreSQL 9.5 or later for SKIP LOCKED. Change the status filter to match your queue's ready state, use the same ORDER BY in every worker, and set the LIMIT to the batch size one worker can finish. Run the claim between BEGIN and COMMIT so the delete commits only after the work is done.

Code Breakdown

The claim has three parts: the locking CTE, the ctid self-join in DELETE ... USING and the RETURNING list.

The CTE with FOR UPDATE SKIP LOCKED

With a LIMIT, locking stops once enough rows have been returned to satisfy the limit, so LIMIT 10 keeps one worker from locking every ready row in the table.

SKIP LOCKED changes what happens when another transaction already holds a candidate row. The scan passes over that row instead of waiting and keeps going until it has 10 unlocked rows or runs out of candidates. When fewer than 10 unlocked rows remain, the worker gets a short batch. When every remaining row is locked, or the table is empty, it gets zero rows. The worker loop has to treat zero rows as a normal result. Adding SKIP LOCKED to the CTE prevents multiple commands from updating the same row, so no two workers claim the same job.

ORDER BY, deadlock and READ COMMITTED

Without an ORDER BY, rows come back in whatever order the system finds fastest to produce. ORDER BY j.creation_date sets which rows are handled first and can prevent deadlock with other commands that use the same ordering.

Under READ COMMITTED, a SELECT with ORDER BY and a locking clause sorts first and locks afterwards. If a sort-column value changes before the lock is granted, those rows appear out of order, though they are in order by the original values. Placing FOR UPDATE in a sub-query and sorting outside it restores the order, but it results in locking all rows of the table, which defeats a 10-row claim.

The ctid self-join in DELETE ... USING

DELETE has no LIMIT clause, but the CTE and self-join give a similar effect. The CTE returns only ctid values, and WHERE dj.ctid = cb.ctid deletes those physical rows. With the standard table access method, a self-join on the system column ctid is very efficient. This use of ctid is safe only because the query is run repeatedly, which avoids the problem of changed ctids. A worker loop does run it repeatedly. A table with a primary key can join on that key instead.

RETURNING as the worker's batch

RETURNING dj.* returns every column of each deleted row. The worker gets the job payload from the same statement that claimed the job and needs no second SELECT. Skipped rows were never deleted, so they never appear in the output and stay in the table for the next poll. If the payload is wide, list only the columns the worker needs.

Key Row-Locking Concepts

FOR UPDATE vs FOR NO KEY UPDATE

FOR UPDATE prevents other transactions from locking, modifying or deleting the row until the current transaction ends. Any DELETE on a row takes the FOR UPDATE lock mode anyway, so in a claim that ends in DELETE, a weaker clause in the CTE gains nothing. FOR NO KEY UPDATE is weaker and does not block SELECT FOR KEY SHARE commands. Use it when the claim updates a status column and keeps the row instead of deleting it.

NOWAIT vs SKIP LOCKED

Both options stop the statement from waiting for other transactions to commit. With NOWAIT, the statement reports an error if a selected row cannot be locked immediately, and the worker has to catch the error and retry. SKIP LOCKED does not throw an error for locked rows. It returns whatever it could lock. For a queue, that means a smaller batch instead of a failed statement. In a 2016 test of a 10,000-row queue drained by 8 worker processes, each worker running FOR UPDATE SKIP LOCKED with LIMIT 1 processed about 1,250 rows. The run took about as long as a version that picked each row with pg_try_advisory_xact_lock, and because SKIP LOCKED takes no advisory locks, those locks stayed free for other purposes.

Table-level locks still apply

NOWAIT and SKIP LOCKED apply only to row-level locks, and the ROW SHARE table-level lock is still taken in the ordinary way. SELECT with FOR UPDATE takes ROW SHARE on the table, and DELETE takes ROW EXCLUSIVE. ROW SHARE conflicts with EXCLUSIVE and ACCESS EXCLUSIVE. If another session holds either of those on the queue table, every claim waits behind it, and SKIP LOCKED does not help.

Lock lifetime and rollback

Row locks are released at transaction end or during savepoint rollback. Keep the transaction open until the batch is processed. While it stays open, other workers do not get those items and retrieve unlocked ones instead. If processing fails, a ROLLBACK or an application abort returns the original items to the queue, because the DELETE never committed.

Where locking clauses are refused

Locking clauses cannot be used where returned rows cannot be clearly identified with individual table rows, for example with aggregation. They also cannot be specified with GROUP BY. FETCH FIRST ... WITH TIES requires ORDER BY and does not allow SKIP LOCKED, so a claim that needs SKIP LOCKED uses LIMIT, as the sample does.

Practical Applications

Each scenario below runs one of the two blocks. Only the filter, the sort, the LIMIT and the length of the transaction change.

Worker pool draining a job table

Each worker runs a loop: BEGIN, the DELETE ... RETURNING claim, the processing, then COMMIT. No other worker gets those rows while the transaction is open. A worker that fails on a batch rolls back, and the rows go back to the queue for the next poll. The LIMIT is a trade-off: a larger batch means fewer round trips, but one failure sends more work back to the queue.

Archival purge in batches

The basic form fits a maintenance purge. Point the status filter at finished or archived rows and raise the LIMIT. It waits on rows another session holds instead of passing over them. For a large change, running smaller batches makes sense, possibly with a VACUUM on the table between them, and the command has to be repeated until no rows remain.

Priority or topic queues

Topics and priorities can be added as columns on the queue table, and only the sub-SELECT that identifies the rows changes. Filter on topic in the CTE's WHERE so each worker pool claims only its own kind of work, and put priority ahead of creation_date in the ORDER BY. Give every worker in the pool the same ordering, because the same ordering can also prevent deadlock.

Durable queue design

Queued work that has to survive a crash belongs in a normal table instead of an UNLOGGED table. Every claim deletes rows, and table bloat is a consideration for high-turnover queuing on any MVCC database. Autovacuum should clean the table up periodically. Monitor bloat and tune autovacuum for the table. Even with tuning, the queue table can need occasional rotation to deal with bloat.

Version Compatibility

SKIP LOCKED was added to SELECT in PostgreSQL 9.5, released 2016-01-07, as an option that does not throw an error for locked rows like NOWAIT does. The current documentation covers version 18, and the supported versions are 14 through 18. Every supported release has SKIP LOCKED.

The claim does not port unchanged to other databases. NOWAIT and SKIP LOCKED do not appear in the SQL standard, and USING, RETURNING and WITH on DELETE are PostgreSQL extensions. The ctid join is also specific to PostgreSQL. Its efficiency is documented for the standard table access method.

Best Practices

References

Posts in this series