How We Replaced Redis with MySQL SKIP LOCKED for Inventory Reservation at Scale

A real rebuild of Mattrx's Sponsored Placements reservation engine. We tore out a Redis hold/Redlock layer that kept overselling premium ad inventory, and moved the whole thing into MySQL 8 using SELECT ... FOR UPDATE SKIP LOCKED. Oversells went to zero, p95 dropped from 210 ms to 34 ms, and a three-node Redis cluster got decommissioned. Here is the before, the after, the code, and the trade-offs.

---

TL;DR

For two years, Mattrx booked limited-availability ad inventory through Redis: a counter in Redis, a Redlock around the decrement, and a TTL key per hold. It oversold. Not catastrophically, but consistently — 40 to 60 double-booked placements a month, each one a manual refund and an apology email to an advertiser. The root cause was never one bug. It was the architecture: two sources of truth that could not be made atomic with each other.

We deleted the Redis layer and put reservations where the money already lived — in MySQL — using row-level FOR UPDATE SKIP LOCKED to claim inventory units without serializing every request through one hot counter.

Dimension	Before (Redis + Redlock)	After (MySQL SKIP LOCKED)
Source of truth	Redis counter and SQL rows	MySQL only
Oversells / month	40–60	0
Reservation p95	210 ms	34 ms
Reservation p99	540 ms	61 ms
Sustained throughput (per instance)	~600 RPS before lock thrash	1,400 RPS
Hold expiry mechanism	Redis TTL (lossy on failover)	Crash-safe rows + lazy reclaim
Reconciliation job	Nightly, 9–14 min	Deleted
Moving parts to operate	App + Redis (3 nodes) + SQL	App + MySQL
Infra cost	+$180/mo Redis	$0 (decommissioned)
Correctness guarantee	"probably" (Redlock is efficiency, not safety)	DB invariant + unique index

Production metrics from the cutover (Sponsored Placements service, 8 weeks before vs 8 weeks after):

• Oversell incidents: 60 → 0 over the measurement window.
• Reservation endpoint p95: 210 ms → 34 ms.
• Reservation endpoint p99: 540 ms → 61 ms.
• Lock-wait timeouts (innodb_lock_wait_timeout hits): from ~900/day on the naive counter design to <5/day.
• Deadlocks (error 1213): tamed to <2/day with consistent claim ordering + retry.
• Nightly reconciliation job: 9–14 min runtime → removed entirely.
• Redis cluster: 3 × cache.t3.medium → 0. Saving ~$180/month.
• Peak reservation throughput before falling over: ~600 RPS → 1,400 RPS per ASP.NET Core instance.
• Mean lines of "is Redis and SQL still in sync?" defensive code: ~340 → 0.
• Pages / on-call escalations tied to inventory drift: ~3/month → 0 in the window.

If you only take one thing: the database row you are already going to write is also the best lock you will ever have. Reaching for Redis to coordinate access to SQL data is adding a second coordinator that cannot participate in the first one's transaction.

---

The one mental shift

Most teams reach for Redis on reservations because of a half-remembered rule: "the database is slow, don't lock rows under load." That rule is from the era of table locks and SERIALIZABLE-by-default. It is wrong for this problem.

The shift is this:

SKIP LOCKED turns a contended table into a concurrent work queue. Instead of every request fighting over one counter, each request grabs different rows and ignores the ones someone else is holding. There is no hot row, so there is no serialization, so there is no reason to escape to Redis.

FOR UPDATE alone does serialize — every transaction queues behind the lock holder. That is the experience that scares people off SQL locking. FOR UPDATE SKIP LOCKED is the opposite: a transaction that would have blocked instead skips the locked row and takes the next free one. Add LIMIT, and you have atomic "give me N available units, whatever's free right now" in a single round trip — no application-level lock, no second datastore.

Once you see inventory as rows you claim rather than a number you decrement, the entire Redis apparatus becomes unnecessary.

---

The running example: Mattrx Sponsored Placements

