SELECT FOR UPDATE SKIP LOCKED — Race-Free Job Queues

Most job queue implementations in PostgreSQL have a race condition: two workers select the same row, both mark it as processing, and the job runs twice. SELECT FOR UPDATE SKIP LOCKED eliminates this at the database level.

-- Job queue table
CREATE TABLE IF NOT EXISTS job_queue (
    id           BIGSERIAL PRIMARY KEY,
    job_type     TEXT NOT NULL,
    payload      JSONB,
    status       TEXT DEFAULT 'pending',  -- pending/processing/done/failed
    priority     INT  DEFAULT 5,
    attempts     INT  DEFAULT 0,
    max_attempts INT  DEFAULT 3,
    scheduled_at TIMESTAMPTZ DEFAULT NOW(),
    started_at   TIMESTAMPTZ,
    finished_at  TIMESTAMPTZ,
    error_msg    TEXT,
    created_at   TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_job_queue_claim
    ON job_queue (status, priority DESC, scheduled_at)
    WHERE status = 'pending';

-- Seed some jobs
INSERT INTO job_queue (job_type, payload, priority)
SELECT
    (ARRAY['send_email','resize_image','generate_report','sync_crm'])[(g%4)+1],
    jsonb_build_object('id', g, 'user', 'user_' || g),
    (g % 10) + 1
FROM generate_series(1, 50) g
ON CONFLICT DO NOTHING;

The Race-Free Claim Pattern

-- Worker claims the next available job atomically
-- SKIP LOCKED: skip any row another transaction has locked
-- FOR UPDATE: lock the claimed row for this transaction
WITH claimed AS (
    SELECT id
    FROM job_queue
    WHERE status = 'pending'
      AND scheduled_at <= NOW()
      AND attempts < max_attempts
    ORDER BY priority DESC, scheduled_at ASC
    LIMIT 1
    FOR UPDATE SKIP LOCKED      -- the critical two words
)
UPDATE job_queue
SET status     = 'processing',
    started_at = NOW(),
    attempts   = attempts + 1
WHERE id = (SELECT id FROM claimed)
RETURNING id, job_type, payload, attempts;