Duplicate keys vs. duplicate work

Lesson 3 of the mid-level course "Service objects: idempotency" showed a check-then-store shape: look up the idempotency key, and if it's missing, do the work, then save the key. It used a hypothetical payment service. Here is the same shape applied to a hypothetical XP award:

existing = IdempotencyKey.find_by(key: idempotency_key, operation: "award_xp")
return existing_result if existing

xp = ExperiencePoint.create!(user:, amount:) # the work

IdempotencyKey.create!(key: idempotency_key, operation: "award_xp", response_body: { id: xp.id })

That shape passes any test that calls it once. This lesson calls it five times at once, with the same key, and counts what got created.

Five requests, one key, in a real database

The setup: two disposable tables in this project's local development database. One is shaped like idempotency_keys, with a unique index on key. The other is shaped like an XP ledger. Five threads, each checking out its own connection from the connection pool, all run the check-then-store code above with the identical key:

idempotency key rows: 1   (the unique index correctly allowed only 1)
XP records created:   4   (should be 1)

Four XP rows where there should be one: three duplicates, from code that has an idempotency key, checks it and stores it. The unique index worked as designed. It stopped a second idempotency_keys row from being written. It didn't stop the XP rows that ran ahead of it.

Why four and not five? The local pool holds five connections and the runner's main thread held one, so the fifth thread waited for a free connection. By the time it got one, the key existed. Rerun it and you get four almost every time.

Why the index didn't help

Walk the threads through the code. The four that got a connection each reach IdempotencyKey.find_by before any of them has written anything, so each sees "no existing key" and falls through to the work. ExperiencePoint.create! runs four times, unguarded. Nothing in that line knows about the other threads doing the same thing at the same moment.

Only afterwards, at IdempotencyKey.create!, does the unique index get a say. By then the only decision left is which inserts lose a race that has already happened. rescue ActiveRecord::RecordNotUnique catches that they lost. It does nothing about the XP they already created.

This isn't a bug in one service. The code does what a check-then-store guard promises: it deduplicates the key table. It never promised to deduplicate the moment the work happens, because nothing in the shape claims that moment before the work starts.

The real Enrollment::AwardXpService in this app doesn't have this problem. It never checks first. It inserts the XP row with insert ... unique_by, so the database's unique index decides before anything else happens. Lesson 2 builds the general version of that idea.

The gap this course is about

A check-then-store key answers "have I seen this key before?" A production idempotency guard has to answer a stricter question: "has anyone, right now, including a request running in parallel with me, already started this work?" Those sound like the same question. The five-thread test shows they aren't, and the difference is the rows that shouldn't exist.

Closing that gap needs no new concept, only a new order for the three steps you already know. Check, work, store becomes claim, work, complete, and the unique index has to sit on the claim step to mean anything.

What the seven lessons cover

Lesson 2 reorders the three steps and reruns the same five-thread test to show the reorder closes the gap. Lesson 3 asks what the threads that lose the claim should do while the winner is still working: block, poll or fail fast, and what a production API like Stripe's does. Lesson 4 covers a case the earlier course didn't raise: the same key arriving with different parameters, and why silently replaying the old result is the wrong answer. Lesson 5 compares where the claim should live: the database table this course builds, or a reservation in this app's Solid Cache store. Lesson 6 goes one level up, to how the key is generated, and a client-crash scenario that a random UUID handles badly. Lesson 7 puts the whole guard together and hands you a version to build yourself in plain Ruby.