Serializable queue pop (advanced)
SQL
Hard
2 views
Problem Description
Implement a safe job-queue pop: pick one NEW job, lock it so others can't pick it, mark it IN_PROGRESS, and return the picked job_id. Use FOR UPDATE SKIP LOCKED if available.
Sample Test Case
Input:
Jobs(job_id, status, created_at)
Constraints
Assume PostgreSQL and SKIP LOCKED support
Official Solution
BEGIN; WITH picked AS (SELECT job_id FROM Jobs WHERE status='NEW' ORDER BY created_at FOR UPDATE SKIP LOCKED LIMIT 1) UPDATE Jobs SET status='IN_PROGRESS' WHERE job_id IN (SELECT job_id FROM picked); COMMIT;
Solutions (0)
No solutions submitted yet. Be the first!
No comments yet. Start the discussion!