CA 28 - Durability

Simulating a Digital Wallet System and Understanding Transaction Durability

As part of my database learning, I worked on simulating a simple digital wallet system, similar to applications like PhonePe or GPay. The goal was to understand how transactions are handled in a database and how systems ensure that operations like money transfers remain consistent and reliable, even in case of failures.

---

Creating the Accounts Table

To begin with, I created an accounts table to store user details such as ID, name, balance, and last updated timestamp. I also added a constraint to ensure that the balance never becomes negative.
sql
CREATE TABLE accounts (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL,
balance INT NOT NULL CHECK (balance >= 0),
last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

---

Inserting Sample Data

Next, I inserted some dummy data to simulate users in the system.

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

To verify:

SELECT * FROM accounts;

At this stage:

• Alice → 1000
• Bob → 500

---

Performing a Money Transfer

To simulate a transaction, I transferred 200 from Alice to Bob. I used transaction control commands to ensure both operations happen together.

BEGIN;

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

UPDATE accounts
SET balance = balance + 200
WHERE name = 'Bob';

COMMIT;

---

Verifying the Transaction

SELECT * FROM accounts;

After execution:

• Alice → 800
• Bob → 700

This confirms that the transaction was successfully committed.

---

Simulating a System Restart

To test durability, I simulated a system restart by disconnecting and reconnecting to the database (or restarting PostgreSQL).

After reconnecting:

SELECT * FROM accounts;

The balances remained:

• Alice → 800
• Bob → 700

This shows that the committed transaction persists even after a restart.

---

Understanding Durability

This behavior demonstrates Durability, one of the ACID properties. Once a transaction is committed, it is permanently stored in the database and cannot be lost, even in case of system failure.

PostgreSQL ensures this using mechanisms like Write-Ahead Logging (WAL), where changes are first recorded in logs before being written to the actual database.

---

Crash Before COMMIT

BEGIN;

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

-- Crash happens here (no COMMIT)

Result: Changes are not saved

---

BEGIN;

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

COMMIT;

-- Crash happens here

---

Testing Atomicity using ROLLBACK

I also tested what happens when a transaction is cancelled.

BEGIN;

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

ROLLBACK;

After running:

SELECT * FROM accounts;

Alice’s balance remains unchanged.

This demonstrates Atomicity, meaning either all operations in a transaction are executed or none at all.

---

Through this experiment, I understood how databases handle real-world financial operations securely. By using transactions (BEGIN, COMMIT, ROLLBACK), I was able to ensure consistency and reliability in money transfers.

This hands-on simulation helped me clearly understand ACID properties, especially Durability and Atomicity, and how they are essential in building systems like digital wallets where even a small inconsistency can lead to serious issues like incorrect balances or data loss.