100 rare items, 140 players got one: a read-then-write race when thousands claimed at once

For a seasonal event, our mobile game offered 100 copies of a limited edition mount, free to claim at 20:00 UTC for players above level 30. We announced it a week in advance. At 20:00:00, about 20,000 players tapped "Claim" within the first five seconds. When the event team checked the next morning, the database showed 140 players owning the mount. Social media had already noticed.

The claim endpoint

def claim_limited_item(player_id: int, item_id: int) -> ClaimResult:
    with db.transaction():
        stock = db.scalar("SELECT remaining FROM limited_stock WHERE item_id = %s", item_id)
        if stock <= 0:
            return ClaimResult.SOLD_OUT
        if db.scalar("SELECT 1 FROM inventory WHERE player_id = %s AND item_id = %s", player_id, item_id):
            return ClaimResult.ALREADY_OWNED
        db.execute("UPDATE limited_stock SET remaining = remaining - 1 WHERE item_id = %s", item_id)
        db.execute("INSERT INTO inventory (player_id, item_id, acquired_at) VALUES (%s, %s, NOW())",
                   player_id, item_id)
    return ClaimResult.OK

It reads the remaining stock, checks ownership, decrements, inserts. All inside a transaction. The team had assumed the transaction made it safe.

What the data showed

SELECT remaining FROM limited_stock WHERE item_id = 9001;
-- -40

SELECT count(*) FROM inventory WHERE item_id = 9001;
-- 140

SELECT player_id, count(*) FROM inventory WHERE item_id = 9001 GROUP BY player_id HAVING count(*) > 1;
-- 3 rows: three players owned two copies each

Stock was negative, and three players had claimed twice by tapping repeatedly.

Reproducing

A load test against staging with 2,000 concurrent claims for 100 items reproduced it every time, giving between 112 and 160 successful claims. The application logs showed many requests reading remaining = 1 or remaining = 2 at the same moment.

What we ruled out

• Replication lag on reads. All queries ran on the primary inside the same transaction.
• A client retry bug. The duplicate claims came from separate requests, each a real tap.
• The isolation level being too low. MySQL's default REPEATABLE READ was in use; it does not prevent this pattern, as explained below.

Root cause

The endpoint used a check-then-act pattern: it read remaining, decided based on the value, then updated. Under concurrency, many transactions read the same positive value before any of them decremented it. REPEATABLE READ gives each transaction a consistent snapshot for plain SELECTs, which means each one saw stock available, and the decrement remaining = remaining - 1 succeeded for all of them, driving the counter below zero. The ownership check had the same race, so a player tapping twice could pass it twice.

Transactions do not serialise read-then-write by default

Inside a transaction, a plain SELECT in InnoDB is a consistent non-locking read. Two transactions can both read remaining = 1, both decide to proceed, and both run the UPDATE. The second UPDATE waits for the first transaction's row lock, then applies to the current value, decrementing again. Nothing in the code re-checks the condition at update time.

This is the lost update and write skew family of anomalies. They need either:

• a condition enforced in the write itself (atomic conditional update);
• an explicit lock on the read (SELECT ... FOR UPDATE);
• a database constraint that rejects the invalid state;
• or a serializable isolation level with retries.

Hot rows

All 20,000 claims competed for one row in limited_stock. Any correct database solution serialises on that row, so its latency under contention determines throughput. For very large bursts, it helps to reject most requests before they reach the database.

Who is affected

Any limited-quantity flow: flash sales, ticket releases, coupon redemption limits, reward claims, seat booking, quota checks. The race needs concurrency, so it rarely shows in testing or at normal load.

Environment

Step 1: make the decrement conditional and atomic

The stock check moves into the UPDATE. The database applies it under the row lock, so only updates that find stock remaining succeed:

def claim_limited_item(player_id: int, item_id: int) -> ClaimResult:
    with db.transaction():
        updated = db.execute(
            "UPDATE limited_stock SET remaining = remaining - 1 WHERE item_id = %s AND remaining > 0",
            item_id,
        ).rowcount
        if updated == 0:
            return ClaimResult.SOLD_OUT
        try:
            db.execute("INSERT INTO inventory (player_id, item_id, acquired_at) VALUES (%s, %s, NOW())",
                       player_id, item_id)
        except UniqueViolation:
            raise AlreadyOwned()          # rolls back the decrement with the transaction
    return ClaimResult.OK

