Cola de jobs concurrente: FOR UPDATE SKIP LOCKED
Cada worker reserva un lote de jobs saltándose las filas ya bloqueadas por los demás: una cola de trabajo fiable en SQL puro, sin broker externo.
Requisitos
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;Resultado
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