· Development

Using a PostgreSQL table as a job queue: FOR UPDATE SKIP LOCKED and when the lock is released

How FOR UPDATE SKIP LOCKED lets several workers share jobs in one PostgreSQL table, per the official docs: lock lifetime, LIMIT and OFFSET, ORDER BY, and locking clauses inside WITH.

DevelopmentDatabasesSQL

Put pending work in a jobs table, let several workers take one job each, and you need to stop two workers from grabbing the same row at the same time. In PostgreSQL, the documentation names SELECT ... FOR UPDATE SKIP LOCKED for exactly this. This article follows the docs for PostgreSQL 18 (18.6), the newest stable release as of October 10, 2026. The SKIP LOCKED passages quoted below read the same in the docs for 14 (14.24), the oldest version still supported. SKIP LOCKED was added in 9.5.

The basic shape

Here is the table used in the examples.

CREATE TABLE jobs (
  id      bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  status  text NOT NULL DEFAULT 'pending',
  payload jsonb NOT NULL
);

A worker opens a transaction and takes one pending job, locking it as it goes.

BEGIN;

SELECT id, payload
FROM jobs
WHERE status = 'pending'
ORDER BY id
LIMIT 1
FOR UPDATE SKIP LOCKED;

-- After the application has processed the job, using the id from above
UPDATE jobs SET status = 'done' WHERE id = 42;

COMMIT;

Without SKIP LOCKED, a second worker that reaches the row the first worker locked waits until that transaction ends. With NOWAIT, it reports an error instead of waiting. With SKIP LOCKED, rows that can’t be locked immediately are skipped, so the second worker gets the next pending row.

The docs endorse this with a condition attached. Skipping locked rows gives an inconsistent view of the data, so it isn’t suitable for general-purpose work, but it can be used to avoid lock contention when several consumers access a queue-like table. Also, NOWAIT and SKIP LOCKED apply only to row-level locks; the ROW SHARE table-level lock is still taken the ordinary way. To avoid waiting for the table lock as well, the docs suggest running LOCK ... NOWAIT first.

The lock is released when the transaction ends

A row locked with FOR UPDATE can’t be locked, modified or deleted by other transactions until the current transaction ends. Row-level locks are released at transaction end or on rollback to a savepoint.

That leads to a common mistake. A statement sent without BEGIN runs in autocommit mode as a transaction of its own and commits when the statement finishes. Send the SELECT ... FOR UPDATE SKIP LOCKED above without BEGIN, and by the time you have the result the lock is already gone, so another worker can take the same row. That’s why it’s worth checking whether your driver or ORM runs in autocommit.

If instead you keep the transaction open while processing and the job fails, rolling back undoes the status change and releases the lock, so the row stays pending and another worker can pick it up. The cost is that the transaction stays open for as long as the processing takes. The docs note that, short of a deadlock, a transaction waiting for a lock waits indefinitely, and so holding transactions open for long periods, for example while waiting for user input, is a bad idea.

For long-running jobs, another arrangement is to set the status to running in a short transaction, commit, and do the work outside the transaction. Then a worker that dies mid-job leaves its row stuck at running, and you need a separate way to put such rows back. This comparison of the two arrangements is reasoned from the behaviour above; the docs don’t make it.

The status condition is checked again

When two workers run the same query at almost the same time, a row that one worker has finished and committed as done may still look pending in the other query’s snapshot. According to the docs, when a locking clause is at the top level of a SELECT, rows that matched the conditions as of the query snapshot are locked, but they aren’t returned if they were updated after the snapshot and no longer match. How rows are re-evaluated under the default READ COMMITTED level is covered in READ COMMITTED and snapshots.

Raise the isolation level to REPEATABLE READ or SERIALIZABLE and things change: at those levels an error is thrown if a row to be locked has changed since the transaction started. The examples here assume READ COMMITTED.

LIMIT is fine, OFFSET needs care

When a locking clause is combined with LIMIT, the docs say locking stops once enough rows have been returned to satisfy the limit. With LIMIT 1, only the one row you get back is locked. Rows skipped over by OFFSET, however, do get locked. Giving each worker a different OFFSET to split the work would lock rows the worker never receives, and with SKIP LOCKED there’s no reason to do that anyway.

Locking clauses can’t be used with aggregation, because the returned rows can’t be clearly tied to individual table rows.

ORDER BY doesn’t guarantee processing order

ORDER BY id only makes each worker pick the lowest id among rows it can lock. A lower id that another worker holds gets skipped, so there’s no guarantee that jobs start or finish in id order. If the worker holding a job rolls back, that job may be processed again after jobs that were queued later. If you need strict first-in, first-out, this approach alone isn’t enough. The conclusions in this paragraph are drawn from the documented behaviour above.

Keep the ORDER BY anyway. The docs explain that LIMIT without ORDER BY doesn’t determine which subset of rows you get, even across repeated runs of the same query.

Taking a job in one statement: a locking clause inside WITH

You can also combine the lookup and the status change into one statement.

WITH next_job AS (
  SELECT id
  FROM jobs
  WHERE status = 'pending'
  ORDER BY id
  LIMIT 1
  FOR UPDATE SKIP LOCKED
)
UPDATE jobs
SET status = 'running'
WHERE id IN (SELECT id FROM next_job)
RETURNING id, payload;

Where the locking clause goes matters. Per the docs, locking clauses don’t apply to WITH queries referenced by the primary query, so to lock rows inside a WITH query you have to write the clause inside it. RETURNING on the UPDATE sends values from the row it actually updated back to the client, so the worker gets the job’s id and payload from this single statement.

Sent without BEGIN, this statement also commits on its own and the lock is released. What protects the row from other workers at that point is the committed running status, not a lock, which makes it the same as the short-transaction arrangement described earlier.

Locked rows are still visible to plain SELECTs

Row-level locks don’t affect data querying; they block only writers and lockers of the same row. If your workers process jobs while holding the lock inside a transaction, a monitoring query such as SELECT count(*) FROM jobs WHERE status = 'pending' counts jobs that are in progress as pending.

Other databases

According to the Compatibility section, the SQL standard allows FOR UPDATE only as an option of DECLARE CURSOR, and NOWAIT and SKIP LOCKED aren’t in the standard. Even if another database supports the same keywords, check its own documentation for the behaviour. Everything in this article applies only to PostgreSQL.

Sources

Links and content checked on October 10, 2026.

Back to the blog