The Same Payment Sent Twice: Idempotency and Retries, by Way of Santander's Christmas

On Christmas Day 2021, while most people were still unwrapping presents, Santander UK ran payments for around 2,000 corporate clients a second time. Roughly 75,000 transactions worth about £130 million went out twice, and the extra copies landed in accounts at Barclays, HSBC, NatWest and other banks, leaving the people and companies on the receiving end with money they were never owed. The bank’s public explanation was a “scheduling issue” and nothing more. Recovery went through the UK banking industry’s error recovery process, working with other banks to claw the duplicates back, which the bank expected to take several days. It also said none of its clients were left out of pocket.

There isn’t enough public detail to guess at the root cause, and I won’t try. What is worth looking at is the position the bank was left in. Once the second payment had been accepted on the other side as a new, well-formed instruction, there was little anyone could do in the moment, and what remained was reconciliation and manual recovery. Duplicates have to be dealt with before they are sent, or at the moment the other side books them. Past that point, what one line of SQL could have handled takes several days of work across banks.

Ordinary debits, credits and transfers between two systems run into the same problem, with far less money at stake and far more often. Networks time out, services restart, workers get killed, so retries will happen, and whether they are safe comes down to whether both sides agree on what “the same payment” means. The sections below go through it piece by piece. The examples use simplified tables, were run on PostgreSQL 16.15, and play the receiving side, the one that holds the balance and books the entries.

1. After a timeout, you don’t know whether it happened

The caller sends a debit and gets no reply. The request might never have arrived, the other side might still be working on it, or it might have finished with the reply lost on the way back. From the caller’s side all of these look like a single timeout. This is the Two Generals’ Problem in API form, and since an acknowledgement can be lost as easily as the request, more round trips don’t settle it.

That leaves two options: retry with the same identifier and let the other side work out whether it has already done the job, or use the same identifier to ask for the status. Both depend on the same thing. From the first time it is sent, the transaction has to carry an identifier that never changes.

That identifier should be created when the decision to debit is made and written to your own database, and every retry should read the same value back. If the retry code creates a fresh identifier just before sending, or a scheduled job rebuilds its batch of payment instructions on every run with new identifiers each time, no amount of deduplication downstream will spot it. As far as the receiver can tell, these are two different transactions.

2. On the receiving side, keep the key and the debit in one transaction

The basic approach on the receiving side is a transaction table keyed on the identifier the caller supplies. The first time an identifier turns up, the operation runs and the result is stored, and any later request with the same identifier gets the stored result back:

CREATE TABLE wallets (
  account_id text PRIMARY KEY,
  balance   numeric NOT NULL CHECK (balance >= 0)
);

CREATE TABLE wallet_tx (
  scope        text NOT NULL,
  tx_id        text NOT NULL,
  op           text NOT NULL,
  request_hash text NOT NULL,
  amount       numeric,
  response     jsonb,
  created_at   timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (scope, tx_id)
);

INSERT INTO wallets VALUES ('a1', 1000);

The debit is a function that inserts the transaction row first and only touches the balance if the insert succeeds:

CREATE FUNCTION debit(p_scope text, p_tx text, p_account text, p_amount numeric)
RETURNS jsonb LANGUAGE plpgsql AS $$
DECLARE
  h   text := md5(concat_ws('|', 'debit', p_account, p_amount));
  r   wallet_tx;
  bal numeric;
  res jsonb;
BEGIN
  INSERT INTO wallet_tx (scope, tx_id, op, request_hash, amount)
  VALUES (p_scope, p_tx, 'debit', h, p_amount)
  ON CONFLICT DO NOTHING;

  IF NOT FOUND THEN
    SELECT * INTO r FROM wallet_tx WHERE scope = p_scope AND tx_id = p_tx;
    IF r.request_hash = 'tombstone' THEN
      RETURN r.response;
    END IF;
    IF r.request_hash <> h THEN
      RAISE EXCEPTION 'idempotency key reused with different request: %', p_tx;
    END IF;
    RETURN r.response || '{"replayed": true}';
  END IF;

  UPDATE wallets SET balance = balance - p_amount
  WHERE account_id = p_account AND balance >= p_amount
  RETURNING balance INTO bal;

  res := CASE WHEN bal IS NULL
              THEN jsonb_build_object('status', 'INSUFFICIENT_FUNDS')
              ELSE jsonb_build_object('status', 'OK', 'balance', bal) END;

  UPDATE wallet_tx SET response = res WHERE scope = p_scope AND tx_id = p_tx;
  RETURN res;
