A Job Queue That Serializes Workers
A background processing system uses InnoDB and a jobs table: CREATE TABLE jobs (id BIGINT AUTO_INCREMENT PRIMARY KEY, status ENUM('queued','running','done','failed') NOT NULL, worker_id INT NULL, updated_at DATETIME, payload JSON, KEY idx_status (status));. Twelve workers each run START TRANSACTION; SELECT id FROM jobs WHERE status = 'queued' ORDER BY id LIMIT 1 FOR UPDATE; UPDATE jobs SET status = 'running', worker_id = ?, updated_at = NOW() WHERE id = ?; COMMIT;, then perform the job. Throughput is only about 5 jobs per second, and SHOW ENGINE INNODB STATUS shows lock waits. After a worker crashes, its jobs stay in running forever. The supervisor also requeues jobs whose updated_at is old, which sometimes causes the same job to run twice when the original worker is still alive but slow.
1. Why do the twelve workers mostly serialize, and how does `SKIP LOCKED` change the claim query?
Read the worked answer
Every worker runs the same ordered SELECT ... FOR UPDATE against status = 'queued'. The first worker locks the lowest-id queued row, and the other eleven block waiting for that same row. Even though there are many queued jobs, they are invisible to the waiting workers because the lock is held until the claim transaction commits. The workers effectively take turns.
SKIP LOCKED tells InnoDB not to wait for rows locked by another transaction; it skips them and returns the next available row:
SELECT id
FROM jobs
WHERE status = 'queued'
ORDER BY id
LIMIT 1
FOR UPDATE SKIP LOCKED;
Each worker can now lock a different queued row. The claim transaction should be short: select the row, update it to running, and commit before doing the actual work. Do not hold the row lock for the duration of the job. Add a composite index (status, id) so the ordered lookup is efficient; the existing idx_status(status) alone still requires sorting by id.
2. What isolation-level and locking considerations matter for the claim transaction, and what happens if the job itself is long-running?
Read the worked answer
Under the default REPEATABLE READ, a locking read can take next-key locks and gap locks on the status index, which increases contention beyond the one row being claimed. For a queue claim, READ COMMITTED is often preferable because it reduces gap locking for this transaction, while the explicit FOR UPDATE SKIP LOCKED still protects the selected row. Set it for the claim transaction only, not globally without testing.
SKIP LOCKED applies to locking reads, not to ordinary consistent reads. A plain SELECT can still see the same queued row, so the claim and the status change must be in the same transaction. The update should include a guard such as WHERE id = ? AND status = 'queued' and the application should check the affected-row count.
If the job runs for minutes, do not keep the claim transaction open. Commit the claim, then run the job. For long jobs, write a heartbeat to updated_at periodically. The supervisor can requeue rows whose heartbeat is stale, but it must use a conditional update so it does not overwrite a job that just heartbeated.
3. How should the supervisor recover crashed workers without running the same job twice?
Read the worked answer
A simple time-based requeue is racy: the supervisor may see an old updated_at just as the original worker finishes, then set the row back to queued while the original worker is still executing. That produces duplicate execution.
Use a lease or fencing token. Add a run_token column, set it to a new UUID when a worker claims the job, and require the worker to include that token in every completion, failure, or heartbeat update:
UPDATE jobs
SET status = 'done', updated_at = NOW()
WHERE id = ? AND run_token = ? AND status = 'running';
If the supervisor has already requeued the job and a new worker changed the token, the old worker's update affects zero rows. The old worker must then stop or treat its result as stale. The supervisor's requeue should also be conditional, for example only when status = 'running' and updated_at < NOW() - INTERVAL 5 MINUTE, and it should increment an attempt counter.
For true safety, make the job handler idempotent or enforce a unique deduplication key in the downstream system. A queue can reduce duplicate execution, but exactly-once delivery requires cooperation from the work being performed.