Why Database Transactions Matter - A Real Life Example

It’s the last few days of Ramadan. Your younger brother wants to go shopping for Eid but doesn’t have cash. So, he calls you and says:

"Bhai, send me 5000 BDT to my bKash account. I need to hit the market tomorrow!"

Being the responsible elder sibling, you open your bKash app, enter the amount, and hit Send Money. But just as you press the button, your internet connection drops!

You think, "No worries, I’ll retry when the network is back." But suddenly, your brother calls again:

"Bhai, I haven’t received the money yet, but I think it got deducted from your account!"

Now, your head is spinning! Where did the money go? It was deducted from your account but never reached your brother’s.

Now, imagine if banking and payment systems had such issues regularly. What would happen? People would lose money, businesses would collapse, and trust in the system would be destroyed.

And this is exactly where many new developers make critical mistakes—they assume that once a transaction starts, it will complete successfully. But in real-world systems, anything can go wrong:

✅ The network connection may drop.
✅ The server may crash.
✅ A hidden bug in the code may interrupt execution.
✅ Or, if a two-step process fails halfway, only one side of the transaction gets executed while the other doesn’t.

Let’s look at some common mistakes developers make when handling database transactions.
❌ Executing multiple SQL statements separately:
Many junior developers write queries like this:

UPDATE accounts SET balance = balance - 5000 WHERE id = 1;  
UPDATE accounts SET balance = balance + 5000 WHERE id = 2;

If something goes wrong in between (server crash, network issue), the first statement might execute while the second one doesn’t. Result? Money is deducted from one account but never credited to the other.

❌ Not handling rollback properly:
Developers often assume that once a query executes, it’s final. But if something fails, the transaction should roll back to prevent inconsistencies. If there’s no rollback mechanism, users could lose money permanently.

How to Handle Transactions Correctly with ACID Principles

To build reliable and scalable systems, we must follow the ACID properties of transactions:

🔹 Atomicity: Either the entire transaction succeeds, or nothing happens at all. If money is deducted from one account, it must be credited to the other. If anything fails, both actions must be undone.

🔹 Consistency: The database must always remain in a valid state. If an error occurs, everything should return to the previous correct state—there should never be a mismatch in financial records.

🔹 Isolation: Even if multiple transactions are happening at the same time, they should not interfere with each other. For example, if two people try to withdraw from the same account at the same time, they shouldn’t overwrite each other’s updates.

🔹 Durability: Once a transaction is committed, it should remain permanent, even if the server crashes.

The Right Way to Handle Transactions

In production systems, we must always use transactions to ensure data integrity

BEGIN TRANSACTION;  

UPDATE accounts SET balance = balance - 5000 WHERE id = 1;  
UPDATE accounts SET balance = balance + 5000 WHERE id = 2;  

COMMIT;

And if something goes wrong, we ROLLBACK to maintain consistency:

ROLLBACK;

Why Does This Matter?

If a banking or payment system doesn’t handle transactions properly, it can lead to financial loss, operational failures, and loss of user trust.

In my career, designing scalable and high-availability systems, I’ve always ensured that transactions are ACID-compliant. A single mistake here isn’t just a coding bug—it can impact thousands of users, their money, and critical business operations.