Mattrx is a multi-tenant marketing-analytics SaaS — 110k MAU, ~3,200 req/sec peak across six ASP.NET Core instances, Clean Architecture + CQRS via MediatR. The analytics core runs on Azure SQL. (If you've read the other Mattrx posts, that's the system you know.)

But there's a bounded context that doesn't belong in the analytics warehouse: Sponsored Placements. Mattrx sells a marketplace of limited premium ad inventory — homepage-takeover slots, category-sponsor seats, and a capped partner-webinar program. These are genuinely scarce: there are exactly 200 "Q3 Homepage Takeover" units, and when they're gone, they're gone. Advertisers add them to a cart, get a 10-minute hold while they pay, and either convert the hold to a sale or let it expire.

This is inventory reservation in the textbook sense, and it lives in its own MySQL 8 OLTP database — separate from analytics, polyglot-persistence style. The backend is .NET 9 (Pomelo EF Core provider + Dapper for the hot path). Peak on the reservation endpoint is ~900 RPS, bursty around month-end campaign planning when everyone scrambles for the same slots.

That burst is exactly when Redis oversold.

---

What the Redis design actually looked like

Here's the shape we inherited. Inventory count lived in Redis. The "truth" of which advertiser owned which unit lived in SQL. A Redlock guarded the decrement. Holds were TTL keys.

                 ┌──────────────────────────────────────────┐
                 │            Reservation request             │
                 └───────────────────┬───────────────────────┘
                                     │
                ┌────────────────────▼─────────────────────┐
                │  1. Redlock.acquire("lock:placement:42")  │  ← 3+ Redis round trips
                │  2. GET count:placement:42                │
                │  3. if count >= qty: DECRBY count qty     │
                │  4. SET hold:res:<id> ... EX 600 NX       │  ← TTL = the hold
                │  5. Redlock.release                        │
                └────────────────────┬──────────────────────┘
                                     │ (later, separate write)
                ┌────────────────────▼─────────────────────┐
                │  INSERT INTO reservation (...) in MySQL   │  ← second source of truth
                └────────────────────┬──────────────────────┘
                                     │
                ┌────────────────────▼─────────────────────┐
                │  Nightly job: reconcile Redis count vs    │
                │  SUM of active reservations in MySQL      │  ← because they drift
                └───────────────────────────────────────────┘

Two systems. The Redlock made step 1–5 look atomic, but the MySQL insert in the next box was a separate transaction in a separate datastore. Any crash, timeout, or failover between them left the two stores disagreeing — hence the nightly reconciliation, hence the drift, hence the oversells.

The C# was honest about its own anxiety:

// BEFORE — ReserveInventoryHandler (Redis + Redlock), abbreviated
public async Task<ReserveResult> Handle(ReserveCommand cmd, CancellationToken ct)
{
    var lockKey = $"lock:placement:{cmd.PlacementId}";
    await using var redLock = await _redlock.CreateLockAsync(
        lockKey, expiry: TimeSpan.FromSeconds(8),
        wait: TimeSpan.FromSeconds(3), retry: TimeSpan.FromMilliseconds(50));

    if (!redLock.IsAcquired)
        return ReserveResult.Busy(); // ← user sees this under burst

    var countKey = $"count:placement:{cmd.PlacementId}";
    var available = (long)await _redis.StringGetAsync(countKey);
    if (available < cmd.Quantity)
        return ReserveResult.SoldOut();

    await _redis.StringDecrementAsync(countKey, cmd.Quantity);

    var reservationId = Guid.NewGuid();
    await _redis.StringSetAsync(
        $"hold:{reservationId}", cmd.PlacementId.ToString(),
        TimeSpan.FromMinutes(10), When.NotExists);

    // SECOND datastore, SEPARATE transaction — the gap that oversold:
    await _db.Reservations.AddAsync(new Reservation(reservationId, cmd), ct);
    await _db.SaveChangesAsync(ct); // if THIS throws, Redis already decremented
    // (compensating decrement-undo existed, but races with TTL expiry)

    return ReserveResult.Held(reservationId);
}

Every failure mode below is real and we hit all of them.

---

Section 1 — Two sources of truth that can't be made atomic

The diagnosis

