Re: Best MySQL schema for storing image processing jobs?

[Herrick Peterson]

Good call, yes SKIP LOCKED is basically essential once you've got more than one worker polling the same table. Without it you get workers either blocking on each other or double-picking the same job in a race condition, both annoying in different ways.

One thing to pair with it: wrap the select+status update in a single transaction, something like:

sql
START TRANSACTION;
SELECT id FROM jobs WHERE status = 'queued'
ORDER BY created_at LIMIT 1 FOR UPDATE SKIP LOCKED;
UPDATE jobs SET status = 'processing', worker_id = ? WHERE id = ?;
COMMIT;

That way the lock only holds for the tiny window of claiming the job, not the actual processing time. Also worth adding a locked_at timestamp alongside worker_id, if a worker crashes mid-job you can have a cleanup process requeue anything stuck in processing past some timeout, otherwise those jobs just sit there forever with no worker coming back for them.

Herrick
DevOps Engineer
Accuweb.cloud