Concurrent job queue: FOR UPDATE SKIP LOCKED
Each worker claims a batch of jobs by skipping rows already locked by the others: a reliable work queue in pure SQL, no external broker.
Prerequisites
PostgreSQL 9.5+, MySQL 8+, Oracle
SQL
WITH next_jobs AS (
SELECT id
FROM job_queue
WHERE status = 'pending'
ORDER BY priority DESC, created_at
LIMIT 10
FOR UPDATE SKIP LOCKED -- ignore les lignes prises par d'autres
)
UPDATE job_queue q
SET status = 'processing',
locked_by = pg_backend_pid(),
locked_at = NOW()
FROM next_jobs n
WHERE q.id = n.id
RETURNING q.id, q.payload;Result
id | payload
-------+-------------------------------------
18203 | {"type": "email", "to": "client A"}
18204 | {"type": "invoice", "order": 9102}
18207 | {"type": "email", "to": "client B"}
(3 rows)
UPDATE 3 -- 18205 et 18206 sautés : déjà verrouillés par un autre workerSQLSKIP LOCKEDJob queueConcurrence