END $$;

Sending the same debit twice:

SELECT debit('op-a', 'tx-1001', 'a1', 100);
SELECT debit('op-a', 'tx-1001', 'a1', 100);
SELECT balance FROM wallets;

 {"status": "OK", "balance": 900}
 {"status": "OK", "balance": 900, "replayed": true}
 balance
---------
     900

The second call leaves the balance alone and returns what was stored the first time. The part people tend to miss is that failures have to be stored as well. Say the first attempt is rejected for insufficient funds, and the user then tops up. If the retry is evaluated afresh it goes through, while the caller may already have cancelled the order on the strength of the first failure. Stripe’s idempotency works the same way: it saves the status code and body of the first request whether it succeeded or failed, and replays even a 500.

The whole design rests on the transaction row and the balance update sharing one transaction. When two requests with the same identifier arrive together, the later one blocks on the primary key conflict and only gets its answer once the first has committed. In the test, session A runs the debit and waits three seconds before committing, while session B sends the same debit in between. As a timeline:

 a_start  13:22:49   A runs debit, gets {"status": "OK", "balance": 900}
 b_start  13:22:50   B sends the same debit, blocks on INSERT
 a_commit 13:22:52
 b_done   13:22:52   B gets {"status": "OK", "balance": 900, "replayed": true}

The balance is debited once. If A fails and rolls back, its transaction row goes with it, B’s insert succeeds and B does the work instead, which is still correct. Turn it round, though. If you write the transaction row in one transaction, commit, and only then debit the balance, a crash in between leaves a row that has no debit and no result, and every later retry is treated as already done. When the work can’t fit in one transaction, for instance because it calls another service halfway through, the row needs a pending state, and a retry that finds it pending should be told “in progress”, never “succeeded”.

3. Something in the key keeps changing

The primary key is (scope, tx_id), which looks simple enough. The trouble is how tx_id gets built. A common mistake is to fold the current session or token into it, usually on the grounds that the same transaction number might come up in different sessions. If the user’s token is replaced between two retries, this happens:

SELECT debit('op-a', 'tx-1002:sess-A', 'a1', 100);
SELECT debit('op-a', 'tx-1002:sess-B', 'a1', 100);
SELECT balance FROM wallets;

 {"status": "OK", "balance": 800}
 {"status": "OK", "balance": 700}
 balance
---------
     700

One debit, taken twice. Timestamps, retry counts, request IDs and signatures all change from one send to the next, and none of them belongs in the key. The key describes the business transaction, so it can only be made of things that stay fixed for that transaction, namely the caller and the transaction number. scope keeps different callers apart, so two of them can’t collide by happening to pick the same number. Its boundary needs some thought too. Cut it too finely, say one scope per session, and you have put the changing part back in.

4. Same key, different content

A bug on the caller’s side can give two different transactions the same identifier, for example an ID generator that starts counting from zero again after a restart. If the receiver only looks at the identifier, the second transaction is treated as a retry, gets the first one’s result and quietly disappears, with both sides believing it went through.

So alongside the identifier, store a hash of the request and compare it on every retry:

SELECT debit('op-a', 'tx-1001', 'a1', 500);

ERROR:  idempotency key reused with different request: tx-1001

Only the fields that define what the transaction means go into the hash, such as the account, amount, operation and order number. Timestamps and signatures stay out, for the same reason as in the previous section. Stripe documents the same behaviour: reusing a key with different parameters returns an error. This kind of error should be loud, because it nearly always points to a bug in the caller, and quietly returning the old result only hides it.

5. The refund arrives before the debit

The caller’s debit times out, and so does the status check, so it gives up on the order and sends a refund that names the debit it just tried. The refund gets through, while the debit is still sitting in some proxy’s retry queue and arrives a few seconds later.

If the receiver handles the refund by finding no original debit, replying “nothing to refund” and moving on, the late debit is processed as a new transaction. The caller has written the order off as cancelled, and the user’s money has been taken anyway.