The decisive question on any reservation system: what is the single row whose existence means "this unit is taken"? In the Redis design there wasn't one. The count lived in Redis; the ownership lived in SQL. No transaction spans both. The Redlock only protects the Redis half.

We proved it with a load test that killed the process between the DECRBY and the SaveChangesAsync:

# Reproduce the drift: 500 concurrent reserves, SIGKILL 1% mid-flight
k6 run --vus 500 --duration 60s reserve.js &
# in another shell, randomly kill an instance
watch -n2 'kubectl delete pod -l app=placements --field-selector ... | head -1'

# After: Redis count says 14 available, SQL says 9 active reservations.
# Drift = 5 phantom units that will be sold twice.

Before

Reconciliation code that should never need to exist:

// BEFORE — nightly reconciliation (the smell)
foreach (var placement in await _db.Placements.ToListAsync(ct))
{
    var active = await _db.Reservations.CountAsync(
        r => r.PlacementId == placement.Id && r.Status == Active, ct);
    var redisCount = (long)await _redis.StringGetAsync($"count:placement:{placement.Id}");
    var expected = placement.TotalUnits - active;
    if (redisCount != expected)
    {
        _log.Warning("Drift on {Id}: redis={R} expected={E}", placement.Id, redisCount, expected);
        await _redis.StringSetAsync($"count:placement:{placement.Id}", expected); // clobber
    }
}

That clobber line silently overwrote real holds that were mid-flight when the job ran. It created oversells while trying to fix them.

After

Make the SQL row the only truth. One unit = one row. "Taken" = status <> 'available'. There is nothing to reconcile because there is nothing else.

-- AFTER — the schema. One row per reservable unit.
CREATE TABLE inventory_unit (
  id              BIGINT      NOT NULL AUTO_INCREMENT PRIMARY KEY,
  placement_id    BIGINT      NOT NULL,
  status          ENUM('available','held','sold') NOT NULL DEFAULT 'available',
  reservation_id  CHAR(36)    NULL,
  hold_expires_at DATETIME(3) NULL,
  updated_at      DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3)
                              ON UPDATE CURRENT_TIMESTAMP(3),
  -- the index that makes SKIP LOCKED claims cheap:
  KEY ix_claim (placement_id, status, id)
) ENGINE=InnoDB;

The reservation itself is the same transaction that claims the rows. No second store, no gap.

Mattrx metric: Reconciliation job 9–14 min nightly → deleted. Drift-related oversells from this class → 0.

---

