Design a Reliable Wallet Transfer System with ACID Guarantees pt - 1 (Atomicity)

Atomicity - A transaction is treated as an all-or-nothing operation which means if any one of the step fails the transaction rollsback and starts from start

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

the accounts table is created successfully

• then the dummy data is added to the table
  INSERT INTO accounts (name, balance) VALUES ('Alice', 1000), ('Bob', 500);

• Now the initial balance in both accounts are Alice=1000 & Bob=500

• Now a Successful transaction is simulated
  BEGIN;

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

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

COMMIT;

• the balance in both accounts are Alice=800 & Bob=700

• Now a Unsuccessful transaction is simulated
  BEGIN;

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

//wrong col name
UPDATE accounts
SET balance = balance + 200
WHERE username = 'Bob';

COMMIT;

• The second query fails hence transaction is not made the balance remains the same (Alice=1000 & Bob=500)

In a wallet system, Atomicity ensures that money is never deducted without being credited. If any part of the transaction fails, the system restores the original state, maintaining consistency and trust