Replace Redis with MySQL (And Know When to Stop): The SKIP LOCKED Pattern

Your checkout flow needs to prevent overselling. Two users hit "Pay" at the same time for the last pair of shoes in a size 9. Classic distributed lock problem.

The instinct is to reach for Redis. Redis is fast, it's built for atomic operations, and every tutorial says so. Shopify used Redis for this exact scenario for years — and then replaced it with MySQL, cutting over gradually behind a dual-write "shadow mode" and a kill switch, pod by pod, starting with the low-traffic ones.

Not because Redis was fast enough and they got bored. Because the Redis design had a correctness hole they could not close, and once they understood the access pattern precisely, MySQL turned out to close it. DoorDash made a structurally similar call with their ML feature store: they replaced a native Redis cluster with Apache Kvrocks — a Redis-protocol-compatible engine backed by RocksDB — and now serve, in their words, "a peak per-second load of over 130M HMGETs for 1.6B retrieved features, within a 50ms P999 latency target."

Different problems, same lesson: Redis clustering is often the default choice, and when you actually question it, you sometimes find a simpler path.

Everything below about Shopify comes from Emilie Noel's write-up on the Shopify engineering blog (May 12, 2026); everything about DoorDash from Luigi Tagliamonte's post (May 18, 2026). Numbers that aren't in those posts aren't in this one either.

The Oversell Problem

Shopify's inventory reservation system has one job: during payment processing, hold inventory so two concurrent checkouts can't claim the same unit. The hold is short — Shopify describes it as "a short hold, e.g. several minutes" — and on Black Friday 2025 merchants on the platform "hit a record $5.1 million in sales per minute at peak." Shopify doesn't publish a reservations-per-second figure, so neither will I.

The interesting part is why Redis had to go, because it wasn't speed. Reservations lived in Redis (a quantity key per item, DECR to reserve, INCR to release) while the inventory ledger — the source of truth — lived in MySQL. The claim step, where a successful payment permanently deducts inventory, had to touch both, and two systems cannot be wrapped in one atomic step. Depending on the order, you get overselling (item sold, never deducted) or underselling (item deducted, still marked reserved). The Redis model also had no multi-location awareness, and it was a second cluster to run.

So the win wasn't throughput. It was collapsing two systems into one so that reserve and claim could sit inside a single ACID transaction.

How SKIP LOCKED Works

SELECT ... FOR UPDATE SKIP LOCKED is a feature most engineers haven't touched. It arrived in MySQL 8.0 and has been in PostgreSQL since 9.5 (Oracle has had it far longer). It does exactly what it sounds like: when scanning rows, skip any row that's currently locked by another transaction and return only available rows immediately — no waiting.

-- READ COMMITTED matters here; see the isolation section below
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
 
-- This does NOT block if other rows are locked
SELECT id FROM reservation_units
WHERE shop_id = 123
  AND inventory_item_id = 456
  AND inventory_group_id = 789
LIMIT 1
FOR UPDATE SKIP LOCKED;
 
-- If a row was returned, we hold the lock. Consume it, then record the hold.
DELETE FROM reservation_units WHERE shop_id = 123 AND inventory_item_id = 456
  AND inventory_group_id = 789 AND id = ?;
INSERT INTO reserved_quantities (shop_id, inventory_item_id, checkout_id, created_at)
VALUES (?, ?, ?, NOW());
 
COMMIT;

The key insight: multiple concurrent checkouts can each claim a different row from the pool simultaneously without blocking each other. You distribute contention across N rows instead of fighting over a single lock.

Proving it, in about forty lines

