PostgreSQL FOR UPDATE SKIP LOCKED: Build a Safe Job Queue

FOR UPDATE SKIP LOCKED tells PostgreSQL to lock the rows a query selects, but to quietly skip any row that another transaction has already locked instead of waiting for it. That one clause lets several workers pull jobs from the same table at the same time without blocking each other and without two workers picking the same job.

This guide builds a small job queue on a real table, runs two and three sessions side by side, and shows the actual output at each step. You will see why a plain FOR UPDATE makes workers wait in line, what NOWAIT does instead, how to claim a job in a single safe statement, and the mistakes that quietly break a queue.

How FOR UPDATE SKIP LOCKED works

A normal SELECT takes no row locks. Adding FOR UPDATE locks every row the query returns until your transaction ends with COMMIT or ROLLBACK. If another transaction already holds a conflicting lock on one of those rows, your query stops and waits.

The locking clause has two options that change what happens on a conflict:

SKIP LOCKED is available from PostgreSQL 9.5 onward. The full syntax is FOR lock_strength [ OF table_name [, ...] ] [ NOWAIT | SKIP LOCKED ], where the lock strength is UPDATE, NO KEY UPDATE, SHARE or KEY SHARE. This article uses FOR UPDATE because a worker is about to change the job row.

Sample setup: a jobs table

Create a scratch database and this table. Each row is a background job, such as sending an order email or building an invoice. Higher priority jobs should run first.

CREATE TABLE jobs (
    job_id      bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    job_type    text        NOT NULL,
    payload     jsonb       NOT NULL,
    status      text        NOT NULL DEFAULT 'pending',
    priority    int         NOT NULL DEFAULT 0,
    attempts    int         NOT NULL DEFAULT 0,
    created_at  timestamptz NOT NULL DEFAULT now(),
    started_at  timestamptz
);

INSERT INTO jobs (job_type, payload, priority) VALUES
  ('send_email',   '{"order_id": 1001}', 0),
  ('send_email',   '{"order_id": 1002}', 0),
  ('make_invoice', '{"order_id": 1001}', 5),
  ('send_sms',     '{"order_id": 1003}', 0),
  ('make_invoice', '{"order_id": 1002}', 5);

SELECT job_id, job_type, payload, status, priority
  FROM jobs
 ORDER BY job_id;

Every worker wants "the next pending job": highest priority first, oldest job_id first within the same priority. With this data, that means job 3, then job 5, then jobs 1, 2 and 4.

Step 1: see the problem with plain FOR UPDATE

Open two psql sessions. Session A starts a transaction and locks the next job, then keeps the transaction open, as a worker does while it processes the job.

-- Session A
BEGIN;

SELECT job_id, job_type, payload
  FROM jobs
 WHERE status = 'pending'
 ORDER BY priority DESC, job_id
 LIMIT 1
   FOR UPDATE;

BEGIN
 job_id |   job_type   |      payload
--------+--------------+--------------------
      3 | make_invoice | {"order_id": 1001}
(1 row)

Now session B runs the exact same query. It does not return. It sits there, waiting for session A to finish.

Find out who is waiting on whom

From a third session, pg_stat_activity and pg_blocking_pids() show the wait:

SELECT pid, state, wait_event_type, wait_event
  FROM pg_stat_activity
 WHERE datname = 'qdemo'
   AND pid <> pg_backend_pid();

SELECT pid, pg_blocking_pids(pid) AS blocked_by
  FROM pg_stat_activity
 WHERE cardinality(pg_blocking_pids(pid)) > 0;

 pid  |        state        | wait_event_type |  wait_event
------+---------------------+-----------------+---------------
 1326 | idle in transaction | Client          | ClientRead
 1330 | active              | Lock            | transactionid
(2 rows)

 pid  | blocked_by
------+------------
 1330 | {1326}
(1 row)