The fix is for the refund to claim the original debit’s identifier as it goes, leaving a tombstone:

-- The original hasn't arrived: take its key now, so a late debit only ever gets CANCELLED
INSERT INTO wallet_tx (scope, tx_id, op, request_hash, response)
VALUES (p_scope, p_ref_tx, 'debit', 'tombstone', '{"status": "CANCELLED"}')
ON CONFLICT DO NOTHING;

The full refund function first applies idempotency on the refund’s own identifier and then deals with the original debit. If the tombstone insert succeeds, the debit has never been seen. If it fails, the debit already exists, so the function locks that row and adds the amount back only if its status is OK, marking it REFUNDED. Running it:

SELECT refund('op-a', 'rf-3001', 'tx-3001', 'a1');
SELECT debit('op-a', 'tx-3001', 'a1', 100);
SELECT balance FROM wallets;

 {"status": "OK", "refunded": 0}
 {"status": "CANCELLED"}
 balance
---------
    1000

The late debit gets CANCELLED and the balance doesn’t move. The normal order works too: a debit, a refund, a retry of that refund, and then a second refund of the same debit under a new refund identifier:

 debit  tx-3002            {"status": "OK", "balance": 900}
 refund rf-3002            {"status": "OK", "balance": 1000, "refunded": 100}
 refund rf-3002 (retry)    {"status": "OK", "balance": 1000, "refunded": 100, "replayed": true}
 refund rf-3002b           {"status": "OK", "refunded": 0}

Refunds get retried too, so a refund needs its own identifier and goes through the same idempotency path. The second refund under a new identifier is stopped because the original debit is already REFUNDED. The tombstone and the debit compete for the same primary key, so if they arrive at the same moment only one of them can write, and no extra locking is needed.

Settlement and refunds colliding is a similar situation with more states. Once a transaction is settled it can’t be refunded, and once it is refunded it can’t be settled. Those rules belong in explicit state transitions, checked on the receiving side against the state of that one row, without relying on the caller never sending things in the wrong order.

6. Replay protection and idempotency are separate layers

Public APIs usually add a signature layer as well, using a timestamp and a nonce so that a captured request can’t be sent again unchanged. That layer rejects a repeated nonce outright, which is the opposite of what the idempotency layer does with a repeated identifier, namely return the earlier result. With both in place, their order and their scope need to be worked out.

A legitimate retry should be signed again, with a fresh timestamp and nonce. It passes the replay check, and the idempotency layer then recognises it as the same transaction by its identifier. If the caller resends the old signature, the first layer rejects it as a replay, and it looks as though the other side refused the transaction. The nonce check should also be scoped to the API path. Otherwise two requests with identical bodies sent to different endpoints in the same second, such as a status query and a debit, will be mistaken for a replay.

7. How long to keep the keys

Once an idempotency record is deleted, the same identifier arriving again is treated as a new transaction. Stripe keeps keys for at least 24 hours before they may be pruned.

The retention period has to be longer than every retry path put together: the longest retry window of the caller’s workers, the time a message might sit in a queue, and the time it takes someone to resend by hand. In accounting systems the records are usually kept forever anyway, since they are part of the ledger. If the idempotency table and the transaction history are the same table, as they are here, storage cost is the only thing left to think about.

Notes

A timeout means the outcome is unknown. The request may not have arrived, may still be running, or may have finished with the reply lost. The only way forward is to retry or query with the same identifier.
Create the transaction identifier when the decision to debit is made and persist it. Every retry reads the same value. If it is created just before sending, the receiver can’t recognise duplicates.
On the receiving side, keep the transaction row and the balance change in one transaction, and let the primary key conflict queue concurrent requests with the same identifier.
Store failures as well. A retry of a debit that failed for insufficient funds should get the same failure back.
Keep anything that changes per send out of the key: sessions, tokens, timestamps, signatures.
Reject the same key with different content. The hash covers only the fields that define the transaction.
When a refund arrives first, leave a tombstone on the original identifier so a late debit gets cancelled. Refunds need their own identifier and go through idempotency too.
Replay protection rejects a repeated nonce, idempotency recognises a repeated identifier. Retries should be re-signed, and the nonce check should include the path.
Keep keys longer than every retry path. Accounting systems usually keep them forever.