+1 (541) 238-9429
QueryKings

Job queues and outbox tables in MySQL: SKIP LOCKED, and the failure modes it does not fix

A recurring conversation on performance reviews: a team is running a job queue, a transactional outbox, or both, inside their MySQL instance, and the queue table has become the hottest table in the schema. The usual symptoms are lock wait timeouts on a table with ten thousand rows, replicas lagging behind a workload no one would call heavy, and a DELETE that mysteriously takes longer than the jobs themselves.

The pattern itself is sound. Keeping the queue in the database buys you one thing nothing else does: the job and the business write commit in the same transaction, so you can never publish an event for an order that rolled back. That is the whole argument for the transactional outbox, and it is a good one. What goes wrong is almost always in the claim mechanics.

What SKIP LOCKED actually does

MySQL 8.0 added SKIP LOCKED and NOWAIT to locking reads. The relevant one here:

SELECT id, payload
FROM jobs
WHERE status = 'ready' AND run_after <= NOW()
ORDER BY run_after
LIMIT 10
FOR UPDATE SKIP LOCKED;

InnoDB walks the access path as usual, and any row it would have had to wait on is simply omitted from the result. No wait, no timeout, no error. Ten workers running that statement concurrently get ten disjoint batches instead of nine of them queueing behind the first.

Two things about that are easy to get wrong.

The result is non-deterministic, by definition. SKIP LOCKED returns an inconsistent view of the table — the manual says so outright. That is fine for a queue, where you only need some available work, and wrong for anything where you need to know what the table contained. Do not reach for it to speed up a report.

It skips locked rows, not rows you filtered out. The skipping happens on index records the scan touches. If the access path makes the scan read a thousand rows to find ten eligible ones, you pay for a thousand rows of lock inspection on every poll, from every worker. The index is doing the real work here; SKIP LOCKED only removes the waiting.

Before 8.0, the common workarounds were an UPDATE ... LIMIT to stamp a worker ID followed by a SELECT of the stamped rows, or a serialized claim through a single advisory lock. Both still work. Neither is better than SKIP LOCKED on 8.0, and the UPDATE-then-SELECT approach serializes the claim step under the same next-key locks you were trying to avoid.

The index is the design

A queue table is a narrow, high-churn table where the same few index pages are being read, locked, and dirtied continuously. Give the claim query an index that resolves it exactly:

ALTER TABLE jobs
  ADD INDEX idx_claim (status, run_after, id);

With that index, the claim scans only the ready prefix in run_after order and stops at the limit. Without it — with, say, an index on run_after alone — every poll scans historical rows in the wrong status, inspects their lock state, and discards them. We have seen that single difference account for a 40x reduction in queue-table CPU on an otherwise unchanged workload.

Three more schema points that matter more than they look:

  • Keep the payload out of the hot path, or out of the table. A LONGTEXT payload on the claim row makes every claim read off-page data it may not use. Either select only the key columns in the claiming statement and fetch the payload by primary key afterwards, or move payloads to a separate table.
  • Use a monotonic primary key. A queue table is the one place where AUTO_INCREMENT versus random UUID has a visible, measurable cost: random keys scatter inserts across the B-tree and split pages continuously in exactly the table that is being inserted into hardest. (We wrote up the general case separately; the queue table is where it bites first.)
  • Do not add indexes you only use for the admin dashboard. Each one is another structure to maintain on every insert, claim, and delete, on a table doing that thousands of times a minute.

Claims need a lease, not a lock

A row lock lives and dies with the transaction. If your worker holds the transaction open while it does the job — calls an API, renders a PDF, sends mail — you have reinvented the long-running transaction, with all of it: blocked purge, a growing history list, replicas waiting on a single enormous event group.

The shape that works keeps the lock short and makes ownership a column:

START TRANSACTION;

SELECT id FROM jobs
WHERE status = 'ready' AND run_after <= NOW()
ORDER BY run_after LIMIT 10
FOR UPDATE SKIP LOCKED;

UPDATE jobs
   SET status = 'claimed',
       owner = @worker_id,
       lease_until = NOW() + INTERVAL 60 SECOND,
       attempts = attempts + 1
 WHERE id IN (...);

COMMIT;

The transaction now lasts a millisecond or two. The work happens outside it. Ownership is durable state, which means a crashed worker's jobs are recoverable: a reaper moves rows whose lease_until has passed back to ready, and long jobs extend their own lease while they run.

The cost of this design, stated plainly: you get at-least-once delivery, not exactly-once. A worker can finish a job, fail before committing the completion, and have the job reclaimed after the lease expires. There is no arrangement of locks that removes this — it is a distributed systems property, not a MySQL one. Make the handlers idempotent, usually with a unique key on some natural idempotency token, and stop trying to solve it in the database.

Polling has a price, and it is mostly deletes

Two background costs dominate a healthy queue table.

Empty polls. Twenty workers polling every 100 ms is 200 queries per second against the hottest index in the schema whether or not there is any work. That is survivable, but it is pure overhead, and it also means your slow query log and digest tables are dominated by a statement that does nothing. Back off when the queue is empty — exponential, capped at a second or two — or push a wake-up signal from the producer side. If you need sub-50 ms dispatch latency with a deep backlog, a database table is the wrong substrate and a broker is the right one.

Completed rows. The queue table's steady-state size should be the backlog, not the history. Teams routinely leave finished rows in place and then discover the claim index has millions of dead entries and the nightly cleanup DELETE is a 2-million-row transaction that stalls replication for minutes. Two habits fix it:

  • Delete in chunks — a few thousand rows per transaction, driven by primary key ranges, with the loop pausing when replica lag rises.
  • Archive history to a separate table if you need it for audit. A DELETE-only hot table keeps its B-tree small enough to stay resident in the buffer pool, which is the actual goal.

Also watch purge. High-churn delete workloads generate undo faster than almost anything else, and if a long reporting transaction on a replica is holding the read view open, the history list grows and your small queue table quietly stops being small.

When to move the queue out of MySQL

We are not purists about this, and "just use a broker" is cheap advice when it means running and operating one more stateful system. The honest thresholds, from the engagements we have done:

  • Under a few hundred jobs per second, with a backlog that drains: MySQL is fine. Index it properly, lease the claims, delete the history.
  • Sustained thousands per second, or fan-out to many consumer groups, or a need for strict ordering per key: you are building a broker inside InnoDB. Move the dispatch to a broker and keep the outbox in MySQL — the outbox only needs to survive until a relay process reads the row and publishes it, which is a much lighter table than a full queue.
  • Anything requiring delivery latency below the poll interval at high depth: move it.

The outbox half is worth keeping in the database longer than the queue half, because that is where the transactional guarantee lives. Splitting the two is usually a smaller change than teams expect.

A short checklist

  1. One composite index that exactly serves the claim predicate and ordering.
  2. FOR UPDATE SKIP LOCKED on 8.0, with LIMIT, in a transaction that commits in milliseconds.
  3. Ownership and lease_until as columns; a reaper for expired leases; idempotent handlers.
  4. Backoff on empty polls.
  5. Chunked deletes, replica-lag-aware, so the hot table stays small.
  6. A dashboard with queue depth, oldest run_after, claim-query latency, and reclaim count. Depth and age tell you different things, and you want both.

If your queue table is already the top entry in events_statements_summary_by_digest and you would rather not rebuild it blind, that is a well-bounded piece of work. Send the table definition, the claim statement, and a day of digest output; the shape of the problem is usually clear from those three.