Section 2 — The hot-row problem (why FOR UPDATE alone isn't enough)

The naive "just use SQL" fix is a counter row plus SELECT ... FOR UPDATE. It's correct. It's also a throughput cliff, and the reason people wrongly conclude "SQL can't do this at scale."

Before (naive SQL — correct but serial)

-- BEFORE (naive) — single counter row, every request queues on it
START TRANSACTION;
SELECT available FROM placement_counter
  WHERE placement_id = 42
  FOR UPDATE;                       -- ← every concurrent reserve blocks HERE
-- app checks available >= qty
UPDATE placement_counter
  SET available = available - 2
  WHERE placement_id = 42;
COMMIT;

Under 900 RPS all targeting the popular "Q3 Homepage Takeover" pool, every transaction serializes behind one row lock. We measured it: throughput flatlined at ~600 RPS and innodb_lock_wait_timeout started firing ~900 times/day. This is the experience that sends teams back to Redis.

-- diagnostic: who is waiting on whom
SELECT * FROM performance_schema.data_lock_waits;
SELECT * FROM sys.innodb_lock_waits;   -- the sys-schema view, friendlier
SHOW ENGINE INNODB STATUS\G            -- LATEST DETECTED DEADLOCK + lock waits

After (row-per-unit + SKIP LOCKED — concurrent)

Stop locking the counter. Lock the units you're taking, and skip the ones someone else is taking right now:

-- AFTER — claim N free units without blocking on anyone else's
START TRANSACTION;

SELECT id
FROM inventory_unit
WHERE placement_id = 42
  AND (status = 'available'
       OR (status = 'held' AND hold_expires_at < NOW(3)))   -- self-healing, see §3
ORDER BY id
LIMIT 2                                                       -- requested quantity
FOR UPDATE SKIP LOCKED;                                       -- ← the whole trick

-- (app gets back e.g. ids 1107, 1108; if it got fewer than 2 rows → SoldOut)

UPDATE inventory_unit
SET status = 'held',
    reservation_id = 'e2c1...-uuid',
    hold_expires_at = NOW(3) + INTERVAL 10 MINUTE
WHERE id IN (1107, 1108);

INSERT INTO reservation (id, placement_id, advertiser_id, quantity, status, expires_at)
VALUES ('e2c1...-uuid', 42, 9001, 2, 'active', NOW(3) + INTERVAL 10 MINUTE);

COMMIT;

Two concurrent requests for the same pool now lock different rows. Neither waits. Throughput scales with the number of free units, not down to a single lock.

// AFTER — ReserveInventoryHandler, MySQL only (Dapper on the hot path)
public async Task<ReserveResult> Handle(ReserveCommand cmd, CancellationToken ct)
{
    await using var conn = await _factory.OpenAsync(ct);
    await using var tx = await conn.BeginTransactionAsync(IsolationLevel.ReadCommitted, ct);

    var ids = (await conn.QueryAsync<long>(new CommandDefinition("""
        SELECT id FROM inventory_unit
        WHERE placement_id = @PlacementId
          AND (status = 'available'
               OR (status = 'held' AND hold_expires_at < NOW(3)))
        ORDER BY id
        LIMIT @Quantity
        FOR UPDATE SKIP LOCKED
        """, new { cmd.PlacementId, cmd.Quantity }, tx,
        cancellationToken: ct))).ToArray();

    if (ids.Length < cmd.Quantity)
    {
        await tx.RollbackAsync(ct);
        return ReserveResult.SoldOut();   // not "Busy" — we never block, so never busy
    }

    var reservationId = Guid.NewGuid().ToString();
    await conn.ExecuteAsync(new CommandDefinition("""
        UPDATE inventory_unit
        SET status='held', reservation_id=@reservationId,
            hold_expires_at = NOW(3) + INTERVAL 10 MINUTE
        WHERE id IN @ids
        """, new { reservationId, ids }, tx, cancellationToken: ct));

    await conn.ExecuteAsync(new CommandDefinition("""
        INSERT INTO reservation (id, placement_id, advertiser_id, quantity, status, expires_at)
        VALUES (@reservationId, @PlacementId, @AdvertiserId, @Quantity, 'active',
                NOW(3) + INTERVAL 10 MINUTE)
        """, new { reservationId, cmd.PlacementId, cmd.AdvertiserId, cmd.Quantity },
        tx, cancellationToken: ct));

    await tx.CommitAsync(ct);
    return ReserveResult.Held(reservationId);
}

Notice what's gone: no Redlock, no IsAcquired check, no compensating decrement, no second datastore. The user never sees "Busy" because nothing ever blocks — they see Held or SoldOut, both of which are true.

Mattrx metric: Sustained throughput 600 RPS → 1,400 RPS per instance; lock-wait timeouts ~900/day → <5/day.

---

Section 3 — Hold expiry: from lossy TTL to self-healing rows

The diagnosis

Redis TTL is a lossy expiry mechanism for correctness purposes. Three ways it bit us:

1. Failover loses holds. Redis replication is async. On a primary failover, the replica is behind by however many writes didn't replicate. Holds that existed on the old primary vanish — the unit looks free again while the original advertiser is still mid-checkout. Instant oversell.
2. No transactional tie. The TTL key and the SQL reservation expire independently. The key can lapse while the reservation row still says active.
3. Reclaim races. A background sweeper deleting expired keys races with a fresh reserve touching the same unit.

Before

// BEFORE — hold = a TTL key. Expiry is "best effort".
await _redis.StringSetAsync($"hold:{reservationId}", placementId,
    TimeSpan.FromMinutes(10), When.NotExists);
// On failover, this key may simply not be on the new primary.
// A separate sweeper scanned hold:* keyspace notifications to free units. It lagged.

After

Two changes, both leaning on the fact that the hold is now a durable, crash-safe InnoDB row.

First, expiry is self-healing inside the claim query — note the OR (status='held' AND hold_expires_at < NOW(3)) from §2. An expired hold is automatically eligible to be re-claimed by the next reserve, with no sweeper on the critical path. Correctness no longer depends on a background job running on time.

Second, a lightweight sweeper still runs — but only to keep dashboards tidy and to flip rows to a clean available state, not to enforce correctness. And it, too, uses SKIP LOCKED so it never fights live reservations:

-- AFTER — janitor sweep, non-blocking, batched
UPDATE inventory_unit
SET status = 'available', reservation_id = NULL, hold_expires_at = NULL
WHERE id IN (
  SELECT id FROM (
    SELECT id FROM inventory_unit
    WHERE status = 'held' AND hold_expires_at < NOW(3)
    ORDER BY id
    LIMIT 500
    FOR UPDATE SKIP LOCKED          -- skips units a live reserve is converting right now
  ) AS batch
);

Because InnoDB's redo log makes the held row durable, a crash or failover does not lose the hold. The new primary comes up with the same held rows and the same hold_expires_at. No phantom availability.

Mattrx metric: Failover-induced oversells (a recurring class during AWS maintenance windows) → 0. Sweeper went from "load-bearing for correctness" to "cosmetic," runtime <300 ms/batch.

---

Section 4 — Redlock was never a correctness primitive

This one is worth stating plainly because it's a common misunderstanding.

Redlock (the multi-node Redis locking algorithm) is, by its own author's framing, a tool for efficiency, not correctness — it reduces duplicate work, but you must not rely on it to guarantee mutual exclusion when correctness depends on it. The canonical critique (Kleppmann's "How to do distributed locking") walks through how clock skew, GC pauses, and process stalls can let two clients both believe they hold the lock. For inventory you cannot oversell, correctness is the whole point. Redlock is the wrong tool by definition.

