Today I worked on a small database problem where I tried to understand how money transfer works in applications like PhonePe or GPay. The main idea was to see how databases handle transactions safely using ACID properties.
First, I created a simple accounts table with id, name, and balance. I inserted two users, Alice with balance 1000 and Bob with balance 500. This helped me simulate a real scenario of transferring money between users.
Before starting the transaction, I checked the data. Alice had 1000 and Bob had 500.
Then I wrote a transaction to transfer 200 from Alice to Bob.
BEGIN;
UPDATE accounts
SET balance = balance - 200
WHERE name = 'Alice';
UPDATE accounts
SET balance = balance + 200
WHERE name = 'Bob';
COMMIT;
After running this, I checked the table again. Alice’s balance became 800 and Bob’s balance became 700. This showed a successful transaction.
Next, I wanted to see what happens when something goes wrong. So I introduced an error after deducting money from Alice.
BEGIN;
UPDATE accounts
SET balance = balance - 200
WHERE name = 'Alice';
SELECT * FROM wrong_table;
UPDATE accounts
SET balance = balance + 200
WHERE name = 'Bob';
COMMIT;
Here, the second step caused an error because the table does not exist. Because of this, the transaction failed.
After that, I checked the balances again. I expected Alice’s balance to be reduced, but surprisingly it was still 1000 and Bob was still 500. This means the database did not save the partial change.
This clearly showed the concept of atomicity. Even though one update was executed, since the full transaction did not complete, everything was rolled back.
If the system allowed partial updates, it could lead to money loss or incorrect balances.
Top comments (0)