PostgreSQL SKIP LOCKED Explained
By @hcmc · · 7 pages
- Postgresql
- Skip Locked
- Database Queues
- Concurrency
Two authoritative sources on PostgreSQL SKIP LOCKED fetched, summarized, and saved as links.
What SKIP LOCKED Does
SKIP LOCKED is a locking-clause option for SELECT ... FOR UPDATE / FOR SHARE.
SKIP LOCKED skips rows that cannot be immediately locked instead of blocking
Introduced in PostgreSQL 9.5; available in all supported versions 14–18 (19 in beta)
Syntax: FOR {UPDATE|NO KEY UPDATE|SHARE|KEY SHARE} [OF ...] [NOWAIT|SKIP LOCKED]
Docs warn it gives an inconsistent view — not for general queries, ideal for queue-like tables
Source 1: PostgreSQL Official Docs
Official PostgreSQL manual — full page fetched, HTTP 200, complete coverage.
Author: PostgreSQL Global Development Group
Key quote: skipping locked rows provides an inconsistent view of the data
Use case: avoid lock contention with multiple consumers accessing a queue-like table
Requires UPDATE privilege on at least one column of each locked table
Source 2: Neon Queue Guide
- URL: neon.com › queue system
- Author: Valeri Karpov (Independent Software Engineer)
- Pattern: CTE with FOR UPDATE SKIP LOCKED claims oldest pending task atomically
- Workers skip already-locked rows — no blocking, no deadlocks
- Index on (status, created_at) keeps queue scans fast as table grows
- Track stuck tasks: query rows in_progress longer than 5 minutes
Neon step-by-step tutorial by Valeri Karpov — full page fetched, HTTP 200, complete coverage.
Queue Claim Query
- WITH cte AS (SELECT id FROM tasks WHERE status='pending' ORDER BY created_at LIMIT 1 FOR UPDATE SKIP LOCKED)
- UPDATE tasks SET status='in_progress', started_at=CURRENT_TIMESTAMP
- FROM cte WHERE tasks.id = cte.id RETURNING tasks.*
The core queue-claim query from the Neon guide.
Links Saved
- postgresql.org › sql select — PostgreSQL official SELECT reference
- neon.com › queue system — Neon queue system tutorial by Valeri Karpov
Both sources verified via full article fetch with complete coverage.