Concept Notes (Deep Explanation + Examples)
1️⃣ Introduction: Why Transaction Control Matters in DBMS
In real-world databases (banks, exam portals, e-commerce sites), data safety is critical.
Imagine:
- Student paying ECET exam fee 💳
- Bank deducts money ❌ but registration not updated ❌
To avoid such issues, DBMS uses transactions with control commands:
- COMMIT
- ROLLBACK
- SAVEPOINT
📌 These are called Transaction Control Language (TCL) commands in SQL.
2️⃣ What is a Transaction? (From Basics)
A transaction is a group of SQL operations executed as one logical unit.
Example:
UPDATE accounts SET balance = balance - 1000 WHERE acc_no = 101;
UPDATE accounts SET balance = balance + 1000 WHERE acc_no = 202;👉 Both statements together form one transaction.
If one fails → whole transaction should fail.
This follows ACID properties:
- Atomicity
- Consistency
- Isolation
- Durability
3️⃣ COMMIT Command (Permanent Save)
🔹 Meaning:
COMMIT permanently saves all changes made during the transaction.
Once committed:
- Changes are written to disk
- Cannot be undone
🔹 Syntax:
COMMIT;🔹 Real-world Example:
💡 Online exam submission
- Student submits answers
- Data stored permanently
- Even if system crashes → data remains
🔹 SQL Example:
UPDATE student SET marks = 85 WHERE roll_no = 10;
COMMIT;📌 After COMMIT, ROLLBACK will NOT work.
4️⃣ ROLLBACK Command (Undo Changes)
🔹 Meaning:
ROLLBACK undoes all changes made after the last COMMIT.
Used when:
- Error occurs
- User cancels operation
- System failure
🔹 Syntax:
ROLLBACK;🔹 Real-world Example:
💡 ATM Transaction
- Money deducted
- Cash not received
- Transaction rolled back
🔹 SQL Example:
SAVEPOINT sp1;
ROLLBACK TO sp1;👉 Deleted row comes back!
📌 Works only before COMMIT.
5️⃣ SAVEPOINT Command (Partial Rollback)
🔹 Meaning:
SAVEPOINT creates a checkpoint inside a transaction.
You can rollback up to a savepoint, not the entire transaction.
🔹 Syntax:
SAVEPOINT sp1; ROLLBACK TO sp1;
🔹 Real-world Example:
💡 Editing student record
- Update name ✔
- Update marks ❌ wrong
- Rollback only marks update
🔹 SQL Example:
UPDATE student SET name = 'Ravi' WHERE roll_no = 15;
SAVEPOINT s1;
UPDATE student SET marks = 120 WHERE roll_no = 15;
ROLLBACK TO s1;
COMMIT;✔ Name updated
❌ Marks not updated
6️⃣ Diagram Explained in Words (Transaction Flow)
Think of a whiteboard:
- Write changes with chalk ✍️
- Until COMMIT → changes are temporary
- COMMIT → take permanent photo 📸
- ROLLBACK → erase board 🧽
- SAVEPOINT → mark a checkpoint line 🟦
7️⃣ ECET Exam Important Points ⭐
✔ COMMIT makes changes permanent
✔ ROLLBACK undoes changes before COMMIT
✔ SAVEPOINT allows partial rollback
✔ TCL commands work with DML (INSERT, UPDATE, DELETE)
✔ DDL commands (CREATE, DROP) do implicit COMMIT
⚙️ Formulas (Plain LaTeX – No Boxes)
(DBMS topic – No mathematical formulas applicable)
🔟 10 MCQs (ECET + GATE Hybrid)
Q1. Which SQL command permanently saves a transaction?
A. ROLLBACK
B. SAVEPOINT
C. COMMIT
D. DELETE
Q2. ROLLBACK command can undo changes made after:
A. SAVEPOINT
B. COMMIT
C. DROP
D. SELECT
Q3. SAVEPOINT is used to:
A. End transaction
B. Delete table
C. Perform partial rollback
D. Commit changes
Q4. Which command creates a checkpoint in a transaction?
A. COMMIT
B. DELETE
C. SAVEPOINT
D. ROLLBACK
Q5. TCL commands work with which SQL commands?
A. DDL
B. DML
C. DCL
D. SELECT
Q6. Which of the following causes implicit COMMIT?
A. INSERT
B. UPDATE
C. CREATE
D. DELETE
Q7. After COMMIT, which command cannot undo changes?
A. SAVEPOINT
B. DELETE
C. ROLLBACK
D. SELECT
Q8. SAVEPOINT is valid until:
A. Server restart
B. ROLLBACK
C. COMMIT
D. SELECT
Q9. Which command undoes changes up to SAVEPOINT?
A. ROLLBACK
B. ROLLBACK TO SAVEPOINT
C. COMMIT
D. DELETE
Q10. TCL stands for:
A. Table Control Language
B. Transaction Control Language
C. Transfer Control Language
D. Technical Control Language
✅ Answer Key (WordPress Table — NO HTML)
Q No | Answer
1 | C
2 | A
3 | C
4 | C
5 | B
6 | C
7 | C
8 | C
9 | B
10 | B
🧠 MCQ Explanations
Q1: COMMIT saves data permanently ✔
Others undo or manipulate data ❌
Q2: ROLLBACK can revert till last SAVEPOINT ✔
Not after COMMIT ❌
Q3: SAVEPOINT allows partial rollback ✔
Not for commit or delete ❌
Q4: SAVEPOINT creates transaction checkpoints ✔
Q5: TCL works only with DML commands ✔
Q6: DDL commands auto-commit ✔
Q7: After COMMIT, ROLLBACK fails ✔
Q8: SAVEPOINT removed after COMMIT ✔
Q9: ROLLBACK TO SAVEPOINT is correct ✔
Q10: Full form is Transaction Control Language ✔
🎯 Motivation (ECET 2026 Specific)
This topic appears every year in ECET from DBMS basics.
Mastering COMMIT–ROLLBACK–SAVEPOINT gives you:
- Easy 1–2 guaranteed marks
- Strong foundation for Transactions & Concurrency
- Confidence in SQL questions
👉 Consistent revision = Rank boost 🚀
📲 CTA (Fixed)
Join our ECET 2026 CSE WhatsApp Group for daily quizzes & study notes:
https://chat.whatsapp.com/GniYuv3CYVDKjPWEN086X9