The claim "no two workers get the same row" is easy to write and easy to get wrong, so I ran it. Four Node workers, each looping BEGIN → SELECT ... WHERE state='ready' ORDER BY id LIMIT 1 <lock clause> → hold for 150 ms of simulated work → UPDATE ... SET state='done' → COMMIT, against a 12-row table on PostgreSQL 17.6. Same worker code every time; only the locking clause changes. (Postgres, not MySQL, because that's the database I had in front of me — the syntax and the semantics of the clause are the same. The InnoDB-specific gotcha is in the next section.)

### SELECT ... FOR UPDATE SKIP LOCKED  (4 workers)
  workers=4 rows=12 work_ms=150 wall=4107ms claims=12 duplicate_claims=0
  per-worker: {"w1":3,"w2":3,"w3":3,"w4":3}
 
### SELECT ... FOR UPDATE  (no SKIP LOCKED, 4 workers)
  workers=4 rows=12 work_ms=150 wall=7383ms claims=12 duplicate_claims=0
  per-worker: {"w1":2,"w2":3,"w3":3,"w4":4}
 
### SELECT ... FOR UPDATE NOWAIT  (4 workers)
  workers=4 rows=12 work_ms=150 wall=10472ms claims=12 duplicate_claims=0
  per-worker: {"w2":12}
  errors: 3 e.g. 55P03 could not obtain lock on row in relation "jobs"
 
### SELECT (no locking clause)  (4 workers)
  workers=4 rows=12 work_ms=150 wall=8631ms claims=34 duplicate_claims=22

The database was across a network from the workers, so read the wall times as ratios between the four variants rather than as latency figures. Four things fall out:

• SKIP LOCKED: 12 rows, 12 claims, zero duplicates, and the work spread perfectly 3/3/3/3 on every run. That even split is the whole point — each worker skipped past the rows its peers had locked and took the next free one.
• Plain FOR UPDATE: also correct, zero duplicates — but 1.8x slower and lopsided (2/3/3/4 here, 4/4/2/2 on the previous run). With ORDER BY id LIMIT 1 every worker wants the same head row, so three of them queue behind the first on every iteration. This is the "fighting over a single lock" failure mode, and note that it's a throughput failure, not a correctness one.
• FOR UPDATE NOWAIT: three of four workers died with 55P03 could not obtain lock on row, leaving one worker to drain all 12 rows by itself. NOWAIT is the wrong tool for a queue; it's for "I need this specific row and I'd rather fail than wait."
• No locking clause at all: 34 claims for 12 rows — 22 duplicate claims. That's the oversell, reproduced. Every worker read the same state='ready' row and every worker thought it had won.

That last line is the number worth remembering. It's the only variant whose count isn't deterministic — repeat runs gave 22 and 25 duplicates — because it's a race, and a race is exactly the thing you can't reason about from the code. Dropping the locking clause doesn't make the queue slightly racy; it hands the same unit to roughly three buyers.

The Pool Design

The naive version of "one row per unit" is one row per unit, full stop — and it collapses. Shopify's example: an item with 50,000 units across 10 locations means 500,000 rows, and the reserve query slows down as it scans them. So they keep a bounded pool: at most 1,000 rows per item/location combination, refilled from the inventory ledger by a replenishment process.

Inventory ledger: 12,400 units available at location X   <- source of truth
Pool rows:        1,000 pre-materialized rows            <- bounded working set
 
Checkout A: SKIP LOCKED → claims unit_1
Checkout B: SKIP LOCKED → claims unit_4   (no waiting, different row)
Checkout C: SKIP LOCKED → claims unit_7   (no waiting, different row)
 
Checkout A fails: unit_1 returned to pool
Checkout B succeeds: unit_4 consumed, replenishment refills toward 1,000

Note what the pool is not: it isn't the inventory count. The ledger is still the source of truth, and the pool is a capped working set in front of it — so SELECT COUNT(*) on the pool tops out at 1,000 and tells you nothing about the 12,400. Getting that backwards is the easiest way to reintroduce the bug you were trying to kill.

Shopify picked 1,000 by measuring: large enough to absorb bursts without draining, small enough that the table stays compact and the SKIP LOCKED scan stays fast. Sizing came from observed peak reservation rates per item/location during flash sales.

And the pool does drain during an extreme flash sale. When it does, the reserve path replenishes inline, with a lock so exactly one transaction refills while the others wait for it rather than all racing to insert — a deliberate thundering-herd guard. That adds latency to the unlucky reservation and preserves correctness: a buyer with available inventory is never told "sold out."

The Index Problem Nobody Saw Coming

The initial schema used a standard auto-increment primary key with a secondary index for lookups. It worked, but had a quiet locking issue, visible in SHOW ENGINE INNODB STATUS: InnoDB was acquiring two row locks per reservation — one on the secondary index used in the WHERE clause, and one on the clustered (primary key) index.

The fix was a composite primary key:

-- Before: two-lock problem
CREATE TABLE reservation_units (
  id                  BIGINT AUTO_INCREMENT PRIMARY KEY,
  shop_id             BIGINT NOT NULL,
  inventory_item_id   BIGINT NOT NULL,
  inventory_group_id  BIGINT NOT NULL,
  KEY idx_lookup (shop_id, inventory_item_id, inventory_group_id)
);
 
-- After: filter columns in the PK prefix, single-lock path
CREATE TABLE reservation_units (
  shop_id             BIGINT NOT NULL,
  inventory_item_id   BIGINT NOT NULL,
  inventory_group_id  BIGINT NOT NULL,
  id                  BIGINT NOT NULL,
  PRIMARY KEY (shop_id, inventory_item_id, inventory_group_id, id)
);

With the composite PK, InnoDB's clustered index already contains the filter columns. The WHERE shop_id = ? AND inventory_item_id = ? AND inventory_group_id = ? predicate resolves directly from the primary key scan — no secondary index, one lock per row instead of two.

The Isolation Level You Have to Change

This is the part that bites people who copy the SKIP LOCKED query and stop there, and it's InnoDB-specific.

MySQL's default isolation level is REPEATABLE READ, and under REPEATABLE READ InnoDB takes gap locks. Shopify found that running SELECT ... FOR UPDATE SKIP LOCKED against an empty pool — exactly the replenishment case — took gap locks including one on the "supremum" pseudo-record at the end of the index. Those locks then blocked the replenishment transaction from inserting the new rows, and could deadlock.

The fix was to run these transactions at READ COMMITTED, where InnoDB doesn't take gap locks the same way, so replenishment can proceed.

Two more decisions from the same project that generalise to any queue-on-a-table:

• Consistent lock ordering. Reserve touched two tables in one order and claim touched them in another, which is a deadlock recipe. They standardised it: reserve always DELETEs from the units table first, then INSERTs into reserved_quantities; claim only touches reserved_quantities. Same order everywhere means no circular wait.
• Batch with UNION ALL. A cart with several line items would otherwise be several round trips. One UNION ALL query fetches all the needed units at once.

The Actual Bottleneck Was Somewhere Else

After the schema fix, throughput still wasn't where it needed to be. Reservation P90 was acceptable, CPU wasn't maxed, the queries were already optimised — and yet there was a ceiling. What they saw in load tests was threads queuing in MySQL, CPU spiking when the queued work ran, and connection exhaustion to MySQL backends at the ProxySQL layer.

The move that cracked it is worth stealing wholesale. Knowing connections are exhausted tells you nothing about who is holding them, so they added per-caller attribution: annotate every SQL statement at the application layer with a comment tag naming the business process, then parse that tag at the proxy and measure how long each caller holds a connection.

SELECT /* conn_tag:checkout_completion */ id FROM reservation_units
WHERE shop_id = ? AND inventory_item_id = ? AND inventory_group_id = ?
LIMIT 1 FOR UPDATE SKIP LOCKED;

The output isn't "which queries are slow" — it's total connection hold time broken down by business process. That immediately showed the culprits: other parts of the checkout path holding connections across long transactions, unoptimised because they had never been the first thing to hit the limit. Reservations were the straw, not the weight.

Cleaning up that path removed 50% of reads and 33% of transactions on the primary. The reservation queries hadn't changed at all. Separately, InnoDB thread concurrency turned out to have been set conservatively years earlier and never revisited against a workload that had since changed; raising it removed another ceiling that was only visible once connection and CPU metrics sat side by side.

DoorDash: The Cluster You Don't Need

DoorDash's ML feature store started on Redis, and it worked well — until two things happened at once. Per-instance vertical scaling ran out, and "the cost of maintaining the entire dataset in memory began putting pressure on our efficiency targets." Their stated design principle is the whole argument in one line: RAM is roughly 100 times more expensive than SSD storage.

An intermediate attempt moved a subset of the data to a horizontally scalable relational database — chosen so that the slower queries landed on features where the latency wouldn't change the prediction. That bought time. Then the dataset doubled, and they were back at Redis's vertical ceiling, now with a 1,000-plus-node relational cluster to operate as well.

Their question: do we actually need Redis cluster semantics here, or just the Redis protocol?

The feature store's access pattern is read-heavy and batch-refreshed hourly, and their reasoning about cluster state is worth quoting: sharing cluster state through a peer-to-peer gossip protocol "almost always becomes a bottleneck at scale."

Enter Apache Kvrocks. It speaks RESP, so clients don't change, but it stores data in RocksDB — which means the working set lives on commodity SSD instead of RAM. Kvrocks does implement Redis clustering natively; DoorDash deliberately didn't use it, because the clustering and state management overhead was the thing they were trying to delete.

DoorDash Feature Store Architecture:
 
Offline batch job
    ↓ (Parquet files)
S3 staging bucket
    ↓ (one indexer per shard, checkpointing progress)
RocksDB backup on S3
    ↓ (fetchers download their designated shard backup at startup)
Kvrocks serving nodes [N1, N2, ..., Nn]
    ↓ (RESP protocol, topology supplied by RCM)
ML inference services

Scaling looks trivial: add a node, have it download the RocksDB shard backup, start serving. No rebalancing, because there's no shared cluster state to rebalance. The Redis Cluster Manager (RCM) they built is what makes that work — a Kubernetes Service that impersonates the Redis cluster discovery endpoint, using the K8s Pod Watch API to answer a client's CLUSTER NODES request with live topology. The data pods are stateless singletons that just serve what they hold.

Except it wasn't trivial, and this is the most useful part of the post. Fetch latency rose with the number of nodes in a shard — the exact opposite of infinite scaling. They tuned client configs, coroutine pools, telemetry, instance sizing. The actual cause was client-side: a Redis client asked to manage more than 2,000 nodes' worth of connections and cluster metadata does not perform well. Their fix was to stop lying to themselves about topology transparency and start lying to the client instead — RCM now returns only a subset of nodes in the discovery response, chosen by the client's source IP, so each client sees a small cluster while the real one keeps growing.

Two smaller findings from the rollout: clients showed a slight bias toward the first node in the CLUSTER NODES response (measured, judged manageable inside their autoscale triggers), and the hourly batch refresh raised fetch latency until they added SST sideloading to Kvrocks so new sorted-string-table files land in the storage engine without competing with queries.

Where it ended up: "a peak per-second load of over 130M HMGETs for 1.6B retrieved features, within a 50ms P999 latency target," with capacity deployed dynamically. DoorDash describes the cost savings as well received but doesn't publish a percentage for this migration, so don't let anyone quote you one.

When Redis Is Actually the Right Call

None of this means Redis is bad. It means Redis clusters have real operational overhead that's worth paying only when you need the capabilities they provide.

The pattern in both cases: they asked what do we actually need from Redis? and found the answer was narrower than assumed. Shopify needed a lock with queue semantics inside the same transaction as the ledger — SKIP LOCKED does exactly that. DoorDash needed fast key-value lookups over batch-refreshed data that was too expensive to keep in RAM — RocksDB with a Redis-protocol frontend does exactly that.

Notice that in neither story was the replacement faster. Shopify's win was atomicity; DoorDash's was cost and the deletion of shared cluster state. And in both, the interesting bug was somewhere other than the component being replaced — connection hold time in code nobody was looking at, client-side metadata handling for a 2,000-node cluster.

Question the default. Understand the access pattern. Then instrument the whole path, because the thing you rewrote is rarely the thing that was limiting you.