ASSIGNMENT 34

*Design a transaction using the given accounts table to transfer money from one user to another by debiting the sender and crediting the receiver within a single transaction block. Then introduce errors at different stages of the transaction (such as after the debit operation) and observe whether the database allows partial updates or rolls back the entire transaction. Analyze the results to verify that atomicity is maintained, ensuring that either both operations succeed or none are applied.
*

Introduction

In a digital wallet system (like PhonePe/GPay/Paytm), Atomicity ensures:

A transaction is completed fully or not executed at all.

This is critical because:

Partial updates --> money loss
Failed transfers --> inconsistent balances

Atomicity guarantees:

Either both debit and credit happen
Or nothing happens
Given Table

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
);

Initial Data

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

Initial State:

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

Step 1: Successful Transaction

Code

BEGIN;

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

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

COMMIT;

Result

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

Both operations succeed

Step 2: Introduce Failure After Debit

Code

BEGIN;

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

UPDATE accounts
SET bal = balance + 300
WHERE name = 'Bob';

COMMIT;

Observation

Second query fails
PostgreSQL automatically aborts the transaction
Entire transaction is rolled back

Final State

Alice --> ₹800 (unchanged)
Bob --> ₹700 (unchanged)

No partial update

Step 3: Manual Rollback Scenario

Code

BEGIN;

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

ROLLBACK;

Observation

Transaction is cancelled
No changes saved

Final State

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

Result:

Money disappears
System becomes unreliable
How PostgreSQL Ensures Atomicity

1. Transaction Blocks (BEGIN ... COMMIT) Groups multiple queries into one unit
2. Automatic Rollback on Failure Any error --> entire transaction is cancelled
3. Write-Ahead Logging (WAL) Tracks changes for safe recovery

Conclusion

Atomicity ensures:

Reliable money transfers
No partial transactions
Strong financial integrity