In this task, I understood the Durability property of ACID using a wallet system. Durability means that once a transaction is committed, the data will be permanently stored in the database and will not be lost even if the system crashes or restarts.
First, I created 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
);
Then I inserted sample data.
INSERT INTO accounts (name, balance)
VALUES
('Alice', 1000),
('Bob', 500);
To check the initial data:
SELECT * FROM accounts;
Alice had 1000 and Bob had 500.
Next, I performed a successful transaction to transfer 300 from Alice to Bob.
BEGIN;
UPDATE accounts
SET balance = balance - 300
WHERE id = 1;
UPDATE accounts
SET balance = balance + 300
WHERE id = 2;
COMMIT;
After committing, I checked the data again.
SELECT * FROM accounts;
Now Alice had 700 and Bob had 800.
Then I simulated a system restart by reconnecting to the database and checked the data again.
SELECT * FROM accounts;
The values remained the same, which shows that the committed transaction was permanently saved.
If a failure happens before COMMIT, the changes are not saved.
BEGIN;
UPDATE accounts
SET balance = balance - 200
WHERE id = 1;
-- crash before COMMIT
After restart, the balance remains unchanged.
If a failure happens after COMMIT, the data is already stored and will not be lost.
This happens because PostgreSQL uses Write-Ahead Logging (WAL), which ensures that committed data can be recovered even after a crash.
From this task, I understood that Durability ensures that once a transaction is completed, the data remains safe and permanent. This is very important in wallet systems to prevent loss of money or incorrect balances.
Top comments (0)