Learn · SQL · Core
SQL Transactions
A transaction groups several changes so they all succeed or all fail — never half. It’s the reason money doesn’t vanish between two accounts. This is your world.
Before we start
- What a transaction is: all-or-nothing groups of statements
- ACID in plain words
- COMMIT vs ROLLBACK
- The money race and how locking prevents it
- Explain why a bank transfer must be a transaction
- Say what ACID means with an example
- Describe how two transactions can corrupt a balance
Why you’re learning it: anything touching money or critical state needs transactions — directly relevant to your EMV/payments work and the RupeeRail ledger. A favourite deep-dive topic. ⏱️ ~30 min.
The idea — a bank transfer
Move ₹500 from A to B. That’s two steps: subtract from A, then add to B. If the system crashes between them, ₹500 vanished. A transaction wraps both into one indivisible unit — both, or neither. Watch it:
Start the transaction — nothing is saved yet.
A goes 1000 → 500 (held, not committed).
B goes 200 → 700 (held, not committed).
Both saved together. If anything failed → ROLLBACK, neither happened.
BEGIN; UPDATE accounts SET balance = balance - 500 WHERE id = 'A'; UPDATE accounts SET balance = balance + 500 WHERE id = 'B'; COMMIT; -- both saved together, or ROLLBACK undoes both
ACID — in plain words
| Letter | Means |
|---|---|
| Atomicity | All steps happen, or none (the transfer above) |
| Consistency | The DB moves from one valid state to another — rules never break |
| Isolation | Concurrent transactions don’t step on each other |
| Durability | Once committed, it survives a crash / power loss |
The money race — why isolation matters
Two withdrawals hit the same ₹1000 account at the same instant, with no locking:
- reads balance = 1000
- computes 1000 − 800 = 200
- writes 200
- reads balance = 1000 (stale!)
- computes 1000 − 800 = 200
- writes 200
Bug: two ₹800 withdrawals happened on a ₹1000 account, and the balance says ₹200. ₹600 vanished.
The fix — locking: SELECT ... FOR UPDATE makes Thread B wait until A commits, so B reads the fresh ₹200 and correctly rejects the second withdrawal. This exact race is a top payments interview question.
Where you’ll use it — real life
Payments, refunds, ledger postings — all must be atomic. Your EMV world lives on this.
“Create order + reduce stock + charge card” commit together or roll back.
Row locking stops two requests double-charging one payment — the RupeeRail problem.
Durability means a committed payment survives the server dying a second later.
Now YOU do the reps
Out loud: “What does ACID mean, and walk me through why a bank transfer must be a transaction — and the race a lock prevents.” Then log it in your Journal.
Next: OS Fundamentals →