CA 38 - Idempotency Situation

CREATE TABLE accounts (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    balance INT
);

INSERT INTO accounts VALUES
(1, 'Alice', 5000),
(2, 'Bob', 3000);

Transfer

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

I ran the same transfer twice.

Result
Alice → 3000
Bob → 5000

Problem

Money can be deducted multiple times
Data becomes wrong

Simple Fix

Use a transaction ID:

CREATE TABLE transactions (
    txn_id VARCHAR(50) PRIMARY KEY
);
INSERT INTO transactions VALUES ('TXN1');