Our checkout flow slows down during promos—how do we prevent hot rows from taking down MySQL?

When your promo hits and thousands of customers pile into checkout at once, MySQL tends to expose its weakest point: hot rows that every request wants to touch at the same time. The result is lock contention, slow queries, and a checkout flow that feels like it’s getting DDoS’d by your own users. The fix is to move that contention out of MySQL and into a fast memory layer—Redis—where you can fan out reads, queue writes, and keep your system of record from melting down.

Quick Answer: Use Redis as a fast memory layer in front of MySQL to absorb hot traffic: cache hot reads, move high‑churn counters and sessions into Redis data structures, and write back to MySQL asynchronously. This removes hot-row contention from MySQL so your checkout flow stays fast even during promos.

The Quick Overview

What It Is: A Redis‑backed pattern for shielding MySQL from hot rows during high-traffic events—by moving locks, counters, and high-frequency reads into Redis instead of hammering a single row in your system of record.
Who It Is For: Teams running ecommerce or marketplace checkouts on MySQL (often in AWS/Azure/GCP, frequently on Kubernetes) that see latency spikes and timeouts during promos, flash sales, or events.
Core Problem Solved: MySQL cannot safely serve thousands of concurrent updates to the same rows without locking and queueing. Redis takes over the hot path so MySQL only handles durable, batched writes instead of real-time contention.

How It Works

At a high level, you use Redis as a fast coordination and data layer that sits between your app and MySQL:

Reads that don’t require strong consistency (cart contents, product availability snapshots, pricing blocks) are served from Redis.
High-churn values (inventory counters, rate limits, feature flags) live primarily in Redis, not in a single hot MySQL row.
Writes to MySQL are rate-limited, batched, or async so the database is never asked to handle promo-level concurrency on a single row.

Think of it as shifting the “hot” part of your workload from disk-bound row locks to in-memory data structures that can handle huge concurrency with sub-millisecond latency.

Phase 1 – Cache hot reads to avoid hammering MySQL
- Identify hot queries: active promotions, top SKUs, user carts, discount configs.
- Move them behind a Redis cache (hashes/JSON and simple key‑value).
- Use short TTLs or Redis Data Integration (RDI) to keep the data fresh without manually invalidating cache entries.
- Result: a 10–100x reduction in read traffic to the hottest MySQL rows.
Phase 2 – Move contention to Redis with atomic operations
- Inventory counters, per-user limits, and “has this coupon been used?” checks move from a single UPDATE in MySQL to atomic Redis INCR/DECR or Lua scripts.
- You treat Redis as the source of truth for the “now” state of availability and limits during the promo window.
- MySQL receives consolidated updates later, instead of being part of the real-time decision on every checkout.
Phase 3 – Safely sync back to MySQL
- Periodically (or continuously) flush state from Redis back to MySQL in a controlled way.
- Use worker services to batch updates per product or per order and write them in small, paced chunks so MySQL stays healthy.
- For read freshness in the rest of your app, use Redis Data Integration to sync data changes from MySQL to Redis, reducing stale reads and cache-aside complexity.

The result is a checkout system that behaves like this: Redis takes the blast, MySQL keeps the books.

Features & Benefits Breakdown

Core Feature	What It Does	Primary Benefit
Fast memory layer for hot data	Keeps high-churn checkout data (counters, sessions, product snapshots) in Redis memory instead of repeatedly hitting MySQL.	Removes hot-row contention and keeps p99 checkout latency low during promos.
Atomic operations & queues	Uses Redis commands (`INCR`, `DECR`, Lua scripts, streams, lists) to handle high-concurrency updates and orchestration.	Serializes conflict-heavy operations in memory, avoiding MySQL locks and deadlocks.
Sync tools for data freshness	Uses Redis Data Integration or background workers to keep Redis and MySQL in sync without manual cache-aside hacks.	Reduces stale reads and failed checkouts while still offloading the hot path from MySQL.

Ideal Use Cases

Best for flash sales & high-traffic promotions: Because it moves the most contentious operations—stock checks, per-user limits, price lookups—out of MySQL and into Redis, so a single SKU can handle thousands of concurrent checkouts without turning into a hot row.
Best for marketplaces & multi-tenant checkouts: Because Redis lets you shard and isolate hot tenants or sellers in memory, use queues and streams for orders, and still keep a single MySQL-based system of record behind the scenes.

How to take the heat off MySQL in checkout

Let’s walk through the typical failure modes and how you use Redis to fix each one.

1. Hot-row reads: product and promo data

Failure mode: During a promo, every page view and checkout hits the same product row(s) and promo/discount rows. Even if writes are modest, shared locks from reads plus occasional writes cause latency spikes and queueing.

