Database Management Systems
Unit 6: Transaction Processing
ACID Properties, Concurrency Control, Locking Protocols, Deadlocks, Isolation Levels & Recovery — how databases guarantee data correctness when everything goes wrong.
🏢 Oracle & PostgreSQL | 📝 15 MCQs (Bloom's) | 🔬 5 Lab Exercises | 💼 Interview Prep
Why This Chapter Pays Your Salary
Every ₹1 that moves through India's banking system — UPI transfers, ATM withdrawals, online payments — relies on transactions. A transaction is a promise: either ALL operations succeed, or NONE do. Without this guarantee, ₹10,000 could vanish from your account without reaching the recipient. Transaction processing is the foundation of database reliability, and it's the most tested DBMS topic in GATE CS (5-8 marks every year).
🏢 Industry Snapshot
PhonePe/Google Pay — Every UPI transaction is an ACID transaction: (1) Debit sender's bank, (2) Credit receiver's bank, (3) Log to NPCI, (4) Send confirmation. If step 3 fails, steps 1 and 2 are automatically rolled back. PhonePe processes 600 crore transactions/month — every one of them ACID-compliant.
SBI Core Banking — When 50,000 ATMs simultaneously process withdrawals, the database uses Two-Phase Locking (2PL) to ensure no two transactions corrupt the same account balance. If two ATMs try to debit the same account simultaneously, one is blocked until the other commits.
Razorpay — Payment gateway uses SERIALIZABLE isolation for critical payment reconciliation. Lower isolation levels (READ COMMITTED) for dashboard queries — a deliberate performance-vs-correctness trade-off.
Learning Outcomes — Bloom's Taxonomy
| Bloom's Level | Outcome Statement |
|---|---|
| L1 — Remember | Define ACID properties; list transaction states; recall lock types (Shared/Exclusive); name the four SQL isolation levels |
| L2 — Understand | Explain how dirty reads, non-repeatable reads, and phantom reads occur; describe why 2PL guarantees serializability; explain WAL (Write-Ahead Logging) for recovery |
| L3 — Apply | Write SQL transactions with COMMIT, ROLLBACK, SAVEPOINT; construct precedence graphs and test for conflict serializability; apply 2PL to a schedule |
| L4 — Analyze | Analyze a concurrent schedule for conflicts; detect deadlocks using wait-for graphs; determine the isolation level that prevents a given anomaly |
| L5 — Evaluate | Evaluate trade-offs between isolation levels (performance vs correctness); justify when to use optimistic vs pessimistic concurrency control |
| L6 — Create | Design a transaction processing strategy for a payment system with proper locking, isolation levels, error recovery, and deadlock handling |