Idempotency Situation

Understanding Idempotency through a Simple Wallet Transfer System

Introduction

In digital payment systems, the same request can sometimes be sent multiple times due to network retries, timeouts, or users clicking the payment button more than once. If the system processes the same request repeatedly, it can lead to serious issues like duplicate deductions or incorrect balances.

This is where Idempotency becomes important. Idempotency ensures that even if the same operation is executed multiple times, the result remains the same as if it were executed only once.

To understand this better, I experimented with a simple wallet transfer system using PostgreSQL and simulated duplicate transaction requests.

Setting up the tables

I started by creating two tables: one for accounts and another to store transaction records.

CREATE TABLE accounts (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL,
balance INT NOT NULL CHECK (balance >= 0),
last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE transactions (
transaction_id TEXT PRIMARY KEY,
sender_id INT,
receiver_id INT,
amount INT,
status TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Then I inserted two users:

INSERT INTO accounts (name, balance)
VALUES
('Alice', 1000),
('Bob', 500);

Checking the table:

SELECT * FROM accounts ORDER BY id;

At this point:

• Alice has 1000
• Bob has 500

Performing a normal transfer

First, I performed a transfer of 200 from Alice to Bob using a unique transaction ID.

BEGIN;

INSERT INTO transactions (transaction_id, sender_id, receiver_id, amount, status)
VALUES ('txn_001', 1, 2, 200, 'SUCCESS');

UPDATE accounts
SET balance = balance - 200
WHERE id = 1;

UPDATE accounts
SET balance = balance + 200
WHERE id = 2;

COMMIT;

After checking:

SELECT * FROM accounts ORDER BY id;

Now:

• Alice = 800
• Bob = 700

And the transaction was recorded in the transactions table.

Simulating duplicate request

Now I simulated a duplicate request by executing the same transfer again with the same transaction ID.

BEGIN;

INSERT INTO transactions (transaction_id, sender_id, receiver_id, amount, status)
VALUES ('txn_001', 1, 2, 200, 'SUCCESS');

UPDATE accounts
SET balance = balance - 200
WHERE id = 1;

UPDATE accounts
SET balance = balance + 200
WHERE id = 2;

COMMIT;

This resulted in an error because the transaction_id already exists.

Observing the result

After the error, I ran:

ROLLBACK;

Then checked the balances:

SELECT * FROM accounts ORDER BY id;

The balances remained:

• Alice = 800
• Bob = 700

The duplicate transaction was not applied.

Why this works

The transactions table uses a PRIMARY KEY on transaction_id.

This ensures that:

• each transaction is unique
• duplicate requests are rejected
• repeated execution does not change the result

Because the insert fails, the entire transaction is rolled back, preventing duplicate updates.

Improving with conflict handling

Instead of throwing an error, we can also handle duplicates gracefully using:

INSERT INTO transactions (transaction_id, sender_id, receiver_id, amount, status)
VALUES ('txn_002', 1, 2, 200, 'SUCCESS')
ON CONFLICT (transaction_id) DO NOTHING;

This prevents duplicate inserts without crashing the system.

What I learned

From this experiment, I understood that:

• Repeating the same SQL operations can cause duplicate updates
• The database does not automatically prevent duplicate business actions
• Idempotency must be implemented explicitly
• Using a transaction table with a unique transaction ID prevents duplicate processing

Conclusion

This experiment showed how duplicate requests can affect a wallet system and how idempotency helps prevent such issues.

By introducing a transaction table with a unique transaction ID, we can ensure that even if the same request is repeated, it is processed only once.

In simple terms, idempotency ensures that performing the same action multiple times does not change the result after the first successful execution.

This is a critical concept in building reliable financial systems where duplicate processing can lead to serious problems.