Redis pattern: read through cache with short TTLs

You can cache product and promo data in Redis using hashes or JSON. In Redis Cloud / Redis Software with RedisJSON:

# Example key: product:sku:12345
JSON.SET product:sku:12345 $ '{
  "sku": "12345",
  "name": "Promo T-Shirt",
  "price": 14.99,
  "promoPrice": 9.99,
  "promoEndsAt": "2026-04-12T23:59:59Z",
  "inventory": 1842
}'
EXPIRE product:sku:12345 30

In your application (Node.js example):

const redis = require('redis').createClient({ url: process.env.REDIS_URL });
const mysqlPool = /* your existing MySQL pool */;

async function getProduct(sku) {
  const key = `product:sku:${sku}`;
  const cached = await redis.get(key);
  if (cached) return JSON.parse(cached);

  const [rows] = await mysqlPool.query(
    'SELECT sku, name, price, promo_price, promo_ends_at, inventory FROM products WHERE sku = ?',
    [sku]
  );
  const product = rows[0];
  await redis.setEx(key, 30, JSON.stringify(product)); // 30s TTL
  return product;
}

Why this helps:

The same hot product can get hit hundreds of thousands of times from Redis without ever touching MySQL.
Short TTLs ensure the cache reflects changes relatively quickly, even without explicit invalidation.
For stricter freshness needs, Redis Data Integration can stream changes from MySQL into Redis in near real time, keeping these keys accurate without the classic cache-aside race conditions.

2. Hot-row writes: inventory and limits

Failure mode: All buyers of a promo SKU decrement a single inventory field in MySQL. Under load, you get lock waits, rollbacks, and sometimes overselling when application-level retry logic kicks in.

Redis pattern: use atomic counters in Redis as the “live” inventory

Move the real-time inventory check into Redis, and treat MySQL as where you reconcile after the fact.

In Redis:

# Initialize Redis inventory based on MySQL
SET inventory:sku:12345 2000  # 2000 units available for promo

In your checkout service:

async function reserveInventory(sku, qty) {
  const key = `inventory:sku:${sku}`;
  // Use a Lua script for "check and decrement" to keep it atomic
  const script = `
    local current = tonumber(redis.call("GET", KEYS[1]) or "-1")
    local qty = tonumber(ARGV[1])
    if current == -1 then
      return -1  -- missing inventory key, treat as error
    end
    if current < qty then
      return 0   -- not enough inventory
    end
    local newVal = current - qty
    redis.call("SET", KEYS[1], newVal)
    return newVal
  `;
  const newInventory = await redis.eval(script, { keys: [key], arguments: [qty.toString()] });

  if (newInventory === -1) throw new Error('Inventory not initialized');
  if (newInventory === 0) return false; // out of stock

  return true;
}

Then, instead of updating MySQL immediately, you:

Write the order to a Redis stream for asynchronous processing:

XADD orders:stream * sku 12345 qty 1 userId 42

Have a worker service consume the stream and write orders + eventual inventory adjustments to MySQL in batches.

Why this helps:

The high-concurrency inventory update is now an in-memory operation with sub-millisecond latency and no row locks.
MySQL sees fewer writes, grouped by the worker, so row-level locking doesn’t become a bottleneck.
You can still enforce hard limits—Redis becomes the authority for “units left” during the promo window.

Warning: If Redis goes down or you lose data in this pattern, you can end up with mismatched inventory between Redis and MySQL. Use Redis Cloud or Redis Software with persistence, clustering, and automatic failover, and design a reconciliation job for post-event correction.

3. Per-user constraints without extra joins

Failure mode: You enforce “1 unit per user” or “one use per coupon” using MySQL uniqueness constraints or a join against a usage table. During promos, those rows turn hot as well, and your enforcement check becomes another point of contention.

Redis pattern: store per-user and per-coupon state in Redis sets/hashes

For “one use per coupon per user”:

# Key stores a set of user IDs who already used the coupon
SADD coupon:SUMMER24:used_users 123   # user 123 used it
SISMEMBER coupon:SUMMER24:used_users 123  # -> 1 means already used

Application logic:

async function canUseCoupon(userId, couponCode) {
  const key = `coupon:${couponCode}:used_users`;
  const alreadyUsed = await redis.sIsMember(key, userId.toString());
  return !alreadyUsed;
}

async function markCouponUsed(userId, couponCode) {
  const key = `coupon:${couponCode}:used_users`;
  await redis.sAdd(key, userId.toString());
}

You can batch sync coupon usage back to MySQL later if you need a durable audit trail, but Redis handles the hot decision path.

