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