Idempotency Situation

Let me explain duplicate transaction First we have accounts table
Alice = 1000
Bob = 500
now we try same transfer again and again.
First transfer:

BEGIN;

UPDATE accounts
SET balance = balance - 200
WHERE name = 'Alice';

UPDATE accounts
SET balance = balance + 200
WHERE name = 'Bob';

COMMIT;

After first transfer:
Alice = 800
Bob = 700
200 is transferred successfully

lets run the transfer again

BEGIN;

UPDATE accounts
SET balance = balance - 200
WHERE name = 'Alice';

UPDATE accounts
SET balance = balance + 200
WHERE name = 'Bob';

COMMIT;

After second time:
Alice = 600
Bob = 900
same operation is applied again and money is transferred again
since, database does not know it is duplicate request it will execute again and again so duplicate transactions are allowed

the problem is same request runs multiple times money will be deducted multiple times this leads to incorrect balance

At the end of the day the database only guarantees that each transaction runs correctly. It doesn’t stop the same request from being sent twice.
So this problem has to be handled at the application level.

Typically systems solve it by assigning a unique transaction ID and checking whether that transaction has already been processed before executing it again. Because of this even if the same request is repeated it wont be applied twice. That means no double deduction and the system stays consistent. This is exactly how real payment apps prevent duplicate transactions.