SKIP LOCKED job queues
On This Lesson 4 topics
This lesson is planned. The outline below lists the topics it will cover. The full lesson has not been published yet.
In this lesson
build a safe multi-worker queue in a table.
Planned topics
- Workers claim a job with
FOR UPDATE SKIP LOCKED, so no two take the same job and nobody waits. - Claim and mark in one statement using a CTE with
UPDATE ... RETURNING. - Handle crashed workers: a claimed job rolls back (lock released) if the transaction dies.
- Fine for moderate volumes; for heavy messaging use a broker (later modules).
Draft outline details
Demonstration
WITH next AS (
SELECT id FROM job WHERE status = 'NEW'
ORDER BY id LIMIT 1 FOR UPDATE SKIP LOCKED
)
UPDATE job SET status = 'RUNNING' FROM next WHERE job.id = next.id RETURNING job.*;Check your understanding
What does SKIP LOCKED do?
Show answer
Ignores rows other transactions have locked instead of waiting.
Practice
Apply this lesson with the module's lab, exercises and quiz.
Marking a lesson complete is optional and you can undo it any time. Your progress stays in this browser.