> ## Content Index
> Fetch the complete content index at: https://debugly.dev/llms.txt
> Use this file to discover other available public pages before exploring further.

# PostgreSQL SKIP LOCKED: An Empty Result Is Not an Empty Queue
- URL: https://debugly.dev/postgresql-skip-locked-empty-result-queue/
- Published: 2026-11-03T12:31:54.000Z
- Updated: 2026-11-03T12:31:54.000Z
- Description: A worker can claim nothing while ready jobs remain. Trace row visibility, lock availability and durable ownership before treating an empty poll as completion.
- Author: Rohit Bhadani
- Tags: Databases, PostgreSQL, Concurrency, Debugging

**Editorial disclosure:** This is an AI-prepared, documentation-based explainer, not a personally reproduced incident. All SQL and concurrency scenarios below are illustrative and have not been executed for this article. See the [editorial policy](https://debugly.dev/editorial-policy/).

A worker asks PostgreSQL for the next job and gets nothing. A separate query still shows ready jobs. The tempting explanations are replication lag, a broken index or a worker pointed at the wrong database.

With `FOR UPDATE SKIP LOCKED`, both observations can be correct. A row can satisfy the eligibility filter and still be unavailable to that worker because another transaction holds an incompatible row lock. The empty result describes what this claim attempt obtained. It does not certify that the queue is drained.

The distinction matters twice: when deciding whether to stop polling, and when deciding what protection a successful claim actually gives. A row lock coordinates database transactions. It does not, by itself, coordinate an external side effect after those transactions finish.

## What the worker actually asked

PostgreSQL's [SELECT documentation](https://www.postgresql.org/docs/18/sql-select.html?ref=debugly.dev#SQL-FOR-UPDATE-SHARE) explicitly permits this pattern for queue-like tables. Rows that cannot be locked immediately are skipped, producing an intentionally incomplete view. The reference here is PostgreSQL 18; this is an evergreen diagnostic explanation, not a new-release announcement.

Consider a queue with one due job. Worker A locks that row and leaves its transaction open. Worker B runs the same claim query with `SKIP LOCKED` and returns zero rows. An ordinary read can still see the committed row. There is no contradiction: visibility and permission to acquire a conflicting lock are separate properties.

If A rolls back, the job can become claimable again. If A commits a transition to running, B's later attempt should no longer select it. Neither outcome was established by B's earlier empty result. A supervisor that turns that result into “all work completed” has promoted a local scheduling observation into a global completion claim.

## A claim must survive the lock being released

A fragile worker selects an ID in one transaction, commits, and later updates it to running. The selection lock has already ended, leaving a gap in which another worker can select the same job.

The [locking reference](https://www.postgresql.org/docs/18/explicit-locking.html?ref=debugly.dev#LOCKING-ROWS) explains that row locks normally last until transaction end. The fix is to make selection and the ownership transition one database operation, then verify the claim committed before starting external work.

This illustrative statement assumes a `jobs` table with a unique primary-key `id`, `state`, `run_at`, `payload`, `claim_token` (UUID) and `claimed_at`. It is a claim primitive, not a complete queue implementation. The caller supplies a fresh UUID as parameter `$1` for this attempt:

```
WITH candidate AS (
  SELECT id
  FROM jobs
  WHERE state = 'ready'
    AND run_at <= now()
  ORDER BY run_at, id
  LIMIT 1
  FOR UPDATE SKIP LOCKED
)
UPDATE jobs AS j
SET state = 'running',
    claim_token = $1::uuid,
    claimed_at = now()
FROM candidate AS c
WHERE j.id = c.id
RETURNING j.id, j.payload, j.claim_token;
```

The locking clause belongs inside the candidate query, where the row is selected. The update records ownership before the transaction releases its lock. [UPDATE RETURNING](https://www.postgresql.org/docs/18/sql-update.html?ref=debugly.dev) returns the row actually changed, rather than requiring a second read that might observe a different state.

Run this as a short transaction. If the client uses an explicit transaction, receiving the returned row is not enough: wait for successful commit. If the connection disappears around commit, reconcile the persisted claim token before deciding whether to claim another job. An uncertain response is not proof of rollback.

## The crash moved; it did not disappear

Once a running claim commits, the row's state keeps normal claimers from selecting it. That avoids keeping a database transaction open while an API call runs. It also creates a recovery obligation: a worker can die after claiming and before completing.

There are at least three different failure windows. A crash before claim commit leaves no committed claim. A crash after commit can strand a running job. A crash after the external operation succeeds but before completion is recorded can lead recovery to perform the operation again.

A practical design therefore needs an explicit recovery policy: who detects abandoned work, when a claim expires, how retries are bounded, and how a stale worker is prevented from finalizing a newer owner's claim. Store an ownership token and require it when recording completion; check the affected-row count. If the token changed, the old worker must not mark the new claim done.

That condition protects the queue record. It does not retract an external payment, email or API write that the stale worker already performed. Use a stable logical-job idempotency key at the side-effect boundary where supported, and design reconciliation for ambiguous responses. Changing the claim token for every attempt is useful for ownership; using that changing token as the external idempotency key would defeat deduplication across attempts.

Debugly's [coupon retry analysis](https://debugly.dev/coupon-applied-twice-on-retry/) covers that second boundary. Exclusive selection and exactly-once business effects are different promises.

## Diagnose the empty claim before changing the SQL

| Observation                          | What to establish next                                                   |
| ------------------------------------ | ------------------------------------------------------------------------ |
| Zero claims; due ready rows visible  | Concurrent ownership, open transactions and the exact eligibility filter |
| Running rows age without completions | Worker liveness, external-operation state and abandoned-claim recovery   |
| The claim statement waits            | The actual wait event; SKIP LOCKED does not bypass every database lock   |
| Jobs complete out of enqueue order   | Whether the design requires strict ordering or only parallel progress    |
| Duplicates despite distinct claims   | Recovery attempts and the external idempotency boundary                  |

Compare a read-only eligibility count using the same filters, database and role as the worker. Record the time and isolation level. It is supporting evidence, not an atomic comparison: under [Read Committed](https://www.postgresql.org/docs/18/transaction-iso.html?ref=debugly.dev), separate commands can see different committed snapshots. Tenant filters, row-level security, due times and state predicates also matter. Counting every row in the table does not measure claimable work.

Inspect [pg\_stat\_activity](https://www.postgresql.org/docs/18/monitoring-stats.html?ref=debugly.dev#MONITORING-PG-STAT-ACTIVITY-VIEW) for long transactions and wait events, using an appropriately authorized monitoring account. A worker skipping row locks need not itself appear blocked. Conversely, an `idle in transaction` session can retain locks while doing no active query. Correlate ownership, transaction age and worker logs rather than inferring every skipped row from a single activity snapshot.

The SELECT reference also limits `SKIP LOCKED` to row-lock handling. Required table locks still apply, so a schema operation can block a claim. Debugly's [timeout comparison](https://debugly.dev/postgresql-statement-lock-transaction-timeout-differences/) helps identify which clock ended such a wait. Adding more workers will not remove a conflicting table lock.

## Fast claims are not necessarily fair claims

`ORDER BY run_at, id` makes candidate preference explicit and breaks timestamp ties. It does not force workers to wait for the first job, and it cannot make concurrent workers finish in that order. A locked early job may be bypassed repeatedly. If ordering is a business invariant, decide where serialization is required instead of assuming a sort clause supplies it.

For this schema, an index on `(run_at, id) WHERE state = 'ready'` is a candidate to evaluate, not a universal performance prescription. PostgreSQL's [partial-index documentation](https://www.postgresql.org/docs/18/indexes-partial.html?ref=debugly.dev) explains that the query predicate must imply the index predicate. Different query shapes, data distributions and parameterization can affect the plan.

Inspect the plan and transaction duration before claiming a speedup. [EXPLAIN ANALYZE](https://www.postgresql.org/docs/18/sql-explain.html?ref=debugly.dev) actually executes the statement, including a claim update. Use a disposable test queue for experiments; do not treat it as a passive production inspection.

Instrument claims, oldest due-job age, oldest running-claim age, completions and recoveries separately. Back off with jitter when a claim returns nothing, then try again according to the worker lifecycle. If you need to declare a finite batch complete, track that batch's durable terminal states and outstanding ownership explicitly. An empty poll is an invitation to wait, not a proof that the work is finished.