4. Session and cart data: stop round-tripping to MySQL

Failure mode: You store session and cart state in MySQL for durability or convenience. Under promo-level load, the “cart” table turns into a mess of updates and reads, and becomes yet another hot region.

Redis pattern: native session and cart storage

Session state keyed by user or session ID:

HSET session:user:123 currentStep "payment" lastSeen "2026-04-12T14:22:01Z"
EXPIRE session:user:123 3600

Cart stored as a Redis hash or JSON document:

HSET cart:user:123 sku:12345 2 sku:98765 1
EXPIRE cart:user:123 1800

Or with RedisJSON:

JSON.SET cart:user:123 $ '{
  "items": [
    {"sku":"12345","qty":2},
    {"sku":"98765","qty":1}
  ]
}'
EXPIRE cart:user:123 1800

You only write the finalized order to MySQL, not every intermediate cart change.

Limitations & Considerations

Eventual consistency between Redis and MySQL:
During a promo, Redis is effectively the real-time authority for inventory and some constraints. MySQL will lag slightly until your workers catch up.
- Workaround: Make it explicit in your design that inventory is enforced in Redis and reconciled to MySQL. Use background jobs after the event to correct any small drift.
Operational complexity of an extra data layer:
Adding Redis means more infrastructure: scaling, failover, metrics, and security.
- Workaround: Use Redis Cloud if you want fully managed operations (automatic scaling, 99.999% uptime, Active-Active Geo Distribution), or Redis Software if you need on‑prem/hybrid. Either way, hook Redis into Prometheus/Grafana and watch p95/p99 latency and command rates so you see issues before checkout does.

Pricing & Plans

Specific pricing depends on your deployment choice and workload shape, but the practical guidance is:

For promo protection, you want:
- Enough memory for hot products, carts, and promo state (often a fraction of your total catalog).
- Throughput headroom for peak promo QPS, not just normal days.

Typical Redis options:

Redis Cloud: Best for teams on AWS/Azure/GCP who want fully managed scaling and high availability. Ideal when you’d rather spend time fixing checkout UX than running clusters.
Redis Software / Redis Open Source: Best for teams who need on‑prem or hybrid deployments, or want to embed Redis tightly into existing Kubernetes infrastructure and manage everything in-house.

You can start small, benchmark under load (k6/Locust + promo traffic patterns), and then right-size your plan once you see how much MySQL traffic you’ve successfully offloaded.

Frequently Asked Questions

Can we fix hot MySQL rows just by adding read replicas or more CPU?

Short Answer: Not reliably—read replicas don’t fix write contention, and more CPU doesn’t solve row-level locks.

Details: Hot rows are a locking and contention problem, not just a capacity problem. Even if you add replicas, the primary still needs to handle all writes and many reads will still require up-to-date data, forcing them onto the primary. Adding CPU or IOPS might delay the problem, but at promo scale you’re still serializing access to a single row. Redis sidesteps the issue by moving the hot parts of the workload to in-memory data structures that handle high concurrency without row locks, and then letting MySQL do fewer, more controlled writes.

How do we avoid overselling if inventory lives in Redis during a promo?

Short Answer: Use Redis as the strict inventory gate (atomic operations) and treat MySQL as the ledger you reconcile afterwards.

Details: Overselling happens when multiple threads think inventory is available and decrement it independently. In Redis, you can enforce a strict “check and decrement” using atomic commands or Lua:

If inventory < requested, reject the order.
If inventory >= requested, decrement and proceed.

This happens in a single, atomic operation with sub-millisecond latency. To keep MySQL accurate, a background worker reads accepted orders from a Redis stream and applies idempotent, batched updates to MySQL. If a worker fails mid-flight, you can replay from the stream (with proper deduplication keys) until MySQL matches Redis. After the promo, you can run a reconciliation job that compares total sold units (MySQL) with remaining inventory (Redis) and corrects any minor drift.

Summary

If your checkout flow slows down during promos, the root cause is almost always the same: hot MySQL rows turning into a serialization point for your entire business. Trying to fix that with more replicas or bigger instances just moves the pain around. The right move is architectural—shift hot paths into a Redis fast memory layer:

Cache hot product and promo reads so they never touch MySQL.
Move inventory and per-user limit enforcement into atomic Redis operations, not single-row updates.
Store sessions and carts in Redis so MySQL only sees final orders, not every click.
Sync back to MySQL from Redis in a controlled, observable way instead of relying on cache-aside best guesses.

When you do this, promos stop feeling dangerous, and your checkout can finally ride the spike instead of collapsing under it.

Next Step

Get Started