Session A (pid 1326) is idle in transaction: it holds the lock and is waiting for its client to send the next command. Session B (pid 1330) has wait_event_type = Lock and is blocked by A. It waits on A's transaction ID because PostgreSQL records row locks on the row itself, so a waiter has to wait for the locking transaction to end. Your pids will differ, and qdemo is the scratch database name used here.

This is safe, but it does not scale. With ten workers, nine of them wait behind the first one, so your "parallel" workers process one job at a time.

Step 2: fail fast with NOWAIT

While session A still holds its lock, run the query in session B with NOWAIT:

-- Session B
SELECT job_id, job_type, payload
  FROM jobs
 WHERE status = 'pending'
 ORDER BY priority DESC, job_id
 LIMIT 1
   FOR UPDATE NOWAIT;

ERROR:  could not obtain lock on row in relation "jobs"

No waiting, but no job either. The error aborts session B's transaction if it was inside one, so the application must catch it, roll back and retry. NOWAIT suits cases like "let a user edit this record only if nobody else is editing it". It is a poor fit for a queue, because the workers keep hitting the same top row and failing.

Step 3: skip the locked job with SKIP LOCKED

Still with session A holding job 3, run the query in session B with SKIP LOCKED:

-- Session B
SELECT job_id, job_type, payload
  FROM jobs
 WHERE status = 'pending'
 ORDER BY priority DESC, job_id
 LIMIT 1
   FOR UPDATE SKIP LOCKED;

 job_id |   job_type   |      payload
--------+--------------+--------------------
      5 | make_invoice | {"order_id": 1002}
(1 row)

Session B returns immediately with job 5, the next job in priority order that nobody holds. Job 3 is still pending in the table. It is just invisible to B while A has it locked.

Why does LIMIT 1 still return a row after skipping one? Look at the plan. The lock step sits below the limit, so PostgreSQL keeps pulling sorted rows and trying to lock them until it has one it could lock:

EXPLAIN (COSTS OFF)
SELECT job_id, job_type, payload
  FROM jobs
 WHERE status = 'pending'
 ORDER BY priority DESC, job_id
 LIMIT 1
   FOR UPDATE SKIP LOCKED;

                       QUERY PLAN
--------------------------------------------------------
 Limit
   ->  LockRows
         ->  Sort
               Sort Key: priority DESC, job_id
               ->  Seq Scan on jobs
                     Filter: (status = 'pending'::text)
(6 rows)

Step 4: claim a job in one statement

Selecting the job is only half the work. The worker must also mark it as taken. Do both in one UPDATE with a subquery. UPDATE itself has no SKIP LOCKED option, so the locking clause goes inside the subquery:

UPDATE jobs
   SET status = 'running',
       started_at = now(),
       attempts = attempts + 1
 WHERE job_id = (
         SELECT job_id
           FROM jobs
          WHERE status = 'pending'
          ORDER BY priority DESC, job_id
          LIMIT 1
          FOR UPDATE SKIP LOCKED
       )
RETURNING job_id, job_type, payload, attempts;

RETURNING hands the claimed job back to the application, so no second query is needed. To test it under real concurrency, three sessions each ran BEGIN, this statement, then SELECT pg_sleep(3) to simulate work, then COMMIT, all started within a second of each other:

-- worker 1
 job_id |   job_type   |      payload       | attempts
--------+--------------+--------------------+----------
      3 | make_invoice | {"order_id": 1001} |        1

-- worker 2
 job_id |   job_type   |      payload       | attempts
--------+--------------+--------------------+----------
      5 | make_invoice | {"order_id": 1002} |        1

-- worker 3
 job_id |  job_type  |      payload       | attempts
--------+------------+--------------------+----------
      1 | send_email | {"order_id": 1001} |        1

Each worker got a different job, in priority order, and none of them waited for another. After all three committed, the table looks like this:

Claim several jobs at once

If a worker can handle a batch, swap = for IN and raise the limit. This claimed the two remaining pending jobs:

