SQL
Safely Process Queue Items Concurrently Using SELECT FOR UPDATE
Implement a robust queue processing mechanism in SQL, ensuring that only one worker can claim and process a task at a time, preventing race conditions.
-- PostgreSQL Example for a simple job queue
BEGIN;
SELECT id, payload
FROM job_queue
WHERE status = 'pending'
ORDER BY created_at ASC
FOR UPDATE SKIP LOCKED
LIMIT 1;
-- If a row is returned, process it and then update its status
-- For example, assuming we got a job with id = 123:
-- UPDATE job_queue SET status = 'processing', worker_id = 42, started_at = NOW() WHERE id = 123;
-- ... application logic to process payload ...
-- UPDATE job_queue SET status = 'completed', completed_at = NOW() WHERE id = 123;
COMMIT;
-- In case of failure, ROLLBACK;
How it works: This snippet illustrates a transactional pattern for safely processing items from a job queue in a concurrent environment. The SELECT ... FOR UPDATE SKIP LOCKED LIMIT 1 clause in PostgreSQL ensures that only one worker process can acquire a specific pending job at a time. FOR UPDATE locks the selected row, and SKIP LOCKED prevents other transactions from waiting, allowing them to pick the next available job. This prevents multiple workers from processing the same job simultaneously, ensuring data integrity for background tasks.