Before

// BEFORE — relying on Redlock for a correctness-critical section
await using var redLock = await _redlock.CreateLockAsync("lock:placement:42", ...);
if (redLock.IsAcquired)
{
    // We BELIEVED we had exclusive access here. A GC pause longer than the
    // lock expiry, or a clock jump on one Redis node, breaks that belief.
    // Two handlers can be inside this block at once. Both decrement. Oversell.
}

After

The transaction is the mutual exclusion, and it's enforced by the same engine that stores the data, with no separate clock and no separate failure domain. Two guarantees do the work:

1. Row locks under FOR UPDATE — two transactions cannot hold the same row's write lock; SKIP LOCKED just means the loser takes a different row instead of waiting.
2. A unique constraint as the final backstop — even if logic somewhere is wrong, the database refuses to record the same unit sold twice:

-- AFTER — the invariant the database enforces no matter what the app does
ALTER TABLE inventory_unit
  ADD CONSTRAINT chk_held_has_res
  CHECK (status <> 'held' OR reservation_id IS NOT NULL);

-- and a unique guard on the sale ledger so a unit can be SOLD at most once
CREATE UNIQUE INDEX ux_sold_unit ON sale_line (inventory_unit_id);

There's no clock to skew, no second node to disagree, no GC pause that can grant two owners. Correctness is a property of the storage engine, not of a distributed-systems paper.

Mattrx metric: Correctness guarantee moved from "probably (Redlock)" to "DB invariant." Oversells from lock-failure class → 0.

---

Section 5 — Deadlocks, ordering, and the retry you do need

SKIP LOCKED removes most contention, but you can still deadlock if two transactions try to lock overlapping sets of rows in different orders. The fix is boring and effective: always claim in the same order (ORDER BY id), and retry the rare deadlock rather than trying to prevent every one.

Before (no ordering, no retry)

-- BEFORE — no ORDER BY: two txns can grab rows in opposite order → 1213 deadlock
SELECT id FROM inventory_unit
WHERE placement_id = 42 AND status = 'available'
LIMIT 2
FOR UPDATE SKIP LOCKED;     -- nondeterministic row order

After (deterministic order + tight retry)

