A payment consumer stores every message ID it has successfully processed in a 'processed_messages' database table, and checks that table before applying a payment. What race condition can still cause a duplicate payment if two copies of the same message arrive at nearly the same time, and how would you close it?

## The sequence that looks correct on paper The naive implementation of a processed-message table looks correct on paper: `SELECT 1 FROM processed_messages WHERE message_id = ?`; if not found, do the payment, then `INSERT INTO processed_messages (message_id) VALUES (?)`. Read individually each statement is fine, but the sequence is a textbook **check-then-act race condition**. If two copies of the same logical message are being handled at overlapping times — say - the broker redelivered a message because an ack was lost in transit, and the original processing hadn't finished yet, or - two consumer instances in the same consumer group both somehow received a copy due to a rebalance glitch — both executions can run the SELECT before either has run the INSERT. Both see 'not found,' both conclude they're the first to handle it, both charge the card, and only afterward do both INSERTs race to write the same `message_id`, with one succeeding and the other, if unprotected, either erroring out too late to prevent the double charge or silently overwriting. ## Why the naive version survives testing **Why the naive version exists:** it's the intuitive translation of 'remember what you did' into code, and it works fine in testing where messages are processed serially by a single thread with no true concurrency. It only breaks under real concurrent or overlapping redelivery, which is exactly the condition integration tests rarely reproduce; this is a common reason dedup bugs escape to production despite passing all unit and integration tests. ## The fix: one atomic claim The fix is to collapse the check and the mark into a single atomic operation, ideally in the same transaction as the side effect itself. Concretely: 1. Put a `UNIQUE` (or `PRIMARY KEY`) constraint on the `message_id` column in `processed_messages`. 2. Then in one database transaction attempt to insert the `message_id` and perform the payment write together. 3. If the INSERT violates the unique constraint, the database itself tells you someone already claimed this `message_id`, and you catch that specific error and treat it as a no-op skip-and-ack, never reaching the payment step at all. This converts a race that used to depend on timing into a **serialization point enforced by the database's own concurrency control** (row-level locking / unique index), which is far more reliable than an application-level if-check because the database guarantees only one of two concurrent inserts for the same key can succeed. An equivalent idiom in Postgres is `INSERT INTO processed_messages (message_id) VALUES ($1) ON CONFLICT (message_id) DO NOTHING`, then checking the row count returned: | Row count | What it tells you | |---|---| | zero rows affected | already processed, skip | | one row affected | you own this message, proceed | ## Claim before, or claim after **Trade-offs:** - **Claim first.** Doing the insert-and-check first, before the business side effect, means you claim the message before you know the business logic will succeed; if the payment step then fails for an unrelated reason (card declined, downstream timeout) and the message needs to be retried, you must either delete your claim row on failure or design retries to bypass the dedup check for legitimate re-attempts, which reintroduces complexity. - **The alternative**, do the payment first, then the atomic insert-and-check as the final step gated by a transaction that wraps both, avoids the false-claim problem but requires the payment write and the `processed_messages` write to be transactionally atomic together (same database, or a two-phase/outbox pattern if they're different systems), which is not always possible if the side effect is a call to an external payment gateway rather than a local database write. ## What goes wrong under load **Failure modes in production:** - **Teams that skip the unique constraint** and rely purely on select-then-insert in application code see duplicate charges appear specifically under load spikes or after incidents that trigger mass redelivery, exactly the conditions where true concurrency is highest and the race window is most likely to be hit. - **Another failure mode is partial claim:** the INSERT succeeds but the process crashes before the payment call completes, leaving the `message_id` marked as processed forever even though the payment never happened; this needs either transactional atomicity between the two writes or a status column (`PENDING/COMPLETED`) with a reconciliation job that revisits stuck rows. ## The same shape behind an idempotency key **A concrete real-world pattern:** this is essentially what a database-backed idempotency-key implementation, like Stripe's, does internally. The idempotency key has a unique constraint, the first request to insert it wins and proceeds to do the real work, and any concurrent or later request with the same key either blocks briefly on the row lock or gets redirected to return the already-computed result once the first request commits.