CA - 29 Idempotency

Simulating Duplicate Transactions and Understanding Their Impact

In this experiment, I simulated a real-world issue where the same transaction (money transfer) is executed multiple times. This can happen in real systems due to network retries, slow responses, or duplicate requests from users. The goal was to observe how the database behaves in such situations and whether it prevents duplicate processing.

---

Initial Setup

I used the same accounts table with two users: Alice and Bob.

sql id="z7k1qp"
SELECT * FROM accounts;

Initial balances:

• Alice → 1000
• Bob → 500

---

First Transaction (Valid Transfer)

I performed a transfer of 200 from Alice to Bob.

sql id="t7x2pa"
BEGIN;

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

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

COMMIT;

After execution:

• Alice → 800
• Bob → 700

---

Repeating the Same Transaction (Duplicate Execution)

Now, I executed the exact same transaction again, simulating a duplicate request.

sql id="4z91dp"
BEGIN;

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

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

COMMIT;

After running this again:

• Alice → 600
• Bob → 900

---

Executing Again (Multiple Duplicates)

Running it one more time:

sql id="j9m8vk"
BEGIN;

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

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

COMMIT;

Final balances:

• Alice → 400
• Bob → 1100

---

Observation

From this experiment, it is clear that the database does not prevent duplicate transaction execution. Each time the same query is executed, it is treated as a new transaction, and the balances are updated again.

This leads to:

• Multiple deductions from the sender
• Multiple credits to the receiver
• Incorrect and inconsistent account balances

This is dangerous in real-world systems, especially in financial applications, where even a single duplicate transaction can cause serious issues like money loss or incorrect balances.

---

Real-World Solution Approaches

To prevent such problems, real-world systems implement additional safeguards:

1. Idempotency Keys

Each transaction is assigned a unique ID.
If the same request is received again, the system checks the ID and ignores duplicates.

1. Transaction Logging

Systems maintain logs of all processed transactions to avoid reprocessing.

---

Conclusion

Through this simulation, I understood that databases like PostgreSQL will execute every valid query they receive, without automatically checking for duplicates. This highlights the importance of designing systems that handle repeated requests carefully.