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”. The problem has since been fixed, and no further technical detail was published. 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.

Correction (2 November 2026, 18:45 Taipei): debit now rejects NULL, non-positive and non-finite amounts, the table has a matching constraint, and the amount’s scale is normalised before hashing; the nonce advice now calls for a fresh high-entropy nonce per request, deduplicated on caller plus nonce (suggested by Codex (gpt-6-astra); the amount check decided by a vote of Codex (gpt-6-astra), Grok and Claude Fable, the rest checked by Claude). The full refund function is now shown, and the refund demo is marked as starting from fresh data with a balance of 1000 (suggested by Claude and Codex (gpt-6-astra), checked by Claude). Stripe’s behaviour is limited to API v1 (suggested by Claude, checked by Codex (gpt-6-astra)). The bank’s explanation now says the issue was fixed (suggested by Claude, checked by Claude).

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),
  -- A debit amount must be finite and positive; tombstones carry no amount
  CHECK (op <> 'debit' OR request_hash = 'tombstone'
         OR (amount IS NOT NULL AND amount > 0 AND amount < 'Infinity'))
);

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;
  r   wallet_tx;
  bal numeric;
  res jsonb;
BEGIN
  -- Reject NULL, negative, zero, NaN and Infinity (PostgreSQL sorts NaN above Infinity)
  IF p_amount IS NULL OR NOT (p_amount > 0 AND p_amount < 'Infinity') THEN
    RAISE EXCEPTION 'invalid amount: %', p_amount;
  END IF;
  -- 100 and 100.00 are the same amount, so drop trailing zeros before hashing
  h := md5(concat_ws('|', 'debit', p_account, trim_scale(p_amount)));

  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 $$;

The amount is checked first, so a negative debit can’t turn into a credit. numeric keeps the scale it was given, so trim_scale normalises the amount before hashing, and 100 and 100.00 count as the same request.

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 API v1 idempotency works the same way: once endpoint execution begins, it saves the status code and body of the first request whether it succeeded or failed, and replays even a 500. Results aren’t saved when parameters fail validation or the request conflicts with one already running. API v2 instead retries failed or partially completed work to completion when possible and returns the updated result.

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:

CREATE FUNCTION refund(p_scope text, p_tx text, p_ref_tx text, p_account text)
RETURNS jsonb LANGUAGE plpgsql AS $$
DECLARE
  h   text := md5(concat_ws('|', 'refund', p_account, p_ref_tx));
  r   wallet_tx;
  bal numeric;
  res jsonb;
BEGIN
  -- The refund's own identifier goes through idempotency first, as in debit
  INSERT INTO wallet_tx (scope, tx_id, op, request_hash)
  VALUES (p_scope, p_tx, 'refund', h)
  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 <> h THEN
      RAISE EXCEPTION 'idempotency key reused with different request: %', p_tx;
    END IF;
    RETURN r.response || '{"replayed": true}';
  END IF;

  -- 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;

  IF FOUND THEN
    res := jsonb_build_object('status', 'OK', 'refunded', 0);
  ELSE
    -- The debit already exists: lock its row and refund only an OK debit
    SELECT * INTO r FROM wallet_tx
    WHERE scope = p_scope AND tx_id = p_ref_tx
    FOR UPDATE;

    IF r.response->>'status' = 'OK' THEN
      UPDATE wallets SET balance = balance + r.amount
      WHERE account_id = p_account
      RETURNING balance INTO bal;

      UPDATE wallet_tx SET response = r.response || '{"status": "REFUNDED"}'
      WHERE scope = p_scope AND tx_id = p_ref_tx;

      res := jsonb_build_object('status', 'OK', 'balance', bal, 'refunded', r.amount);
    ELSE
      res := jsonb_build_object('status', 'OK', 'refunded', 0);
    END IF;
  END IF;

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

The refund demo starts from fresh data: the tables are recreated and a1 is back at a balance of 1000, so the debits from the earlier sections don’t count. 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. Each request should carry a fresh high-entropy nonce, and the replay layer only needs to deduplicate on the authenticated caller (or key) plus that nonce. The method and target URI belong in the signature, which is a separate decision from whether the nonce cache is split by endpoint.

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’s API v1 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. Check that the amount is finite and positive first. 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. Keep keys longer than every retry path, and in accounting systems usually forever.
  • 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 with a fresh nonce, deduplicated on caller plus nonce.

Further reading

Ideas and technical judgement by Sheng; drafted with Claude · examples run on PostgreSQL 16.15.