A job queue on FOR UPDATE SKIP LOCKED
The pattern that used to require Redis: a correct, crash-safe job queue in plain SQL. MySQL 8.0 added the missing keyword — per the manual, "a locking read that uses SKIP LOCKED never waits to acquire a row lock. The query executes immediately, removing locked rows from the result set."
That's the whole trick: a claimed job is a locked row, and competing workers don't see it. No claimed_by column to reset after crashes, no heartbeat table — the row lock is the claim, and it lives exactly as long as the worker's transaction:
Two workers run the same loop: claim the oldest queued job, do the work, mark it done, commit.
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 finishes and commits. B crashes mid-job — its claim evaporates with its transaction.
A> UPDATE jobs SET state = 'done' WHERE id = 1;
Query OK, 1 row affected
A> COMMIT;
Query OK
B> ROLLBACK;
Query OKA restarted worker finds job 2 right back in the queue — nothing was lost, nothing ran twice.
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.10 · Run it yourself · Scenario source
Why each piece matters
Every keyword in that claim query is load-bearing. FOR UPDATE is the claim itself: the row stays visible to plain SELECTs, since readers work off the snapshot, but any competing locking read has to reckon with the lock. SKIP LOCKED is the part that refuses to wait — drop it and worker B queues up behind worker A in the lock queue instead of grabbing the next job, and your queue serializes down to one effective worker. The ORDER BY id LIMIT 1 is fairness and determinism: oldest job first.
Crash safety comes for free. B's "crash" in the transcript is nothing more than a ROLLBACK, and because the claim was a lock, it evaporated with the transaction and the job dropped straight back into the queue — the same guarantee PostgreSQL gives you.
The manual's warning — "Queries that skip locked rows return an inconsistent view of the data. SKIP LOCKED is therefore not suitable for general transactional work. However, it may be used to avoid lock contention when multiple sessions access the same queue-like table" — is exactly the design here: for a queue, not seeing claimed jobs isn't inconsistency, it's the point.
One InnoDB-specific note: run workers at READ COMMITTED if you see deadlocks between claimers — at REPEATABLE READ the locking read can take gap locks on the index ranges it scans, and two workers' ranges can overlap.
The whole worker fits in one transaction: claim with SELECT … WHERE state = 'queued' ORDER BY id LIMIT 1 FOR UPDATE SKIP LOCKED, do the work, UPDATE … SET state = 'done', and COMMIT. Keep that transaction short, because the claim holds a row lock for its entire life, and a worker that wanders off mid-job is a job nobody else can touch until it commits or dies. Crash safety is the payoff for that discipline: a dead worker's claim was only ever a lock, and locks don't outlive their transaction.