Building a Reliable Job Queue on MySQL with SKIP LOCKED

Not every application needs Redis or SQS to run background work. Plenty of PHP apps process jobs out of a plain database table, and that setup holds up surprisingly well when you get the locking right. The piece people get wrong is the part where several workers reach for the same table at once. Do it naively and your workers either grab the same job twice or grind to a halt fighting over locks. MySQL has had the fix for this since version 8.0.1, and if you run Laravel you are almost certainly using it already without knowing.

The tool is SKIP LOCKED, a modifier on locking reads. It turns a queue-like table into something multiple workers can drain in parallel with no coordination between them.

The problem SKIP LOCKED solves

Picture a jobs table and three worker processes. Each worker wants to claim the next available job, mark it as taken, and run it. The obvious first attempt is a locking read inside a transaction:

START TRANSACTION;
SELECT * FROM jobs WHERE reserved_at IS NULL ORDER BY id LIMIT 1 FOR UPDATE;
-- mark it reserved, then COMMIT

SELECT ... FOR UPDATE sets an exclusive lock on the rows it reads, the same lock an UPDATE would set. That protects a row from being claimed twice, which is good. The trouble is what happens to worker two and worker three. They run the same query, hit the row worker one already locked, and block. They sit there waiting for the first transaction to commit or roll back. Your parallel workers have quietly become serial, and under load you start collecting lock wait timeouts and the occasional deadlock.

You do not want the other workers to wait. You want them to shrug, skip the locked row, and grab the next free one. That is exactly what SKIP LOCKED does.

How SKIP LOCKED behaves

MySQL 8.0.1 added two modifiers to locking reads, NOWAIT and SKIP LOCKED. According to the MySQL reference manual, a locking read with SKIP LOCKED never waits to acquire a row lock. It executes immediately and removes locked rows from the result set. NOWAIT is the stricter sibling: it also refuses to wait, but instead of skipping it fails right away with an error if any requested row is locked.

The manual’s own example makes the difference concrete. Session 1 opens a transaction and locks the row where i = 2:

-- Session 1
START TRANSACTION;
SELECT * FROM t WHERE i = 2 FOR UPDATE;

Session 2 asks for that same row with NOWAIT and is turned away immediately:

-- Session 2
SELECT * FROM t WHERE i = 2 FOR UPDATE NOWAIT;
-- ERROR 3572 (HY000): Do not wait for lock.

Session 3 asks for everything with SKIP LOCKED and simply gets back the rows that are free:

-- Session 3
SELECT * FROM t FOR UPDATE SKIP LOCKED;
-- returns 1 and 3, the locked row 2 is absent

That is the whole trick. Point three workers at the same queue table with FOR UPDATE SKIP LOCKED and each one pulls a different free job. No blocking, no fighting.

The caveats worth knowing

SKIP LOCKED is not a general purpose tool, and the manual is blunt about why. Because it hides rows that are locked, a query using it returns an inconsistent view of the data. That is fine for a work queue, where “give me any job nobody else is holding” is precisely the semantics you want. It is wrong for reporting or anything that needs to see the full, consistent state of a table. Reach for it only on queue-like access patterns.

A few other constraints matter in practice. Locking reads only work with autocommit disabled, so you need an explicit transaction. NOWAIT and SKIP LOCKED apply to row-level locks only. And statements using either modifier are unsafe for statement-based replication, so if you still run that format, be aware. Row-based replication, the modern default, is fine.

Doing it in PHP

With PDO the pattern is a transaction that claims a job, then a separate update to reserve it:

$pdo->beginTransaction();

$stmt = $pdo->query(
    'SELECT id, payload FROM jobs
     WHERE reserved_at IS NULL
     ORDER BY id
     LIMIT 1
     FOR UPDATE SKIP LOCKED'
);

$job = $stmt->fetch(PDO::FETCH_ASSOC);

if ($job === false) {
    $pdo->commit(); // nothing to do
} else {
    $update = $pdo->prepare(
        'UPDATE jobs SET reserved_at = NOW() WHERE id = ?'
    );
    $update->execute([$job['id']]);
    $pdo->commit();

    // process $job['payload'] outside the transaction
}

Keep the transaction short. Claim the row, mark it reserved, commit. Then do the actual work outside the lock so you are not holding a row lock for the length of a slow job. If your worker crashes mid-job, a reaper that resets rows whose reserved_at is older than some threshold will put them back in play.

Laravel already does this for you

If you use Laravel’s database queue driver, this is not something you build. The framework’s queue code applies FOR UPDATE SKIP LOCKED when the underlying database supports it. The version check it performs is instructive, because it captures exactly which engines qualify: MySQL 8.0.1 and later, MariaDB 10.6 and later, and PostgreSQL 9.5 and later all get FOR UPDATE SKIP LOCKED. SQL Server gets its equivalent, the with(rowlock,updlock,readpast) table hint, since READPAST is the SQL Server name for the same idea. On anything older, Laravel falls back to plain locking so it stays correct, just less concurrent.

This is why the database queue driver scales to multiple workers gracefully. Each queue:work process claims its own jobs and never blocks on another worker’s in-flight row.

At the query builder level there is no dedicated skipLocked() helper yet, despite a long-standing community request. If you want the behavior in your own Eloquent or query builder code, pass the lock string directly:

$job = DB::table('jobs')
    ->whereNull('reserved_at')
    ->orderBy('id')
    ->limit(1)
    ->lock('FOR UPDATE SKIP LOCKED')
    ->first();

Wrap that in DB::transaction() and you have the same pattern the framework uses internally, available for a custom table that is not a Laravel queue.

When to use it

Database-backed queues are not the answer for every workload. If you are pushing hundreds of thousands of jobs a minute, a purpose-built broker earns its keep. But for the many apps handling a steady trickle of emails, exports, and webhooks, a jobs table you can already back up, query, and reason about is a perfectly good choice. SKIP LOCKED is what makes it safe to run more than one worker against it. It is a small piece of SQL that removes a whole category of concurrency bugs, and it has been sitting in MySQL, quietly powering your Laravel queue, since 2017.