Make it single-use in the WHERE clause

Heckin Quest pays dog walkers in Pup Points, which buy rewards at local businesses. Buying one issues a code, and the code is marked used at the counter. A code that can be used twice is a reward the business gives away twice.

The obvious way to write "use it once" is two steps:

row = RedemptionCode.get(code=code)
if row.status == "issued":
    row.status = "redeemed"
    row.save()

Read the row, see that it's unused, write it as used. Two requests that arrive together both read issued, both pass the check, and both write. That is a check-then-act race, and it has nothing to do with the language or the framework. Any time the decision and the write are two statements, something else can run between them.

The WHERE clause is the check

The redemption endpoint, from the first commit that had one (3a4cd3a, 2026-09-02), does it in one statement:

won = (
    RedemptionCode.update(status="redeemed", redeemed_at=now, redeemed_by=user.id)
    .where(
        RedemptionCode.id == row.id,
        RedemptionCode.status == "issued",
        RedemptionCode.expires_at > now,
    )
    .execute()
)
if won != 1:
    return None  # the handler answers 409, "Code already used"

Which is this SQL:

UPDATE redemptioncode
   SET status = 'redeemed', redeemed_at = ?, redeemed_by_id = ?
 WHERE id = ? AND status = 'issued' AND expires_at > ?

The database runs writes to a row one at a time. The first UPDATE matches the row and changes it. The second runs after it, and by then its WHERE no longer matches anything: zero rows affected. The row count is the answer. There is no lock for the application to hold and no transaction boundary to reason about, because the condition and the write are the same statement.

This was not a race found in production and fixed. It was designed in. The project plan, written the day before, already said "single-use guaranteed by the DB, not by app logic", and its acceptance test was "double-redeem rejected under concurrent attempts". The endpoint does still read the row first, so it can answer "not found" or "expired" with the right message, and a request can be turned away by that read. But the read only chooses the error message. The WHERE clause decides.

The purchase that issues a code uses the same move on the balance:

UPDATE dog SET pp_balance = pp_balance - ? WHERE id = ? AND pp_balance >= ?

Zero rows means "Not enough Pup Points for this reward", a 409. The code is created in the same transaction, so a spend can never happen without a code or a code without a spend.

The tests race both. Four threads wait on a barrier and then mark the same code used at once; the test asserts exactly one 200 and exactly three 409s. Three threads race purchases against a balance that covers one; exactly one succeeds, the balance ends at zero, and one code exists.

What the naive version does

It is easy to believe the race is theoretical, so it is worth running. A short script puts one issued row in a SQLite file with the settings the app's database library gives it (the default journal, a five-second busy timeout, one connection per thread), releases N threads at once, and has each try to redeem it. There is no artificial delay between the read and the write.

Even with two threads and nothing slowing them down, the naive version gave the reward away twice in 61 trials of 100. At four threads it was nearly every trial. At 32 threads one "single-use" code was redeemed 26 times on average. The guarded UPDATE produced exactly one winner in all 500 trials, without an error.

A transaction wrapper is not the fix

The tempting fix for the naive version is to wrap it in a transaction. The same repository shows why that isn't enough, three weeks later, in a different feature.

Walks in Heckin Quest turn up encounters, and resolving one pays the dog XP. The resolve endpoint checked the encounter's status in Python, then saved inside a transaction:

encounter = _owned_encounter(encounter_id, user)
if encounter.status == "resolved":
    return ...  # idempotent: no re-pay

# The payout and the status flip move together.
with db.atomic():
    encounter.status = "resolved"
    encounter.save()
    dog.xp += xp
    dog.save()

The transaction made the payout and the status flip atomic with each other. It did nothing for the check, which ran before the transaction started. 0d5874b (2026-09-21) describes the result: "two racing resolves (a double-tap, or a retry racing the original) could both pass the check and both pay." The fix is the same move as redemption, a guarded UPDATE that matches only a still-pending encounter, where only the winner pays. Its new test, a four-way race, "fails 3/3 on the old code".

Wrapping the check too doesn't rescue it on SQLite either. The right panel above is the naive version with the read and the write inside one transaction. It never redeemed twice. SQLite refused the second writer's lock outright, even with a five-second busy timeout. But the losers got database is locked instead of "already used", which in an API is a 500 instead of a 409. The guarded UPDATE gets both right: one winner, and a clean answer for everyone else.

The same commit fixed a mirror image of the race. Both payouts did dog.xp += n; dog.save(), and a whole-row save writes every column back, including a pp_balance read before the request. A reward bought mid-request was silently refunded: the commit's test measured "11 instead of 4". The guarded spend was correct, and a different write to the same row undid it. Payouts are now increments in the database (Dog.xp + n). A guard on one write is only as good as every other write to that row.

The flip side: the loser waits on the database

Letting the database pick the winner means the loser waits on the database, and what it sees while it waits is your error handling. The test suite uses SQLite's shared-cache in-memory mode, where contention doesn't queue politely behind the busy timeout. It fails at once with "table is locked". So every guarded write runs inside a small helper, retry_on_locked, that retries a few times with a growing sleep, so a loser comes back as a clean 409 instead of a 500.

On the first afternoon that budget changed twice:

3a4cd3a  12:14   5 attempts, 20 ms steps   0.20 s of sleep at most
ca9d3cc  13:12   8 attempts, 30 ms steps   0.84 s
b2c0348  13:31  12 attempts, 40 ms steps   2.64 s

ca9d3cc says why: the concurrency test flaked on Python 3.13 in CI, a loser surfaced a 500 instead of its 409, and "The single-use guarantee held throughout". The second widening, nineteen minutes later, rode along in a commit about backups and rate limits without a mention.

Then the part worth reading the traceback for. The failed CI run's error was raised in the authentication dependency's user lookup, which runs before the endpoint and was never inside retry_on_locked. No budget could have absorbed it. On 2026-09-19 the same test failed again in CI, the same way, at the same line, under the widest budget yet.

So the database's half of the bargain held every time: one winner. The work was all in the other half, deciding what the losers see and making sure every read on their path can wait as patiently as the write. The commit's own note on production is that "production file-backed SQLite barely touches this path". It is a test-suite problem, which is a fine place to find it.

The rule

• Put the condition in the statement that acts, and read the row count. One row means you won. Zero rows means someone else did, and that is an answer, not an error.
• A transaction around the write is not a check. If the read that decides happens outside it, nothing changed.
• Every write to the row has to respect the guard. A whole-row save of a stale object can undo a perfectly guarded one.
• Decide what the loser sees, and read the traceback before you widen a retry. The guarantee was never in question here. The 500s came from a read nobody had thought of as part of the race.