Idempotency Situation

Idempotency Situation is a when Even if the same request comes multiple times,it should behave like it happened only once this often happens in payment transactions due to network issues can be cause loss to customer if it is not handled properly.

This can be solves by keeping track of transactions and every bank transaction has a transaction ID and even if retry is given the trasaction takes place only once

The trasaction table keep a record all the amount that is transferred from a user to another user

CREATE TABLE transactions (
    id SERIAL PRIMARY KEY,
    sender TEXT,
    receiver TEXT,
    amount INT,
    idempotency_key TEXT UNIQUE
);

how it works

BEGIN;

INSERT INTO transactions (sender, receiver, amount, idempotency_key)
VALUES ('Alice', 'Bob', 200, 'txn_101');

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

COMMIT;

The data is entered before every transaction and then the query is updated

now, when the same transaction with the same idempotency_key is there for another query , an error is prompted because duplicate key values violates the unique constraint of the attribute

INSERT INTO transactions (sender, receiver, amount, idempotency_key)
VALUES ('Alice', 'Bob', 200, 'txn_101');