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

Source: Dev.to

Durability

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

Schema

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

Sample Data

INSERT INTO accounts (name, balance)
VALUES ('Alice', 1000), ('Bob', 500);

Initial balances: Alice = 1000, Bob = 500.

Transaction Test

BEGIN;

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

COMMIT;

After the COMMIT, Alice’s balance becomes 800 and the change is permanently saved.

Verifying Durability

1. Simulate a crash immediately after the commit.
2. Restart the database.
3. Query Alice’s balance; it should still be 800.

If the committed data persists after the restart, durability is confirmed.

How Durability Is Achieved

• Write‑Ahead Logging (WAL) records changes before they are applied to the data files.
• Disk storage ensures that logs and data pages are flushed to stable media.
• Crash recovery processes the WAL on startup to redo any committed transactions that were not yet reflected on disk.

These mechanisms guarantee that once a transaction is committed, its effects survive any subsequent failures.