Design a Reliable Wallet Transfer System with ACID Guarantees pt - 3 (Isolation)

Isolation – ensures that multiple transactions execute independently without interfering with each other, even when running concurrently.

• 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
• To test if isolation works properly, simulate two concurrent transactions:

• Transaction 1:
  BEGIN;
  UPDATE accounts
  SET balance = balance - 500
  WHERE name = 'Alice';

• Transaction 2 (runs at the same time):
  BEGIN;
  UPDATE accounts
  SET balance = balance - 700
  WHERE name = 'Alice';

• Without isolation:
  both transactions may read the same initial balance (1000)
  both updates may proceed based on outdated data
  this can lead to incorrect final balance (race condition)

• With proper isolation:
  one transaction is executed or completed before the other affects the same data
  the second transaction will either wait or see the updated value

• inconsistent updates are prevented

• If the database prevents both transactions from interfering with each other and maintains correct results, then isolation works properly

• Isolation is maintained through mechanisms like optimistic and pessimistic locks

• Isolation guarantees that concurrent transactions do not affect each other's execution and the database remains reliable under parallel operations