Double click, double refund: stopping duplicate transactions in a finance system

The short answer

If a payment, refund or cancellation can be recorded twice, assume it will be. People double-click, browsers retry and slow pages get submitted again. The only check that holds when two requests arrive at the same moment is one the database enforces: a row lock, a unique index, or both. Checks in application code still help, but they can’t be the last line of defence.

How do duplicate transactions happen?

On a student information system we worked on for a group of universities, finance staff noticed several rows with the same receipt number on their cancelled-transactions screen. Cancelling a student’s module registration deletes the fees attached to it, copies each deleted payment into a cancelled-transactions table, and credits a refund to the student’s wallet. Somewhere in that chain, the same payment was being cancelled more than once.

Two explanations fitted the symptom, and the data could tell them apart:

1. Two different payments sharing a receipt number. Receipt numbers were built from a timestamp and a few random digits, so two payments in the same second could collide. If that were the cause, the duplicate rows would point at different original payments.
2. The same payment cancelled twice. In that case, every copy would point at the same original payment.

They all pointed at the same payment, which ruled out collisions. Most copies were created seconds apart, not milliseconds, which suggests a second request arriving while the first was still running: a re-click on a slow page, or a retry. We also found code paths that could reach the same fee twice within one request. Whatever the trigger, nothing stopped the second attempt from doing everything the first one did, including crediting the refund.

Why don’t the usual fixes work?

There are three fixes most teams reach for first. Each one helps, and none of them is enough on its own.

The last one is the most tempting, because it works on a developer’s machine and on a single server. Add a background worker container, or a second copy of the app behind a load balancer, and each process holds its own lock, so two requests can run side by side again. An application-wide lock also makes every transaction wait for every other one, related or not.

There’s a quieter trap for anyone relying on a unique constraint they think they have. In older versions of Rails, writing unique: true on a column inside create_table doesn’t create an index; the option is silently ignored. You need index: { unique: true } or a separate add_index. Check your schema, not your migrations.

How do you stop duplicate transactions for good?

We made the database responsible for the guarantee, and fixed it once, in the shared function that the web app, the mobile app and the admin tools all call to cancel a registration. There are four layers, starting with the one that matters most:

1. Lock the row. The cancellation takes a database lock on the registration before doing anything (SELECT … FOR UPDATE, which Rails exposes as with_lock). A second request for the same registration waits until the first one finishes. It works across processes, servers and containers, because the database holds the lock.
2. Treat “already done” as success. When the second request gets the lock, the registration is already gone. Instead of an error, it reports that the registration was cancelled, which is true. The action is now safe to repeat.
3. One transaction for the whole change. Removing fees, recording the cancellation and crediting the refund happen together or not at all, so a failure halfway can’t leave a refund without its cancellation.
4. Guards on top. A uniqueness check refuses a second cancelled record for the same payment, and the page ignores a second click while the first request is in flight. These catch mistakes early, but the lock is what makes it correct.

def cancel_registration(registration)
  registration.with_lock do     # SELECT ... FOR UPDATE
    registration.refund_fees!   # fees, cancellations, refund
    registration.destroy!
  end
rescue ActiveRecord::RecordNotFound
  :already_cancelled            # a repeat request; same outcome
end

Where the thing that must not repeat has a natural key, such as a bank’s transaction ID or a refund reference, back it with a unique index too. The lock protects one action; the index catches every path you forgot about. On the same project, refund references were built from a random number between 0 and 99 plus the student and the amount, so two refunds of the same amount to the same student had a one-in-a-hundred chance of colliding. We replaced the random part with a long random string and rewrote the old references, so a unique index can be added safely.

What about duplicates already in the data?

Fixing the code stops new duplicates; it doesn’t remove old ones. We wrote tasks that list every duplicate group, both the way finance sees them on screen and by original payment, as a spreadsheet finance could work through. That matters, because each duplicate cancellation may have come with a duplicate refund, and deciding what to do about money already credited is a finance decision, not an engineering one.

The order is always the same: find the duplicates, let the people who own the money decide, clean up, then add the unique constraint. Add the constraint first and the migration fails on the duplicates that are already there.