ORDER BY id makes every transaction walk the index the same direction, so lock acquisition order is consistent and the deadlock window nearly closes. For the residue, a small retry on error 1213 / 1205:

// AFTER — retry only the two transient MySQL errors worth retrying
static bool IsTransient(MySqlException e) =>
    e.Number is 1213    // ER_LOCK_DEADLOCK
            or 1205;    // ER_LOCK_WAIT_TIMEOUT

public async Task<ReserveResult> ReserveWithRetry(ReserveCommand cmd, CancellationToken ct)
{
    for (var attempt = 1; ; attempt++)
    {
        try { return await Handle(cmd, ct); }
        catch (MySqlException e) when (IsTransient(e) && attempt <= 3)
        {
            // jittered backoff; deadlocks are rare and clear instantly on retry
            await Task.Delay(TimeSpan.FromMilliseconds(5 * attempt), ct);
        }
    }
}

-- diagnostic: confirm the deadlock graph after a 1213
SHOW ENGINE INNODB STATUS\G   -- read the LATEST DETECTED DEADLOCK block
SELECT COUNT(*) FROM performance_schema.events_errors_summary_global_by_error
WHERE error_name = 'ER_LOCK_DEADLOCK';

A retry budget of 3 with single-digit-ms backoff was enough: deadlocks fell to <2/day, and every one of those resolved on the first retry — invisible to the advertiser.

Mattrx metric: Deadlocks (1213) tamed to <2/day, all auto-resolved; zero user-visible reservation failures from contention.

---

Section 6 — Isolation level, indexes, and pool sizing

A few settings make or break this pattern. We learned them the unglamorous way.

Use READ COMMITTED, not the MySQL default REPEATABLE READ. Under REPEATABLE READ, InnoDB takes gap locks and next-key locks that widen what your SELECT ... FOR UPDATE holds, increasing contention and deadlocks for exactly this kind of range-claim query. READ COMMITTED locks only rows it actually touches.

-- AFTER — set per transaction (or per connection) on the reservation path
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

Index for the claim predicate. The ix_claim (placement_id, status, id) composite lets the optimizer seek to the right pool, filter by status, and walk id ascending for the ORDER BY — all from the index, no filesort. Without it, SKIP LOCKED still works but scans, and a scan under load reintroduces contention.

-- diagnostic: prove the claim query is an index range scan, not a table scan
EXPLAIN
SELECT id FROM inventory_unit
WHERE placement_id = 42
  AND (status='available' OR (status='held' AND hold_expires_at < NOW(3)))
ORDER BY id LIMIT 2 FOR UPDATE SKIP LOCKED;
-- want: key = ix_claim, Extra without "Using filesort"

Right-size the connection pool and innodb_lock_wait_timeout. Because nothing blocks for long anymore, you don't need a huge pool — you need a bounded one so a stall can't open thousands of transactions. We run a pool of 30 per instance and innodb_lock_wait_timeout = 5. Short timeouts surface problems fast instead of letting them pile up.

Before: pool=200, lock_wait_timeout=50  → stalls cascaded, threads piled up
After:  pool=30,  lock_wait_timeout=5   → fast-fail, retry, recover

Mattrx metric: Switching to READ COMMITTED cut deadlocks by ~70% on its own; the composite index dropped claim-query time from ~9 ms to <1 ms at p95.

---

Before / after, end to end

The architecture diff is the real story — most of the wins come from deleting a box, not from clever SQL.

BEFORE                                   AFTER
──────                                   ─────
┌───────────┐                            ┌───────────┐
│  ASP.NET  │                            │  ASP.NET  │
│   Core    │                            │   Core    │
└─────┬─────┘                            └─────┬─────┘
      │ Redlock (3 RT)                          │ 1 txn
      ▼                                          ▼
┌───────────┐   decrement     ┌────────┐   ┌──────────────┐
│   Redis   │◄──────────────► │ counter│   │   MySQL 8    │
│ (3 nodes) │   TTL holds     └────────┘   │  inventory_  │
└─────┬─────┘                              │  unit rows   │
      │ drift                              │ FOR UPDATE   │
      ▼                                    │ SKIP LOCKED  │