rowcount tells us whether this request got an item. There is no separate read to race on.

Step 2: enforce one per player with a constraint

ALTER TABLE inventory ADD UNIQUE KEY uq_inventory_player_limited (player_id, item_id);

(For items players can own multiple times, the constraint lives on a separate limited_claims table instead.) The unique key turns "already owned" from a check into a guarantee. When a second insert for the same player fails, the transaction rolls back, including the stock decrement, so the item stays available for someone else.

A CHECK (remaining >= 0) constraint on limited_stock is a final safety net: even if some future code path forgets the condition, the database refuses negative stock.

Step 3: shed load before the database with Redis

With the database logic correct, 20,000 requests still queued on one hot row, and p99 latency for claims reached several seconds. We added a Redis counter as a gate: requests first decrement it, and only the first 100 proceed to the database.

def claim(player_id: int, item_id: int) -> ClaimResult:
    left = redis.decr(f"limited:{item_id}:left")
    if left < 0:
        return ClaimResult.SOLD_OUT          # fast path for the other 19,900 players
    try:
        return claim_limited_item(player_id, item_id)
    except AlreadyOwned:
        redis.incr(f"limited:{item_id}:left")   # give the slot back
        return ClaimResult.ALREADY_OWNED

Redis DECR is atomic, so exactly 100 requests see a non-negative value. The database remains the source of truth: if Redis and the database ever disagree, the database's conditional update and constraints still prevent overselling. A reconciliation job after each event compares the two.

Alternatives and trade-offs

• SELECT ... FOR UPDATE on the stock row fixes correctness too, but holds the lock longer than a single conditional update.
• A queue that processes claims one by one gives fairness by arrival order and smooth database load, at the cost of asynchronous results ("your claim is being processed").
• Pre-assigned tokens: generate 100 claim tokens and let requests pop one from a Redis list. Equivalent to the counter, and makes each item individually traceable.

How to test the fix

# 2,000 concurrent claims from 1,800 distinct players (some duplicates)
k6 run -e ITEM=9001 -e VUS=2000 claim-burst.js
mysql -e "SELECT remaining FROM limited_stock WHERE item_id=9001; SELECT count(*) FROM inventory WHERE item_id=9001;"
# 0 and 100, every run

Confirming in production

The next limited release, 500 items with about 60,000 claims in the first ten seconds, ended with exactly 500 owners, no duplicates and stock at zero. Claim p99 latency was 90 ms, because 59,500 requests were answered from Redis.

Regression guard

• Every limited-quantity table has a non-negative CHECK constraint and a uniqueness constraint per player.
• A concurrency test with a burst of claims runs in CI against a real MySQL container.
• Code review guideline: any "check a value, then write" sequence must move the condition into the write or take a lock.

Security implications

Races in limited resources are also abused deliberately: scripted clients send many parallel requests to claim multiple items or exceed per-account limits. Atomic updates and database constraints remove the race for everyone, scripted or not. We also added per-account rate limiting on claim endpoints.

Prevention checklist

• Never read a quantity and then write based on it without a lock or a conditional write.
• Put conditions in the UPDATE and check affected rows.
• Enforce invariants (non-negative stock, one per player) with constraints.
• Shed load for hot rows with an atomic counter in front of the database.
• Load test limited releases with realistic bursts before the event.

References

• MySQL 8.0, Consistent nonlocking reads: https://dev.mysql.com/doc/refman/8.0/en/innodb-consistent-read.html
• MySQL 8.0, Locking reads: https://dev.mysql.com/doc/refman/8.0/en/innodb-locking-reads.html
• MySQL 8.0, CHECK constraints: https://dev.mysql.com/doc/refman/8.0/en/create-table-check-constraints.html
• Redis, DECR command: https://redis.io/docs/latest/commands/decr/