A job queue on FOR UPDATE SKIP LOCKED
This recipe claims a queued row inside a transaction, changes its state, and commits. In the demonstrated MySQL 8.4 InnoDB schedule, worker B skips worker A's locked job and selects the next one. The manual states: "NOWAIT and SKIP LOCKED only apply to row-level locks." They do not remove every possible wait, including metadata-lock waits.
Two sessions select the lowest available queued id under a row lock. Only database job-state changes are executed.
A> BEGIN;
Query OK
A> SELECT id, task FROM jobs WHERE state = 'queued'
ORDER BY id LIMIT 1 FOR UPDATE SKIP LOCKED;
id | task
----+--------------------
1 | send welcome email
(1 row)
B> BEGIN;
Query OK
B> SELECT id, task FROM jobs WHERE state = 'queued'
ORDER BY id LIMIT 1 FOR UPDATE SKIP LOCKED; -- job 1 is claimed — skipped without waiting
id | task
----+------------------
2 | generate invoice
(1 row)A marks job 1 done and commits. B explicitly rolls back, releasing its claim on job 2.
A> UPDATE jobs SET state = 'done' WHERE id = 1;
Query OK, 1 row affected
A> COMMIT;
Query OK
B> ROLLBACK;
Query OKB starts a new transaction and selects job 2 again. No restarted process or external task execution is observed.
B> BEGIN;
Query OK
B> SELECT id, task FROM jobs WHERE state = 'queued'
ORDER BY id LIMIT 1 FOR UPDATE SKIP LOCKED;
id | task
----+------------------
2 | generate invoice
(1 row)
B> UPDATE jobs SET state = 'done' WHERE id = 2;
Query OK, 1 row affected
B> COMMIT;
Query OK
A> SELECT id, state FROM jobs ORDER BY id;
id | state
----+-------
1 | done
2 | done
(2 rows)Verified against MySQL 8.4.11 · Run it yourself · Scenario source
Why each piece matters
FOR UPDATE takes the row lock used as the claim. SKIP LOCKED excludes locked rows from this locking read. ORDER BY id LIMIT 1 selects the lowest eligible id among the rows the query can return. Skipped jobs can be selected later; this does not prove fairness or prevent starvation.
B's failure here is an explicit ROLLBACK, not a killed process or a database crash. After that rollback B can select job 2 again, and both jobs finish in state done. The task strings mention email and invoices, but no email or invoice service is called. A repeated selection does not establish exactly-once execution of external work.
Entailed guarantee†: cooperating workers that use this locking claim and update database-local work and job state in the same transaction keep those database effects inside one commit boundary. Rollback removes those changes and releases the transaction's locks. † This follows from InnoDB locking reads and rollback; the transcript executes one rollback schedule, not all failure paths.
Keep the transaction short. A stalled transaction can retain its claim until it ends; process death does not specify how quickly the server detects a lost connection. For work outside the database, design retry and deduplication separately, as in the outbox lesson.
The manual's locking-read discussion limits SKIP LOCKED to cases that tolerate an incomplete view, such as queue-like tables. Query plans and indexes also affect which locks are taken. If claimers deadlock, inspect their lock footprints and retry the deadlock victim. READ COMMITTED changes gap-lock behavior; it is not a promise of deadlock-free workers.