CONCEPT Cited by 1 source

SELECT FOR UPDATE SKIP LOCKED¶

Definition¶

SELECT ... FOR UPDATE SKIP LOCKED is a row-locking clause that acquires a row-level exclusive lock on the matching rows and skips over any rows another transaction has already locked. It is the SQL-standard building block for lock-free job-queue claim semantics: multiple workers run the same SELECT ... LIMIT 1 FOR UPDATE SKIP LOCKED ... ORDER BY run_at query concurrently, and each worker receives a different pending row without blocking.

Introduced in Postgres 9.5 and available in MySQL 8.0+ and Oracle.

Example¶

BEGIN;

SELECT * FROM jobs
  WHERE status = 'pending'
  ORDER BY run_at
  LIMIT 1
  FOR UPDATE SKIP LOCKED;

-- worker performs the job

DELETE FROM jobs WHERE id = $1;
COMMIT;

Without SKIP LOCKED, N workers running this query would serialize on the same head-of-queue row. With SKIP LOCKED, worker 1 gets row A, worker 2 gets row B, etc., without any worker blocking.

What it fixes vs what it doesn't¶

SKIP LOCKED replaced the pre-9.5 queue-claim idiom (recursive CTEs or advisory locks) and is the correct modern primitive for Postgres-backed queues. Compared to the 2015-era recursive-CTE approach used by the original Brandur que-degradation-test, it:

Eliminates serialization on the claim path.
Does not fix dead-tuple accumulation in the B-tree index scanned by the ORDER BY run_at plan — both approaches walk the same B+tree and encounter the same dead tuples.
Does not protect against MVCC horizon pinning from other workloads on the database.

PlanetScale re-ran Brandur's 2015 degradation benchmark on Postgres 18 with SKIP LOCKED + batching, and the degradation curve was almost identical to the legacy recursive-CTE shape. Lock times climbed from 1.3–3.0 ms baseline to 180 ms at 24k dead tuples — the dead-tuple dynamic is load-bearing, not the claim-API choice (Source: sources/2026-04-11-planetscale-keeping-a-postgres-queue-healthy).

Batching¶

A common complementary optimization is claiming N jobs per transaction instead of 1:

SELECT * FROM jobs
  WHERE status = 'pending'
  ORDER BY run_at
  LIMIT 10
  FOR UPDATE SKIP LOCKED;

Amortizes the index-scan cost over 10 deletes instead of 1. In non-pressure tests this measurably reduced per-job work but still produced the same dead-tuple accumulation rate as single-row claims — the underlying write churn is unchanged, just redistributed (Source: sources/2026-04-11-planetscale-keeping-a-postgres-queue-healthy).

Required complementary patterns¶

SKIP LOCKED is necessary but not sufficient for a healthy production queue:

Keep worker transactions short — the horizon is pinned for as long as the worker holds the claim row's transaction open.
Ensure autovacuum can reclaim dead tuples — see concepts/postgres-autovacuum and patterns/workload-class-resource-budget.
Use a partial index covering the hot predicate: CREATE INDEX ... ON jobs (run_at) WHERE status = 'pending'.

Seen in¶

sources/2026-04-11-planetscale-keeping-a-postgres-queue-healthy — Canonical modern Postgres queue claim pattern; demonstrated not to solve the MVCC-horizon failure mode on its own. The article's thesis: modern Postgres has lifted the floor (SKIP LOCKED + B-tree bottom-up deletion) but not removed the ceiling (horizon-pinned dead-tuple accumulation from overlapping analytics queries).