💾 Database Transactions and Concurrency
💡 What is a Transaction?
A transaction is a set of operations that perform a single logical task in a database.
🔍 Real-World Example: Banking Transfer
Imagine you're using a banking app to transfer ₹1000 from Account A to Account B.
This task involves:
1. BEGIN TRANSACTION
2. Deduct ₹1000 from Account A
3. Add ₹1000 to Account B
4. COMMIT TRANSACTION
This is one transaction. If any step fails, the whole operation should fail.
Banking transactions require all operations to succeed or fail together
🧱 What is an Operation in a Transaction?
In a transaction, an operation is a single step like reading, writing, updating, or committing data in the database.
Together, multiple operations complete the logical task.
✅ Common Types of Operations
- BEGIN TRANSACTION – Start the transaction
- READ(X) – Read value of X
- WRITE(X) – Update value of X
- COMMIT – Save changes
- ABORT – Cancel and rollback
📘 Example: Transfer ₹1000 from A to B (Step-by-step)
Transaction T1:
1. BEGIN TRANSACTION
2. READ(A) -- Get balance of Account A
3. A = A - 1000 -- Deduct ₹1000
4. WRITE(A) -- Save new balance of A
5. READ(B) -- Get balance of Account B
6. B = B + 1000 -- Add ₹1000
7. WRITE(B) -- Save new balance of B
8. COMMIT -- Finalize the transaction
Each READ and WRITE is an operation. Together, they make one complete transaction.
✅ ACID Properties (Very Important for Interviews)
ACID properties are a set of rules that ensure database transactions are processed reliably, even in case of failures or concurrent access.
🔄 Atomicity
All operations in a transaction are treated as a single unit – either all succeed or none at all.
💡 Real-World Example:
When transferring ₹1000 from Account A to B, if deduction from A fails, the amount must not be added to B.
BEGIN TRANSACTION
Deduct ₹1000 from A
Add ₹1000 to B
COMMIT
-- If any step fails, rollback both
⚖️ Consistency
The database must always remain in a valid state before and after the transaction.
💡 Real-World Example:
Total money in the system should be the same before and after a transfer.
Before Transaction:
A = ₹5000, B = ₹3000 → Total = ₹8000
After Transaction (₹1000 A to B):
A = ₹4000, B = ₹4000 → Total = ₹8000 ✅
🔒 Isolation
Each transaction runs independently. Partial results of one transaction are not visible to others.
💡 Real-World Example:
Two users transferring money simultaneously won’t interfere with each other’s balances.
User 1: Transfer ₹500 A → B
User 2: Transfer ₹1000 C → D
-- Isolation ensures these run safely in parallel without data conflict
💾 Durability
Once a transaction is committed, the changes are permanent – even if the system crashes.
💡 Real-World Example:
If a user completes a payment and the app crashes, the payment still goes through.
COMMIT
-- Data saved in log or backup
-- After power failure: data is restored from logs ✅
ACID properties ensure database reliability, correctness, and integrity during all transactions.
🤝 Concurrent Execution
When multiple transactions run at the same time, it's called concurrent execution.
🎬 Movie Ticket Booking Example
Imagine many users are booking movie tickets online at the same time. The system processes many transactions in parallel, but must ensure that no one gets the same seat.
Transaction 1: User A books Seat 5A Transaction 2: User B books Seat 7C Transaction 3: User C books Seat 5A ← Should be blocked!
Multiple users booking tickets simultaneously - perfect example of concurrency
🔄 Transaction States
In a DBMS, a transaction moves through several states to ensure safe and correct execution. The diagram below shows the complete life cycle:
Visual flow of transaction states including commit, abort, and rollback.
🧭 Explanation of Each State
| State | Meaning | Transition | Example |
|---|---|---|---|
| Active | Transaction is currently executing (doing read/write) | → Partially Committed (if successful) or → Failed (if error) | User clicks "Recharge ₹100" |
| Partially Committed | Final step executed; about to save changes | → Committed (if successful) or → Failed (if error) | Recharge sent to operator, waiting for response |
| Committed | All changes made permanent in the database | → Terminated | "Recharge Successful" message shown |
| Failed | Error occurred during transaction | → Aborted | Network failure or recharge error |
| Aborted | Transaction rolled back (undone) | → Terminated | Recharge failed and money returned |
| Terminated | Transaction completed successfully or aborted safely | End of transaction | Recharge is either complete or failed, system ready for new transaction |
📘 Real-World Example in <pre> format
Recharge Transaction Flow
1. BEGIN TRANSACTION
2. READ(wallet_balance)
3. wallet_balance = wallet_balance - ₹100
4. WRITE(wallet_balance)
5. Send recharge request
If success:
6. COMMIT → Move to COMMITTED → TERMINATED
If failure:
6. FAIL → Move to FAILED → ROLLBACK → ABORTED → TERMINATED
📅 What is a Schedule?
A Schedule is the order in which operations (like read, write) from multiple transactions are executed.
Types of Schedules:
🔄 Serial Schedule
T1: read(A) → write(A) → commit T2: read(B) → write(B) → commit
One transaction at a time
🔀 Concurrent Schedule (parallel schedules)
T1: read(A) T2: read(B) T1: write(A) T2: write(B)
Multiple transactions interleaved
Database scheduling manages the order of transaction execution
⚠️ Problems in Concurrency
Jab multiple transactions ek hi samay par execute karte hain bina proper control ke, to data galat ho sakta hai. Yeh problem ko kehte hain: Concurrency Issues.
💥 Dirty Read (Uncommitted Read)
Definition: Jab ek transaction (T2) kisi doosre transaction (T1) ke uncommitted data ko read kar leta hai, to agar T1 fail ho jaye, T2 galat data ke basis pe kaam kar chuka hota hai.
🛍️ Real-World Example: Aapka friend T1 aapke account se ₹500 nikalta hai, aur aap T2 me balance check karke ₹1500 dekh lete ho. Par T1 fail ho jata hai aur rollback ho jata hai. Aapne jo balance dekha wo galat tha.
Image: Timeline of Dirty Read Problem between T1 and T2
🧠 Step-by-Step Explanation:
- t1–t3 (T1): T1 reads A, deducts ₹500, and writes new value. But it has not committed yet.
- t4–t5 (T2): T2 reads A’s new (uncommitted) value and adds ₹100. This is the dirty read.
- t6 (T1): T1 fails due to some issue.
- t7 (T1): T1 rolls back. That means its update is cancelled.
- t7 (T2): But T2 already used that uncommitted value and wrote it. Now the database is inconsistent.
📝 Lost Update
Definition: Jab do transactions same data ko read karte hain aur update karte hain bina synchronization ke, to ek transaction ka update dusre ke update se chhup jata hai. Isse kehte hain Lost Update.
🛍️ Real-World Example: Alice aur Bob ek product ki quantity update kar rahe hain. Dono ne same original value (7) read ki. Par Bob ne jab last me commit kiya, to Alice ka update (quantity = 6) chhup gaya aur final value ban gayi quantity = 10.
Image: Lost Update between Alice and Bob
🧠 Step-by-Step Explanation:
- Step 1: Alice transaction start karti hai aur product ki quantity read karti hai (value: 7).
- Step 2: Bob bhi transaction start karta hai aur same quantity read karta hai (value: 7).
- Step 3: Alice quantity ko 6 set karta hai aur commit karta hai.
- Step 4: Bob quantity ko 10 set karta hai (based on old value 7) aur commit karta hai.
- Result: Alice ka update (6) overwrite ho gaya. Final quantity = 10.
🔄 Read-Write Conflict (Unrepeatable Write)
Definition: Jab ek transaction data ko read karke uspar operation karta hai, aur beech me doosra transaction usi data ko overwrite kar deta hai, to pehle transaction ka update dusre ko ignore kar deta hai.
🛍️ Real-World Example: Do log ek hi account balance X par kaam kar rahe hain. A ne X ko +15 kiya, par write nahi kiya tha. B ne X ko read kiya, -25 kiya, aur write bhi kar diya. Jab A ne apna write kiya, to B ka change chhup gaya.
Image: Read-Write Conflict – Final value of X becomes 115, B’s update is lost.
🧠 Step-by-Step Explanation:
- Step 1: Transaction A reads X = 100 and prepares to update X = 115
- Step 2: Before A writes, Transaction B reads X = 100, subtracts 25 → X = 75, and writes it
- Step 3: Transaction A now writes its old planned value: X = 115
- Final Result: B’s change (X = 75) is overwritten — data conflict occurs
🔁 Recoverable vs Irrecoverable Schedule
Jab multiple transactions ek hi data ke sath kaam karte hain, to hume dekhna padta hai ki koi transaction galat data pe commit to nahi kar raha. Iske liye schedule ka type define kiya jata hai.
✅ Recoverable Schedule
Definition: Ek transaction T2 tabhi commit kare jab usne jo data read kiya hai, wo T1 pehle se commit kar chuka ho.
🛍️ Real-World Example: T1 updates salary and commits, fir T2 reads that salary and commits. Agar T1 fail hota to T2 ne kuch commit nahi kiya hota — so safe.
- T1: WRITE salary = ₹80,000 → COMMIT
- T2: READ salary → COMMIT
❌ Irrecoverable Schedule
Definition: Ek transaction T2 read kar leta hai uncommitted data (dirty read), aur commit bhi kar deta hai. Agar T1 fail hua to rollback impossible ho jata hai.
🛍️ Real-World Example: Loan approve hua salary ₹80,000 ke basis pe, par later pata chala T1 ne wo salary rollback ki thi.
- T1: WRITE salary = ₹80,000
- T2: READ salary → COMMIT
- T1: ROLLBACK
🔄 Cascading vs Cascadeless Schedule
When multiple transactions interact with shared data, their commit order and read/write timing decides if the system can recover properly or not.
✅ Cascadeless Schedule
Definition: Ek transaction tabhi kisi aur ka data read karta hai jab wo pehle commit ho chuka ho.
📌 Advantage: No cascading rollback, safe, and consistent.
All transactions (T2, T3) only read after T1 commits — No cascading rollback possible.
- T1: Reads/Writes A → Commits
- T2: Reads A (after T1 commits), Writes A → Commits
- T3: Reads A (after T2 commits), Writes A → Commits
⚠️ Cascading Schedule (Recoverable)
Definition: Jab ek transaction dusre ka uncommitted data read karta hai, to agar pehla transaction fail ho gaya, to baaki sab rollback karne padte hain.
📌 Problem: Chain of rollbacks = cascading abort
T2, T3, T4 all read A before T1 commits — If T1 fails, they must rollback.
- T1: Reads/Writes A, but fails
- T2: Reads A (before T1 commits), Writes A → Must rollback
- T3: Reads A (before T2 commits), Writes A → Must rollback
- T4: Reads A (before T3 commits), Writes A → Must rollback
🔐 Deadlock in Concurrency
A deadlock occurs when two or more transactions wait indefinitely for each other to release resources.
🔄 Deadlock Example
Transaction 1: - Locks Account A - Waits for Account B Transaction 2: - Locks Account B - Waits for Account A Result: Both transactions wait forever!
🧠 Real-World Summary
💳 UPI Payment
Perfect example of a Transaction with ACID properties
🔄 All-or-Nothing
Demonstrates Atomicity - complete success or complete failure
🎬 Movie Booking
Shows Isolation - two users can't book same seat
💾 Crash Recovery
Illustrates Durability - refund processed even after crash
👁️ Wrong Balance
Example of Dirty Read - seeing incomplete transaction
📝 Profile Update
Shows Lost Update - two edits overwrite each other
🎯 Interview Tips
- Always explain ACID with real examples - banking transfers, e-commerce orders
- Know the difference between serial and concurrent schedules
- Understand common concurrency problems - dirty read, lost update, phantom read
- Explain deadlock with practical scenarios - two users trying to transfer money
- Practice drawing transaction state diagrams
🔗 Quick Reference
| Concept | Key Point | Real-World Example |
|---|---|---|
| Transaction | Logical unit of work | Bank transfer, online purchase |
| Atomicity | All or nothing | Payment succeeds completely or fails completely |
| Consistency | Database remains valid | Account balances always correct |
| Isolation | Transactions don't interfere | Multiple users can't book same seat |
| Durability | Changes persist | Transaction survives system crash |
| Concurrency | Multiple transactions simultaneously | Many users using ATM at same time |
| Deadlock | Circular wait condition | Two users waiting for each other's resources |