This exercise was about understanding Atomicity in databases using a wallet transfer system like PhonePe or GPay. The main idea is when money is transferred from one account to another, both debit and credit must happen together. If one fails, everything should rollback. That is what Atomicity means either everything happens or nothing happens.
First we created the accounts table and inserted dummy data like Alice and Bob with balances.
Before doing any transfer, I checked the balances.
SELECT * FROM accounts;
Suppose Alice sends 200 to Bob. We must do this inside a transaction block.
Correct Transaction (Atomic Transfer):
BEGIN;
UPDATE accounts
SET balance = balance - 200
WHERE name = 'Alice';
UPDATE accounts
SET balance = balance + 200
WHERE name = 'Bob';
COMMIT;
Here what happens is first Alice balance reduces, then Bob balance increases, and finally COMMIT saves the changes permanently.
If we check balances after commit:
Alice = 800
Bob = 700
So transfer successful.
Then we tested Atomicity by introducing an error after the debit step.
Transaction With Error (Testing Atomicity):
BEGIN;
UPDATE accounts
SET balance = balance - 200
WHERE name = 'Alice';
-- Intentional error
UPDATE accounts
SET balance = balance + 200
WHERE name = 'NonExistingUser';
COMMIT;
This will cause an error because the user does not exist or the query fails. When error happens, PostgreSQL will not complete the transaction.
Then we run:
ROLLBACK;
After rollback, if we check balances again:
SELECT * FROM accounts;
We will see:
Alice = 1000
Bob =500
So even though we deducted money from Alice in the first query, because the second query failed, the whole transaction was cancelled. This proves Atomicity – partial update did not happen.
Then we tested another failure case where we forced an error after debit.
BEGIN;
UPDATE accounts
SET balance = balance - 300
WHERE name = 'Alice';
Force error
SELECT 1/0;
UPDATE accounts
SET balance = balance + 300
WHERE name = 'Bob';
COMMIT;
This gives divide by zero error. Because of that error, the transaction fails and we rollback.
Again checking balances shows no change. So money was not lost from Alice even though debit query ran earlier. That means database protected the data using Atomicity.
So finally what I understood from this exercise is, transactions are very important in banking or wallet systems. Without transactions, money may get deducted but not added to the other person, which is very dangerous. Atomicity ensures that both debit and credit happen together, otherwise nothing happens.
So the main commands used in this exercise are:
BEGIN – start transaction
UPDATE – modify balance
COMMIT – save changes
ROLLBACK – undo changes
This experiment clearly showed that PostgreSQL follows ACID properties and prevents partial updates in money transfer systems.
Top comments (0)