Back to Blog
Cost Optimization Cloud Infrastructure Strategy AWS

Skip the Redis Cluster: Job Queues and Reservations with SQL SKIP LOCKED

Adding Redis for a queue or reservation table costs you a cache bill and a consistency problem. Here's the SKIP LOCKED pattern that keeps both in one database.

Skip the Redis Cluster: Job Queues and Reservations with SQL SKIP LOCKED

Two weeks after launch, support forwards a customer email: they booked the last slot, got a confirmation, then got a cancellation. Nobody can reproduce it. The shape is familiar — the reservation lives in Redis, the booking lives in the database, and no transaction spans the two. Sometimes one write lands and the other doesn’t. Ninety-nine times out of a hundred nobody notices. (A composite, not one engagement — it’s a shape we’ve walked into more than once.)

That bug wasn’t introduced by a bad commit. It arrived the day someone added a second source of truth — the default reflex for anything queue-shaped: reservations, job runners, distributed locks, rate counters. Add Redis. It’s fast, everyone knows it, the console makes it a five-minute job.

What gets skipped in those five minutes: a cache cluster is two costs. The line item is the obvious one. The expensive one is the distributed-consistency problem you now own forever — sagas, compensating writes, reconciliation jobs, or quietly living with drift.

For a large fraction of the workloads we see, one ACID datastore is both cheaper and more correct.

The proof point runs at a scale you don’t have

The usual objection is throughput: a database can’t take that write volume. Shopify published a counterexample in May 2026, moving inventory reservations out of Redis and into MySQL. The trigger was a unified database strategy, not a performance complaint — but the argument they make along the way is worth stealing:

Reservations and the inventory ledger lived in two different systems.

Because those two writes couldn’t be wrapped in a single atomic step, the ordering could produce overselling (“item sold but was never deducted from the ledger”) or underselling (“item deducted and still marked reserved”). Note the tense — that’s the failure mode the split buys you, not an incident report.

Their fix was MySQL 8’s SKIP LOCKED, restructuring the data as one row per sellable unit instead of one row per item, with a pool “capped at 1,000 per item/location combination” — contention spreads across a thousand rows instead of one hot key.

The numbers need their context. Black Friday 2025 peaked at “a record $5.1 million in sales per minute” — platform-wide GMV across a sharded fleet, not one database’s throughput, and the cutover rolled pod by pod. Separately, during flash sales, “writer CPU stayed under 50% and reader CPU under 16%, with headroom to spare.”

Read that CPU figure as an outcome, not a property of the pattern. After the redesign Shopify still hit a throughput ceiling below target, and the bottleneck was neither CPU nor query design — it was connection exhaustion. Reaching sub-50% took work unrelated to SKIP LOCKED: tagging every SQL statement with its business process so connection hold time could be attributed at the proxy layer, then removing 50% of reads and 33% of transactions on the primary in checkout code with no obvious link to inventory, and raising InnoDB thread concurrency.

So SKIP LOCKED removes the lock convoy. It does not remove whatever else on your primary holds connections open, and at scale that’s what bites. The ceiling is further out than the reflex assumes — but you reach it by measuring, not by adopting a pattern.

What SKIP LOCKED actually does

A normal SELECT ... FOR UPDATE blocks when another transaction holds the row lock. With ten workers polling one queue table, nine sit in a lock queue and you’ve built a serial system with a concurrency bill.

SKIP LOCKED inverts that. Per the MySQL 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.” Each worker grabs a disjoint set of rows and moves on. No lock convoy, no broker. None of it is novel — Shopify credits 37signals’ Solid Queue as the inspiration; database-backed queues are well-trodden ground.

A job queue in MySQL:

START TRANSACTION;

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

-- claim the ids you just read
UPDATE jobs
   SET status = 'running', locked_at = NOW(), worker_id = ?,
       attempts = attempts + 1
 WHERE id IN (/* ids from above */);

COMMIT;

PostgreSQL does it in one statement — the version we reach for given the choice:

UPDATE jobs
   SET status = 'running', locked_at = now(), worker_id = $1,
       attempts = attempts + 1
 WHERE id IN (
   SELECT id
     FROM jobs
    WHERE status = 'pending'
      AND run_after <= now()
    ORDER BY run_after
    LIMIT $2
      FOR UPDATE SKIP LOCKED
 )
RETURNING id, payload;

Workers die, and nothing above notices. A row whose worker was OOM-killed mid-job sits in running forever. The queue needs its own reaper:

UPDATE jobs
   SET status = IF(attempts >= 5, 'failed', 'pending'),
       worker_id = NULL, locked_at = NULL
 WHERE status = 'running'
   AND locked_at < NOW() - INTERVAL 10 MINUTE;

Without attempts, a job that reliably kills its worker is retried forever.

The reservation variant is the same shape, scaled down:

CREATE TABLE inventory_units (
  item_id        BIGINT UNSIGNED NOT NULL,
  location_id    BIGINT UNSIGNED NOT NULL,
  unit_seq       INT UNSIGNED    NOT NULL,
  reserved_by    CHAR(36)        NULL,
  reserved_until DATETIME        NULL,
  PRIMARY KEY (item_id, location_id, unit_seq)
) ENGINE=InnoDB;

The primary key is the interesting line. The obvious schema — an AUTO_INCREMENT id plus a secondary index on (item_id, location_id) — is the one Shopify moved away from: claiming through a secondary index takes two row locks per reservation, one on the index entry, one on the clustered row. Leading the primary key with the columns you filter on gets it down to one.

Claim units, write the ledger entry, commit — one transaction, no seam to drift across:

START TRANSACTION;

