Today I tried to understand what happens when the same transaction is executed multiple times in a database. This can happen in real applications like PhonePe or GPay when there are network issues and the same request is sent again.
I used the same accounts table where Alice has 1000 and Bob has 500. First, I performed a normal transfer of 200 from Alice to Bob.
BEGIN;
UPDATE accounts
SET balance = balance - 200
WHERE name = 'Alice';
UPDATE accounts
SET balance = balance + 200
WHERE name = 'Bob';
COMMIT;
After this, Alice had 800 and Bob had 700. Everything was correct.
Then I repeated the same transaction again, as if the same request was sent twice.
BEGIN;
UPDATE accounts
SET balance = balance - 200
WHERE name = 'Alice';
UPDATE accounts
SET balance = balance + 200
WHERE name = 'Bob';
COMMIT;
Now the balances became Alice 600 and Bob 900. This means the same transaction was applied again. The database did not stop it.
From this, I understood that PostgreSQL does not automatically prevent duplicate transactions. It will execute whatever queries we send, even if they are repeated.
This can be dangerous in real systems because users may lose money or get incorrect balances if the same request is processed multiple times.
To handle this, real world systems use additional logic. One common approach is using a unique transaction id. Every transaction is given a unique id, and the system checks if that id is already processed. If it is already present, the system ignores the duplicate request.
Another approach is to maintain a transaction history table and check before processing any new request. Some systems also use locking or idempotent operations to avoid repeating the same effect.
I understood that while the database ensures safety using ACID properties, it does not automatically handle duplicate requests. That responsibility is handled by the application design.
Overall, this helped me understand the importance of handling repeated transactions carefully in real world financial systems.
Top comments (0)