A queue table is not a queue

The complaint was that one customer had received the same order confirmation eleven times. The job had run once, been picked up by two workers, failed partway through on both, and been retried. Every part of that was a consequence of the queue being a table and the claim being a read followed by an update.

The symptom

$ mysql -Nse "SELECT id, attempts, reserved_at FROM jobs
    WHERE payload LIKE '%91204%'"

8841  6  2018-05-14 09:12:04

$ grep 'SendReceipt.*91204' /var/log/app/queue.log | wc -l
11

$ grep 'SendReceipt.*91204' /var/log/app/queue.log | head -2
[09:12:04] worker-1 started  job=8841
[09:12:04] worker-2 started  job=8841      ← same job, same second

Two workers, one row, the same second. The attempt counter said six because each worker had incremented it independently and the retries had done the same. Nothing in the schema prevented any of it.

Why it happens

// the claim, as almost everybody writes it first
$job = DB::table('jobs')
    ->whereNull('reserved_at')
    ->orderBy('id')
    ->first();

if ($job) {
    DB::table('jobs')->where('id', $job->id)->update(['reserved_at' => now()]);
    $this->handle($job);
}

Read, then write. Two workers can both complete the read before either completes the write, and both proceed. This is the check-then-act race in its purest form, and it is invisible with one worker and constant with four.

The obvious fix — wrapping it in a transaction with FOR UPDATE — is correct and serialises the workers: the second one blocks on the row the first is holding, waits, and then finds it reserved. Adding workers adds no throughput at all, which is a different failure and one that takes longer to notice.

The fix

SKIP LOCKED, which needs 8.0

START TRANSACTION;

SELECT id, payload FROM jobs
WHERE reserved_at IS NULL
ORDER BY id
LIMIT 1
FOR UPDATE SKIP LOCKED;

UPDATE jobs SET reserved_at = NOW(), attempts = attempts + 1
WHERE id = ?;

COMMIT;
-- then do the work, OUTSIDE the transaction

SKIP LOCKED passes over rows another transaction is holding rather than waiting for them, so each worker claims a different job and the pool scales. It arrived in MySQL 8.0 in April, which is the only reason this design is worth considering at all this year — before that the table version could be correct or concurrent, not both.

The transaction has to stay short, and the temptation is to do the work inside it so that a crash rolls the reservation back. That recreates the serialisation, because the lock is held until commit. Reserving in a short transaction and recovering stalled reservations separately is the arrangement that works.

-- the recovery, on a timer
UPDATE jobs SET reserved_at = NULL
WHERE reserved_at < NOW() - INTERVAL 5 MINUTE
  AND completed_at IS NULL AND attempts < 5;

-- and the ones that have run out
UPDATE jobs SET failed_at = NOW()
WHERE attempts >= 5 AND completed_at IS NULL AND failed_at IS NULL;

Five minutes is a threshold to choose rather than copy: too short and a slow but healthy worker has its job taken and done twice, which is only survivable because handlers are idempotent. The attempt ceiling is what stops a permanently failing job cycling forever, and moving it to a failed state rather than deleting it is what makes it recoverable by a human.

Idempotency, because at-least-once is the only guarantee

Every arrangement in this article delivers at least once. A worker can complete the work and die before marking the row done, and the recovery will hand it to somebody else. The handler has to survive that.

public function handle(int $orderId): void
{
    // the unique index arbitrates, not a SELECT
    try {
        DB::table('receipts_sent')->insert(['order_id' => $orderId]);
    } catch (QueryException $e) {
        if ($this->isDuplicateKey($e)) {
            return;                  // already sent. done.
        }

        throw $e;
    }

    Mail::to($this->emailFor($orderId))->send(new Receipt($orderId));
}

The insert has to come before the side effect, not after — a receipt recorded after a successful send is a receipt that is not recorded when the process dies between the two. Recording first means a crash after the insert loses one email, which is the better failure. There is no ordering that gets both, which is the honest summary of exactly-once delivery.

What the table still cannot do

With SKIP LOCKED and a recovery timer the table is correct and concurrent, and it is still missing things a broker gives you for free.

a table gives you      a broker gives you
durability             durability
queryability           blocking receive (no polling)
one transaction with   delayed delivery, priorities
  the business data    a bury / dead-letter state

Polling is the one that costs continuously. A worker checking every second is 86,400 queries a day that usually return nothing, per worker, and the latency floor is the poll interval. A blocking receive has neither problem.

The item in the left column that keeps the table honest is the first: a job inserted in the same transaction as the row that caused it either both happen or neither does. That is the outbox pattern, and it is the one genuinely good reason to keep jobs in the database — everything else is an argument for a broker.

Beanstalkd, which is the smallest thing that is actually a queue

For a worker pool of two or three processes, the honest alternative is not RabbitMQ. Beanstalkd is a single binary with no configuration file that models the lifecycle explicitly.

$job = $queue->reserveWithTimeout(5);   // mine for TTR seconds

try {
    $this->handle($job->getData());
    $queue->delete($job);               // done, gone
} catch (Transient $e) {
    $queue->release($job, 1024, 60);    // back to the tube in 60s
} catch (Throwable $e) {
    $queue->bury($job);                 // set aside for a human
}

A reserved job returns to the ready queue automatically when its time-to-run expires, which is the recovery timer built into the server. Buried jobs sit in their own state until somebody kicks them back — a dead letter queue as a first-class concept rather than a convention. The distinction between release and bury is the one that matters in a handler, and it maps directly onto transient versus permanent failure.

queue:
  image: schickling/beanstalkd
  command: ["-b", "/data", "-f", "1000"]   # binlog on, 1s fsync
  volumes: [beanstalk:/data]

Verifying it worked

$ php artisan seed:jobs 10000
$ for i in 1 2 3 4; do php artisan queue:work & done

$ mysql -Nse 'SELECT COUNT(*), SUM(attempts) FROM jobs WHERE completed_at IS NOT NULL'
10000  10000               # one attempt each. no duplicates.

# and the crash test
$ kill -9 $(pgrep -f 'queue:work' | head -1)
$ sleep 310 && mysql -Nse 'SELECT COUNT(*) FROM jobs WHERE completed_at IS NULL'
0

The sum of attempts equalling the row count is the assertion that says no job was claimed twice — a stronger check than counting completions, because a double claim that happens to produce the right side effect still shows up here. The kill -9 is the other half: a worker that dies mid-job leaves a reservation that nothing releases, and the only way to know the recovery works is to cause the failure deliberately.

What this costs

The table version costs a MySQL 8.0 dependency and a polling loop, and it buys transactional consistency with the business data. The broker version costs another daemon to run, monitor and back up, and it buys blocking receives, delays, priorities and a dead-letter state. Neither is free and the choice is genuinely about which of those two lists matters more for the workload in front of you.

What is not defensible is the version this started with. A read followed by an update is broken under any concurrency, it looks correct, and it will pass every test written with one worker. If the table is staying, SKIP LOCKED is not optional — and if the database is older than 8.0, the table cannot be made correct and concurrent at the same time, which settles the argument by itself.