The slowest query on your queue box is not slow. I learned that staring at a jobs table that could not push more than about 300 claims per second even though each individual claim query measured under two milliseconds. We scaled the worker fleet from 4 processes to 40, and throughput went down. The workers were not queueing on jobs. They were queueing on each other's row locks, thirty-something sessions deep, all stacked behind whichever worker had grabbed the first pending row. The database was, in effect, running the entire fleet single-threaded through one lock.
This is the queue pattern done right on MySQL 8.0 and InnoDB: what SELECT FOR UPDATE SKIP LOCKED actually changes, the index that makes the claim cheap, the isolation-level trap that still bites under REPEATABLE READ, what to do with finished rows, and how to watch the whole thing in performance_schema.
Why does plain SELECT FOR UPDATE serialize workers?
Plain FOR UPDATE serializes workers because every claim query targets the same row. The classic claim is SELECT id FROM jobs WHERE status = 'pending' ORDER BY priority DESC, id LIMIT 1 FOR UPDATE. Given identical data, that query is deterministic: every worker picks the same first pending row. The first worker takes its exclusive lock and goes off to process. Every other worker blocks on that same lock until the first transaction commits, then the survivor re-reads, picks the new first row, and the cycle repeats. Your claim rate collapses to one claim per commit latency, plus lock-wait overhead, no matter how many workers you start.
The failure gets worse when a worker stalls. With innodb_lock_wait_timeout at its default of 50 seconds, a wedged claim holder parks every other worker for most of a minute before they even see an error. From the outside the symptom is bizarre: CPU idle, disk idle, queue depth growing, workers all alive. I have watched teams add workers to fix exactly this, which adds more sessions to the same lock queue and makes it worse. A queue built on plain FOR UPDATE is a mutex wearing a table as a costume.
What do SKIP LOCKED and NOWAIT actually change?
SKIP LOCKED changes the claim from wait-your-turn to take-the-next-one: when a locking read with SKIP LOCKED meets a row it cannot lock, it silently skips that row and keeps scanning for the next candidate. That is exactly queue semantics, because a worker rarely needs a specific job, it needs any eligible job. Forty workers can now sweep the pending set concurrently, each peeling off different rows, and the same fleet that managed 300 claims per second with plain FOR UPDATE went back to roughly 900 per second after the switch, with no other change. The MySQL manual is unusually honest about the trade: SKIP LOCKED can return an inconsistent view of the data and is meant for queue-like access, not general-purpose reads.
NOWAIT is the sibling with different manners: instead of skipping, it fails immediately with error 3572, lock not available. That is the right tool when you need a specific row or nothing, for example claiming a job whose id you already hold, because it lets the caller fail fast and retry elsewhere instead of burning innodb_lock_wait_timeout. For the main claim loop, SKIP LOCKED is the one you want.
One trap catches everyone once: SELECT FOR UPDATE in autocommit mode. The locks release when the statement ends, so the claim evaporates before your worker can mark the row. The claim must live inside an explicit transaction: START TRANSACTION, SELECT ... FOR UPDATE SKIP LOCKED, UPDATE the row by primary key to mark it claimed, COMMIT. Do the actual processing after the commit, guarded by a lease column, or a slow job holds a hot row hostage.
How should the claim query and index be shaped?
The claim index is a composite on (status, priority, id), with priority descending so the ORDER BY resolves straight from the index without a filesort. MySQL 8.0 honors descending index parts, and this is one of the few places I reach for them deliberately:
CREATE TABLE jobs (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
status ENUM('pending','running','done','failed') NOT NULL DEFAULT 'pending',
priority INT NOT NULL DEFAULT 0,
payload JSON NOT NULL,
claimed_by VARCHAR(64) NULL,
lease_until TIMESTAMP(6) NULL,
PRIMARY KEY (id),
KEY claim_idx (status, priority DESC, id)
) ENGINE=InnoDB;The claim itself is two statements in one short transaction. The equality on status plus the descending priority, ascending id ordering means InnoDB walks claim_idx and stops at the first lockable match:
START TRANSACTION;
SELECT id, payload
FROM jobs
WHERE status = 'pending'
ORDER BY priority DESC, id
LIMIT 1
FOR UPDATE SKIP LOCKED;
-- worker then marks exactly the row it won:
UPDATE jobs
SET status = 'running',
claimed_by = 'worker-17',
lease_until = NOW(6) + INTERVAL 5 MINUTE
WHERE id = 48123;
COMMIT;Three details matter. First, always mark by primary key, never by re-running the predicate, or you may update a row you did not lock. Second, keep the transaction short: claim, mark, commit, then process. A worker that processes inside the claim transaction is just a slower version of the serialization problem. Third, the lease_until column is your crash recovery: a reaper job resets status = 'pending' where status = 'running' and lease_until has passed, which rescues jobs whose worker died mid-flight.
Do gap locks still bite under REPEATABLE READ?
Yes, and this is the part most SKIP LOCKED tutorials skip. InnoDB's default isolation, REPEATABLE READ, uses next-key locks: a locking read locks not just the rows it finds but the gaps around the range it scanned. On a queue table, that means a claim can lock the gap where a brand-new job is about to be inserted, and an insert of a new high-priority job can block behind a claim it has nothing to do with. The intermittent symptom is inserts stalling for a second here and there on a table that looks contention-free. I chased that ghost for a week on one system before the deadlock log made it obvious.
The standard fix among people who run queues for a living is READ COMMITTED on the queue workload. Under READ COMMITTED, InnoDB drops gap locking for searches, keeping it only for foreign-key and duplicate-key checks, so claims take record locks only and inserts flow through the gaps. You can set it per session for the workers if you do not want it server-wide. There is one hard prerequisite: READ COMMITTED is rejected for writes when binlog_format is STATEMENT, so the box must run ROW or MIXED binlogging, which you should be doing anyway. The broader menu of gap-lock reduction tricks is covered in this gap-lock patterns note, and the PostgreSQL side of the same queue design is compared in skip-locked queues, MySQL vs PostgreSQL.
Delete or flag: what should happen to finished rows?
My position: delete finished rows promptly and archive them somewhere else if you need the history. The queue table is a hot path, and hot paths should be small. With done rows deleted, the pending set is the whole working set, the claim index stays tiny, backups stay lean, and the claim scan never wades through history. I keep an audit table, jobs_archive, fed by the worker or a trigger, and the queue table itself holds only live work.
Flagging rows done instead of deleting them is not wrong, it is just a different bill. The table grows without bound unless you add a periodic purge job, and every flip from pending to running to done is an update with undo behind it. Deletes have their own cost: they produce undo records that purge must collect, and if some reporting transaction parks on a read view for an hour, purge stalls and the history list grows no matter how aggressively you delete. That interaction is worth understanding before you blame the queue; history list length and purge lag explains the mechanism. Whichever lifecycle you pick, the one unforgivable option is doing nothing: a jobs table that has accumulated two years of finished rows makes every claim pay for archaeology.
How do you watch queue lock contention?
When claims slow down, performance_schema.data_lock_waits tells you who is waiting on whom, and joining it to data_locks and threads gets you from lock to connection in one query:
SELECT rt.processlist_id AS waiting_conn,
bt.processlist_id AS blocking_conn,
rl.object_schema,
rl.object_name,
rl.lock_type,
rl.lock_mode,
rl.lock_data
FROM performance_schema.data_lock_waits AS w
JOIN performance_schema.data_locks AS rl
ON rl.engine_lock_id = w.requesting_engine_lock_id
JOIN performance_schema.data_locks AS bl
ON bl.engine_lock_id = w.blocking_engine_lock_id
JOIN performance_schema.threads AS rt
ON rt.thread_id = w.requesting_thread_id
JOIN performance_schema.threads AS bt
ON bt.thread_id = w.blocking_thread_id;The lock_data column shows which index record is contended; on a healthy SKIP LOCKED queue you should see short, transient waits, not a standing queue behind one lock_data value. The friendlier wrapper is sys.innodb_lock_waits if you want the query text along with the locks, and this lock-waits field guide walks those views in detail. During bring-up of a new queue, I also enable innodb_print_all_deadlocks temporarily so every deadlock lands in the error log, because worker fleets have a gift for finding deadlock cycles you swore were impossible; the resulting triage is easier with the deadlock postmortem workflow. Turn that logging off again once the fleet stabilizes, it is noisy on purpose.
Where MonPG stands on MySQL
I build MonPG, so I will be plain about it: MonPG monitors PostgreSQL today, and MySQL support is still in active development, not shipped. The queue health signals from this note are part of what the MySQL work is meant to surface: standing waits in data_lock_waits, deadlock rates, claim-throughput collapse, and purge lag behind delete-heavy lifecycles, graphed so a serialized fleet is obvious in seconds. The MySQL monitoring (coming soon) page is where that lands as it ships. Until then, the same lock-wait and transaction philosophy already runs on the PostgreSQL side, and the rest of these MySQL field notes live on the blog.