UPDATE jobs
   SET status = 'running',
       started_at = now(),
       attempts = attempts + 1
 WHERE job_id IN (
         SELECT job_id
           FROM jobs
          WHERE status = 'pending'
          ORDER BY priority DESC, job_id
          LIMIT 10
          FOR UPDATE SKIP LOCKED
       )
RETURNING job_id, job_type;

 job_id |  job_type
--------+------------
      2 | send_email
      4 | send_sms
(2 rows)

UPDATE 2

When the queue is empty, or every pending row is locked by someone else, the same statement simply returns UPDATE 0 and no rows. Your worker should treat that as "nothing to do" and sleep briefly before polling again.

Step 5: finish, retry or recover a job

There are two common ways to run a worker, and they differ in how long the transaction stays open.

Pattern A: keep the transaction open while working

Run BEGIN, the claim, the work, then mark the job done and COMMIT. If the worker crashes, PostgreSQL rolls the transaction back, the row lock disappears, and the job is automatically pending again. The cost is one open transaction and one database connection per busy worker for the whole job. Keep this pattern for jobs that take seconds, not minutes.

Pattern B: claim, commit, then work

Commit right after the claim so the status running is saved and the lock is released. Do the work outside any transaction, then record the result:

-- success
UPDATE jobs
   SET status = 'done'
 WHERE job_id = 3;

-- failure: retry up to 3 attempts, then give up
UPDATE jobs
   SET status     = CASE WHEN attempts >= 3 THEN 'failed' ELSE 'pending' END,
       started_at = NULL
 WHERE job_id = 5;

Now a crashed worker leaves its job stuck in running forever, so you need a small cleanup job that puts old running jobs back in the queue. Choose a timeout comfortably longer than your slowest normal job:

UPDATE jobs
   SET status = 'pending',
       started_at = NULL
 WHERE status = 'running'
   AND started_at < now() - interval '15 minutes'
RETURNING job_id, job_type, attempts;

 job_id |  job_type  | attempts
--------+------------+----------
      1 | send_email |        1
(1 row)

UPDATE 1

Because a job can now run more than once (a slow worker might finish after being requeued), the work itself should be safe to repeat. Sending the same email twice is the classic example; check whether it was already sent before sending.

Common mistakes

Putting the lock outside a CTE

Many people write the "pick a job" step as a CTE and add the locking clause to the outer query. PostgreSQL accepts it, but the documentation says locking clauses do not apply to WITH queries referenced by the main query. You can confirm with the pgrowlocks extension, which reads row locks from the table:

CREATE EXTENSION pgrowlocks;

BEGIN;
WITH next_job AS (
  SELECT job_id FROM jobs
   WHERE status = 'pending'
   ORDER BY job_id LIMIT 1
)
SELECT * FROM next_job FOR UPDATE SKIP LOCKED;

SELECT locked_row, modes FROM pgrowlocks('jobs');
ROLLBACK;

 job_id
--------
      1
(1 row)

 locked_row | modes
------------+-------
(0 rows)

The query returned job 1 but locked nothing, so two workers can take the same job. Move FOR UPDATE SKIP LOCKED inside the CTE and the lock appears:

BEGIN;
WITH next_job AS (
  SELECT job_id FROM jobs
   WHERE status = 'pending'
   ORDER BY job_id LIMIT 1
     FOR UPDATE SKIP LOCKED
)
SELECT * FROM next_job;

SELECT locked_row, modes FROM pgrowlocks('jobs');
ROLLBACK;

 job_id
--------
      1
(1 row)

 locked_row |     modes
------------+----------------
 (0,19)     | {"For Update"}
(1 row)

Running the SELECT without a transaction

In autocommit mode, a lone SELECT ... FOR UPDATE SKIP LOCKED commits as soon as it finishes, and the lock is gone before your code reads the result. Either wrap the select and the status update in one transaction, or use the single UPDATE ... RETURNING statement from Step 4.

