Design a Reliable Wallet Transfer System with ACID Guarantees pt - 4 (Durability)

Durability – ensures that once a transaction is successfully committed, its changes are permanently stored in the database, even in case of system failures.

• 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 durability works properly, perform a transaction and commit it

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

• After COMMIT:
  Alice’s balance becomes 800
  the change is permanently saved

• To verify durability, even if the system crashes immediately after commit:

• When the database restarts, Alice’s balance should be 800
  the committed transaction should not be lost

• If the committed data persists after restart or failure, then durability works properly

• If the system restores the database to the last committed state and does not lose committed changes, durability is maintained

• Durability is maintained through mechanisms like write-ahead logging (WAL), disk storage, and crash recovery

Durability guarantees that once a transaction is committed, its effects are permanent and survive any subsequent failures