┌───────────┐    INSERT       ┌────────┐   └──────────────┘
│   MySQL   │◄──────────────► │ resv   │
│ (truth #2)│                 └────────┘   one source of truth,
└─────┬─────┘                              one transaction,
      │                                    zero reconciliation
      ▼
┌──────────────┐
│ nightly      │
│ reconcile +  │  ← exists only to paper over the split
│ clobber      │
└──────────────┘

And the race that used to oversell, as a timeline:

BEFORE — two advertisers, last unit, Redlock GC-pause window
 t0  A: Redlock acquired
 t1  A: GET count = 1
 t2  A: [GC pause 9s — longer than 8s lock expiry]
 t3  Redis: A's lock auto-expires
 t4  B: Redlock acquired (Redis thinks it's free)
 t5  B: GET count = 1 → DECRBY → INSERT reservation   ✓ B holds the unit
 t6  A: wakes, still "believes" it holds lock → DECRBY → INSERT  ✗ OVERSOLD

AFTER — same scenario
 t0  A: BEGIN; SELECT ... id=1107 FOR UPDATE SKIP LOCKED   (locks row 1107)
 t1  B: BEGIN; SELECT ... FOR UPDATE SKIP LOCKED            (1107 locked → skipped)
 t2  B: 0 rows returned → SoldOut  ✓ exactly one winner, enforced by the row lock
 t3  A: UPDATE 1107 → held; COMMIT  ✓

There is no t-where-both-win in the after case, because the row lock is held by the storage engine for the life of the transaction — no external clock, no expiry to outrun.

---

Aggregate Mattrx metrics

Metric	Before (Redis + Redlock)	After (MySQL SKIP LOCKED)	Delta
Oversells / month	40–60	0	eliminated
Reservation p95	210 ms	34 ms	−84%
Reservation p99	540 ms	61 ms	−89%
Sustained throughput / instance	~600 RPS	1,400 RPS	+133%
Lock-wait timeouts / day	~900	<5	−99%
Deadlocks / day	~40 on naive SQL	<2	controlled
Reconciliation job	9–14 min nightly	removed	−100%
Claim query p95	~9 ms	<1 ms	index
Datastores to operate	2 (Redis + MySQL)	1	−1
Infra cost	+$180/mo Redis	$0	−$180/mo
Defensive "sync" LOC	~340	0	deleted
On-call inventory pages / mo	~3	0	eliminated

The dollar figure ($180/month) is small; that was never the point. The point is one fewer distributed-systems failure domain and a correctness guarantee we can explain in one sentence: a unit is taken iff its row's lock is held or its status isn't available.

---

Pre-merge checklist

Before you ship a SKIP LOCKED reservation path, walk this list:

• One row per reservable unit (or per slot), not a single counter row.
• Composite index covering (pool_id, status, id) so the claim is an index range scan — EXPLAIN confirms no filesort, no full scan.
• ORDER BY id (or any stable order) in the claim SELECT so lock acquisition order is deterministic.
• FOR UPDATE SKIP LOCKED with LIMIT = quantity; treat "got fewer rows than requested" as SoldOut.
• The claim, the held update, and the reservation insert are in one transaction.
• Expiry is self-healing: the claim query also picks up held rows past hold_expires_at.
• Isolation level READ COMMITTED on the reservation connection.
• Retry only on 1213 (deadlock) / 1205 (lock wait), bounded to ~3 attempts with small jittered backoff.
• A unique index / check constraint as a hard backstop against double-sale, independent of app logic.
• Bounded connection pool + short innodb_lock_wait_timeout so stalls fast-fail instead of cascading.
• Janitor sweep (if any) also uses SKIP LOCKED so it never blocks live reservations.
• Load test with mid-transaction process kills to prove there's no drift to reconcile.

---

Honest stuff — when NOT to do this, and what we'd change

No technique is free. Where this one costs you, and where it isn't the answer:

1. Row-per-unit explodes for fungible, high-cardinality stock. 200 ad slots → 200 rows, fine. 5 million identical SKUs → 5 million rows is wasteful. For truly fungible inventory, a bounded counter with a guarded UPDATE ... WHERE available >= qty (relying on the row lock + the WHERE to reject oversell) is simpler than row-per-unit. Use row-per-unit when units are distinguishable or individually addressable (specific seats, specific slots, specific serials).

2. SKIP LOCKED is not portable everywhere. MySQL 8.0+, PostgreSQL, and Oracle have it. SQL Server's equivalent is the READPAST table hint with UPDLOCK, with different semantics. MySQL 5.7 doesn't have it at all — we had to be on 8.0. If you're stuck on an older engine, this whole approach is off the table.

3. You can still create a hot spot with one pool + tiny stock. If a pool has 2 units and 1,000 simultaneous reservers, 998 of them legitimately get SoldOut instantly (good), but the design's throughput advantage only shows when there's free inventory to spread claims across. For flash-sale "1 item, 100k people," a queue/token-bucket in front is still the right shape — SKIP LOCKED is for scarce-but-not-singular inventory.

4. Long-held holds reduce effective concurrency. A 10-minute hold ties up a row for 10 minutes. That's a business choice, not a tech one, but if holds are long and stock is thin, you serialize on availability regardless of locking cleverness. We keep holds at 10 min and lazily reclaim aggressively.

5. We kept Redis — for what it's actually good at. We did not delete Redis from Mattrx; we deleted it from the correctness path. It still caches read-heavy availability counts for the marketplace browse page (eventually-consistent, never used to make a reservation decision). The rule we landed on: Redis for speed, MySQL for truth, and never the two confused.

6. The unique-index backstop can surface as a user error if logic is wrong. A 1062 duplicate-key on the sale ledger means "your code tried to oversell." We treat it as a 500 + alert, not a silent retry — it should be impossible, so if it fires, something upstream is broken and we want to know.

7. What we'd do differently: start with the row-per-unit + SKIP LOCKED model on day one. The Redis design wasn't a bad decision given what the team knew in 2023 — it was the cargo-culted default ("reservations → Redis"). The lesson isn't "Redis bad." It's "don't add a coordinator that can't join the transaction your invariant lives in."

---

The closing mental model

Put the lock where the truth is. Inventory correctness is a property of one transaction over one row. Any design that needs a second datastore to coordinate access to the first has, by construction, a window where they disagree — and that window is your oversell.

Three habits this leaves you with:

1. Before adding a coordinator, ask if the data store can coordinate itself. Most "we need a distributed lock" problems are "we need one transaction" problems wearing a costume.
2. Reach for SKIP LOCKED whenever you'd otherwise serialize on a hot row. Queues, job claims, reservations, outbox draining — all the same shape: many workers, claim-distinct-rows, never block.
3. Make the invariant a constraint, not a convention. A unique index can't be paged at 3 a.m. and can't forget. If correctness matters, let the engine enforce it.

---

Further reading

• Outbox Pattern — A Complete Guide with Order Processing Example — the other half of reliable reservations: how the "payment succeeded → convert hold to sale" event leaves MySQL exactly once.
• SAGA Pattern in Microservices — A Complete Guide — when a reservation spans services and you can't wrap it in one transaction, this is the compensating-action model you fall back to.
• Data Access in .NET — EF Core, LINQ, and Dapper — why we drop to Dapper on the reservation hot path and keep EF Core for everything else.
• Indexing Strategies for Postgres at the 100M-Row Mark — the composite-index reasoning that makes the claim query a sub-millisecond range scan (Postgres, but the index logic is identical in MySQL).
• EF Core in Production: The N+1 Trap and How to Spot It — the diagnostic mindset (EXPLAIN, measure, don't guess) applied to the data layer.
• MySQL Reference Manual — "Locking Reads" (SELECT ... FOR UPDATE and SKIP LOCKED semantics).
• Martin Kleppmann — "How to do distributed locking" (why Redlock is for efficiency, not correctness).

---

Stuck on overselling, lock-wait storms, or a Redis layer you suspect you don't need? Email randhir.jassal@gmail.com with your reservation schema and your p95 — happy to point at which of these techniques applies to your inventory shape.