Using it with aggregates

SELECT count(*) FROM jobs WHERE status = 'pending' FOR UPDATE SKIP LOCKED;

ERROR:  FOR UPDATE is not allowed with aggregate functions

A count is not a row you can lock. Count without the locking clause, and accept that the number is approximate while workers are running.

Forgetting ORDER BY or LIMIT

Without LIMIT, the first worker locks every pending row and the others get nothing. Without ORDER BY, you get whichever rows the scan finds first, which is not a fair or predictable order.

Performance tips

A queue table usually holds a few pending jobs and a lot of finished ones. With 200,000 done rows added to the sample table, the claim query reads the whole table to find four pending jobs:

Limit (actual rows=1 loops=1)
  ->  LockRows (actual rows=1 loops=1)
        ->  Sort (actual rows=1 loops=1)
              Sort Key: priority DESC, job_id
              Sort Method: quicksort  Memory: 25kB
              ->  Seq Scan on jobs (actual rows=4 loops=1)
                    Filter: (status = 'pending'::text)
                    Rows Removed by Filter: 200001

A partial index that matches the WHERE and ORDER BY of the claim query fixes this:

CREATE INDEX jobs_pending_idx
    ON jobs (priority DESC, job_id)
 WHERE status = 'pending';

Limit (actual rows=1 loops=1)
  ->  LockRows (actual rows=1 loops=1)
        ->  Index Scan using jobs_pending_idx on jobs (actual rows=1 loops=1)
              Filter: (status = 'pending'::text)

The plan now reads one index entry and stops. The partial index was 16 kB, against 4408 kB for the primary key on the same table, because it only covers pending rows.

• Every claim and every status change is an UPDATE, which leaves a dead row version behind. Make sure autovacuum keeps up on the jobs table, and move or delete finished jobs regularly instead of keeping millions of done rows.
• Keep transactions short. A long-open transaction holds row locks and also stops VACUUM from cleaning dead rows across the database.
• When the queue is empty, let workers sleep for a short interval before polling again instead of running the claim query in a tight loop.

Frequently asked questions

What does SKIP LOCKED do in PostgreSQL?

It makes a SELECT ... FOR UPDATE (or FOR SHARE) skip any row it cannot lock immediately, instead of waiting. The query returns only rows it managed to lock.

Which PostgreSQL version added SKIP LOCKED?

PostgreSQL 9.5. Every supported version today has it.

What is the difference between NOWAIT and SKIP LOCKED?

NOWAIT raises ERROR: could not obtain lock on row as soon as it meets a locked row. SKIP LOCKED leaves that row out and returns the others. Neither waits.

Can I use SKIP LOCKED directly in an UPDATE or DELETE?

No. The locking clause belongs to SELECT. Put a SELECT ... FOR UPDATE SKIP LOCKED subquery in the WHERE clause of the UPDATE or DELETE, as shown in Step 4.

Does SKIP LOCKED skip rows locked by the table-level lock?

No. NOWAIT and SKIP LOCKED apply only to row-level locks. The query still takes its normal ROW SHARE table lock and will wait if, for example, an ALTER TABLE holds an ACCESS EXCLUSIVE lock on the table.

Summary

• Plain FOR UPDATE is safe but makes workers wait for each other.
• NOWAIT errors on a locked row; SKIP LOCKED moves on to the next free row.
• Claim jobs with one UPDATE ... WHERE job_id = (SELECT ... FOR UPDATE SKIP LOCKED LIMIT 1) RETURNING statement.
• Put the locking clause inside the CTE or subquery, never only on the outer query.
• Always use ORDER BY and LIMIT in the claim query.
• If you commit before doing the work, add a timeout job that requeues stuck running rows, and make jobs safe to repeat.
• Add a partial index on pending rows and keep finished jobs out of the hot table.
• Never use SKIP LOCKED for reports or any query that must see every row.