ASSIGNMENT 37

Design a digital wallet system (like PhonePe/GPay/Paytm) using the given accounts table and sample data. Perform a money transfer between two users within a transaction and commit the changes. Then simulate a system crash or restart and retrieve the account balances to verify whether the committed transaction persists. Based on your observation, explain how the database ensures durability, and analyze what happens if a failure occurs just before or just after the COMMIT

Introduction

In applications like PhonePe / GPay / Paytm, even a tiny inconsistency can cause:

Money loss
Duplicate transactions
Incorrect balances

To prevent this, databases follow ACID properties, where Durability ensures:

Once a transaction is committed, it is permanently stored — even if the system crashes.

Given Table Structure

Code

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

Explanation

balance >= 0 prevents negative money
last_updated tracks latest changes
Ensures basic integrity

Step 1: Insert Dummy Data

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

Initial State:

Alice --> ₹1000
Bob --> ₹500

Step 2: Perform a Transaction (Transfer Money)

Code

BEGIN;

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

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

COMMIT;

Result After Commit
Alice --> ₹800
Bob --> ₹700

Transaction successful and committed

Step 3: Simulate System Crash / Restart

Now imagine:

Database crashes
Server restarts

Observation After Restart

Output remains:

Alice --> ₹800
Bob --> ₹700

Changes are still محفوظ (persisted)

Why Did This Work? (Durability Explained)

Durability is guaranteed by the database using:

1. Write-Ahead Logging (WAL)

Before modifying data, changes are written to a log file
Even if crash happens → database can recover using logs

Think of it like:

“Write it in a notebook before actually doing it”

1. Commit = Permanent Record

Once COMMIT is executed:
Changes are flushed to disk
Marked as permanent

What Happens During Failures?

Case 1: Crash BEFORE COMMIT

BEGIN;

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

Result after restart:

Transaction is rolled back

Alice still --> ₹1000

No partial updates (Atomicity + Durability)

Case 2: Crash AFTER COMMIT

COMMIT;

Result after restart:

Changes are preserved

Alice --> ₹800

Durability ensures no data loss

What If Durability Didn’t Exist?

Without durability:

Transactions may disappear after crash
Users could:
Lose money

Real-World Implementation (Wallet Apps)

Apps like PhonePe / GPay / Paytm ensure durability by:

Using robust databases (PostgreSQL, MySQL)
Maintaining replicated logs across servers
Performing frequent backups
Using distributed transaction systems

Final Conclusion

Key Takeaways

COMMIT = guarantee of persistence
Database logs ensure recovery after crash
Durability prevents data loss in financial systems