SELECT item_id, location_id, unit_seq
  FROM inventory_units
 WHERE item_id = ? AND location_id = ? AND reserved_by IS NULL
 LIMIT ?
   FOR UPDATE SKIP LOCKED;

UPDATE inventory_units
   SET reserved_by = ?, reserved_until = NOW() + INTERVAL 15 MINUTE
 WHERE (item_id, location_id, unit_seq) IN (/* keys from above */);

INSERT INTO inventory_ledger (item_id, location_id, delta, reason, ref)
VALUES (?, ?, -?, 'reservation', ?);

COMMIT;

That last INSERT is the whole argument. With Redis holding the reservation, it lives in another process with another failure mode and no shared commit. Here it all lands or none of it does.

You still need a sweeper for reserved_until expiry — a cron or scheduled Lambda that nulls reserved_by on rows past their hold. The one piece Redis gives you free with a TTL; about fifteen lines. Running it from Lambda against RDS? Our walkthrough on executing database queries from Lambda to an RDS instance covers the plumbing.

The caveats, straight from the manuals

It is deliberately inconsistent. MySQL: “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.” PostgreSQL’s SELECT docs use near-identical wording.

Queue-like access is the sanctioned use. Don’t use SKIP LOCKED to paper over contention in a reporting query or general read path — you’ll get silently missing rows.

Gap locks will ambush you on an empty pool. The caveat that actually bites in the reservation shape. Under InnoDB’s default REPEATABLE READ, a claim that finds no free rows still takes gap locks — including on the supremum record — blocking the very inserts that would replenish the pool, and deadlocking. Shopify fixed it by running those transactions at READ COMMITTED, which takes no gap locks. If your pool refills on demand, budget for this before you ship.

Replication format matters — and READ COMMITTED settles it. MySQL: “Statements that use NOWAIT or SKIP LOCKED are unsafe for statement based replication.” A non-issue on modern defaults — binlog_format defaults to ROW and is deprecated — and InnoDB at READ COMMITTED requires row-based logging anyway. Confirm if you inherited the database.

Take your tables in the same order everywhere. The reservation transaction touches inventory_units, then inventory_ledger. Every other path touching both — a release, an adjustment, a refund — must use that order, or two healthy code paths deadlock. Shopify’s deadlocks came from exactly this, between reserve and claim.

Ordering is best-effort. Workers skip contended rows, so strict FIFO isn’t guaranteed. If you need ordered processing per key, partition by that key and run one consumer per partition.

Bound the pool. Unbounded rows-per-item turns the claim into a scan; Shopify’s 1,000-row cap exists for a reason.

Version floors. SKIP LOCKED needs MySQL 8.0+ or PostgreSQL 9.5+; the feature is rarely the constraint, your support window is. MySQL 8.0 moved to sustaining support on April 21, 2026, and Oracle’s notice tells those users to “upgrade to MySQL 8.4 LTS or 9.7 LTS.” On AWS, Aurora MySQL 8.4 went GA on May 21, 2026, compatible with community MySQL 8.4.7 and with version numbers now aligned to community releases — there is no “version 4”; version 3 is the older 8.0-compatible branch. Plan new work on 8.4. On PostgreSQL, every supported major has had SKIP LOCKED for a decade: 14 through 18, with 14 dropping out November 12, 2026.

Where Redis still earns its keep

This is an argument against a reflex, not against Redis.

Redis is the right answer when the data genuinely isn’t your source of truth: real caching, pub/sub fan-out, edge rate limiting where a millisecond of latency is the whole point, session storage you’re happy to lose, leaderboards and counters where the data structure is the feature. If you already run it for caching, a queue on top is a small marginal cost and the argument here mostly evaporates.

The economics have moved, which matters if you’re pricing this. Valkey is a fork of Redis, not a rename — it came out of the 2024 license change and now lives under the Linux Foundation — and AWS offers ElastiCache for Valkey alongside Redis OSS. Per AWS, node-based Valkey is “20% lower cost per node”; Serverless goes further, with “33% reduced pricing and a minimum data storage of 100 MB, which is 90% lower than ElastiCache Serverless for Redis OSS.” For a small queue that storage floor is usually the bigger number: a queue that never holds a gigabyte was paying for one anyway. Adding a cluster on purpose? Price the Valkey variant. Adding one out of habit? Price not adding it.

The threshold we actually use

Before adding a second datastore for queue-shaped work, we ask three questions:

  1. Does this data need a transaction with something already in the database? If yes — reservations, balances, ledgers, anything with a seam that can drift — keep it there. That decides most cases alone.
  2. Is the write rate within an order of magnitude of what the primary already handles? If your queue peaks at a few hundred claims per second and the database sits at 20% CPU, you don’t have a throughput problem. You have a habit.
  3. Are we already running Redis for something else? If yes, the marginal cost is a config line. If no, you’re buying a cluster, an HA story, a failover runbook, monitoring, and a consistency model.

Two “no”s and a “yes” to the first, and one datastore wins on both axes. The cheapest infrastructure is the kind you never provision — the logic behind most of the wins in our AWS cost-cutting checklist, except this is an architecture decision rather than a knob, so it compounds.

The honest counterweight: if you cross those thresholds later, migrating to Redis is straightforward — you’ll be moving well-understood access patterns out of a system that already told you where the contention is. Untangling two sources of truth after a year of drift is the hard direction. Start where it’s easier to leave.

To try it locally, our Dockerized NestJS + React + MySQL setup gives you a MySQL 8.x to point ten workers at.

Weighing a queue, a reservation table, or a cache cluster you’re not sure you need? Start a conversation — architecture and cost reviews are most of what we do.

Sources / last verified 2026-09-21

Back to Blog