CA 38 – Idempotency Situation

This task was about what happens if same transaction runs multiple times, like due to network retry or user clicking twice.

First I checked initial data:

```sql id="t9z4an"
SELECT * FROM accounts;

Alice -> 1000
Bob -> 500

Then I did a transfer (Alice → Bob 200):



```sql id="u2x9pl"
BEGIN;

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

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

COMMIT;

Now balances:

Alice -> 800
Bob -> 700

Then I ran same transaction again (same query again):

```sql id="l8k2ws"
BEGIN;

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

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

COMMIT;

Now balances became:

Alice -> 600
Bob -> 900

So same request executed twice and money got transferred twice.
Database did not stop it.

So I understood this point:
DB alone will not prevent duplicate transactions.

Then I thought how real systems solve this.

One idea is using transaction_id:



```sql id="p7n3ye"
CREATE TABLE transactions (
    id SERIAL PRIMARY KEY,
    txn_id TEXT UNIQUE,
    sender TEXT,
    receiver TEXT,
    amount INT
);

Then before inserting:

```sql id="q3m8hv"
INSERT INTO transactions (txn_id, sender, receiver, amount)
VALUES ('TXN123', 'Alice', 'Bob', 200);

If same txn_id comes again, it will fail because of UNIQUE constraint.

So same transaction will not run again.

So what I understood:

Running same query again = duplicate money transfer
Database allows it
Idempotency must be handled using unique request id or logic

So idempotency means even if same request comes multiple times, result should happen only once.