This one is about a problem that does not come from bad code or a hardware failure. It comes from the real world being messy. A user taps pay, the network stutters, the app retries, and now the same transfer request hits the database twice. Does the money move twice? In a naive system, yes it does. This problem is about understanding why that happens and how to stop it.
The Setup
Alice has 1000, Bob has 500. We want to transfer 200 from Alice to Bob.
SELECT name, balance FROM accounts;
-- Alice: 1000
-- Bob: 500
Running the Same Transfer Twice
This is what happens when a network retry fires the same request again without any duplicate protection.
First execution:
BEGIN;
UPDATE accounts
SET balance = balance - 200,
last_updated = CURRENT_TIMESTAMP
WHERE name = 'Alice';
UPDATE accounts
SET balance = balance + 200,
last_updated = CURRENT_TIMESTAMP
WHERE name = 'Bob';
COMMIT;
Check balances:
SELECT name, balance FROM accounts;
-- Alice: 800
-- Bob: 700
Second execution, same query runs again because the app retried:
BEGIN;
UPDATE accounts
SET balance = balance - 200,
last_updated = CURRENT_TIMESTAMP
WHERE name = 'Alice';
UPDATE accounts
SET balance = balance + 200,
last_updated = CURRENT_TIMESTAMP
WHERE name = 'Bob';
COMMIT;
Check balances again:
SELECT name, balance FROM accounts;
-- Alice: 600
-- Bob: 900
Alice lost 400 total. Bob gained 400 total. The transfer ran twice and PostgreSQL had no way to know the second one was a duplicate. From the database's perspective both were perfectly valid transactions. This is the problem.
Why PostgreSQL Cannot Catch This on Its Own
PostgreSQL guarantees that each transaction is atomic, consistent, isolated, and durable. But it cannot read your mind. Both transfer requests were valid SQL, both passed the CHECK constraint, both committed cleanly. Nothing about the second transaction looked wrong to the database. The duplicate problem is a business logic problem, not a database constraint problem. The database cannot solve it alone.
The Fix — Idempotency Keys
The standard solution in real payment systems is an idempotency key. Every transfer request gets a unique ID generated by the client, like a UUID. Before processing the transfer, the system checks whether a transaction with that ID has already been processed. If it has, it skips the transfer and returns the original result. If it has not, it processes it and records the ID.
First we need a table to track processed transactions:
CREATE TABLE transactions (
transaction_id UUID PRIMARY KEY,
sender TEXT NOT NULL,
receiver TEXT NOT NULL,
amount INT NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
Now the transfer logic checks this table before doing anything:
BEGIN;
-- Check if this transaction was already processed
DO $$
BEGIN
IF EXISTS (
SELECT 1 FROM transactions
WHERE transaction_id = 'a1b2c3d4-e5f6-7890-abcd-ef1234567890'
) THEN
RAISE EXCEPTION 'Duplicate transaction detected. Skipping.';
END IF;
END $$;
-- Record the transaction first
INSERT INTO transactions (transaction_id, sender, receiver, amount)
VALUES (
'a1b2c3d4-e5f6-7890-abcd-ef1234567890',
'Alice',
'Bob',
200
);
-- Now do the actual transfer
UPDATE accounts
SET balance = balance - 200,
last_updated = CURRENT_TIMESTAMP
WHERE name = 'Alice';
UPDATE accounts
SET balance = balance + 200,
last_updated = CURRENT_TIMESTAMP
WHERE name = 'Bob';
COMMIT;
First run goes through cleanly. Alice is at 800, Bob is at 700, and the transaction ID is recorded.
Second run with the exact same transaction ID hits the IF EXISTS check, finds the record, raises an exception, and the whole block rolls back. No second deduction happens.
SELECT name, balance FROM accounts;
-- Alice: 800
-- Bob: 700
The balance is exactly what it should be after one transfer. The retry had zero effect.
What Happens If the System Crashes Between the INSERT and the COMMIT
This is worth thinking about. What if we recorded the transaction ID but then crashed before the balance updates committed?
Because everything is inside a single transaction block, a crash before COMMIT rolls back everything including the INSERT into the transactions table. The transaction ID is not recorded. When the system comes back up and the retry fires, the ID check finds nothing and the transfer goes through normally.
The INSERT and the two UPDATEs either all commit together or none of them do. That is atomicity protecting the idempotency logic itself.
How Real Systems Like PhonePe and GPay Handle This
Real payment systems take this a few steps further. The idempotency key is usually generated on the client side and sent as part of the API request header. The backend stores it in a fast lookup store like Redis before even touching the database, so duplicate requests can be caught early before they reach the transaction layer. The database level check is a second safety net for cases where the Redis check is bypassed or the cache expires.
Some systems also use a status field on the transaction record. Instead of just checking for existence, they check the status. If the status is pending it means a previous attempt started but did not finish, so the system can decide whether to retry or wait. If the status is completed it returns the original result immediately without reprocessing.
What I Took Away from This
The database handles ACID guarantees beautifully but it cannot know whether two identical requests are intentional or accidental. That distinction lives in the application layer and the standard tool for handling it is an idempotency key tied to a transaction log table.
The elegant part is that because the idempotency check and the balance updates live inside the same transaction block, you never end up in a state where the key is recorded but the money did not move, or the money moved but the key was not recorded. Atomicity keeps the duplicate protection and the actual business logic perfectly in sync.
This is the kind of thing that separates a hobby project from a system you would actually trust with someone's money.
Top comments (0)