Transaction Control Commands

Excellent 👍
Now TCL (Transaction Control Language) in the SAME structured pattern:

• Base tables

• Question

• Transaction SQL

• State BEFORE & AFTER (outputs)

• ✔ crystal-clear takeaways

---

📘 Base Tables (Used for ALL TCL examples)

employees

emp_id	name	age
1	Amit	30
2	Neha	28

---

products

product_id	category	price
1	Electronics	20
2	Furniture	100

---

customers

customer_id	name	age
1	Ram	65
2	Sita	45

---

🧾 Summary Table (TCL Questions)

No.	TCL Command	Question
1	COMMIT	Save all changes permanently
2	ROLLBACK	Undo all changes
3	SAVEPOINT	Create rollback checkpoints
4	ROLLBACK TO SAVEPOINT	Roll back to a specific point
5	SET TRANSACTION	Set isolation level

---

1️⃣ COMMIT

Question: Insert an employee and update product price, then save changes.

BEGIN TRANSACTION;

INSERT INTO employees (name, age)
VALUES ('Alice', 30);

UPDATE products
SET price = 25
WHERE category = 'Electronics';

COMMIT;

✅ Result AFTER COMMIT

employees

emp_id	name	age
1	Amit	30
2	Neha	28
3	Alice	30

products

category	price
Electronics	25
Furniture	100

✔ Changes saved permanently
✔ Cannot be undone

---

2️⃣ ROLLBACK

Question: Insert & update data, then undo everything.

BEGIN TRANSACTION;

INSERT INTO employees (name, age)
VALUES ('Bob', 35);

UPDATE products
SET price = 30
WHERE category = 'Electronics';

ROLLBACK;

✅ Result AFTER ROLLBACK

employees (unchanged)

emp_id	name	age
1	Amit	30
2	Neha	28
3	Alice	30

products (unchanged)

category	price
Electronics	25
Furniture	100

✔ All changes cancelled
✔ Database returns to last COMMIT

---

3️⃣ SAVEPOINT

Question: Create savepoints and roll back partially.

BEGIN TRANSACTION;

INSERT INTO employees (name, age)
VALUES ('Carol', 28);

SAVEPOINT before_update;

UPDATE products
SET price = 40
WHERE category = 'Electronics';

SAVEPOINT after_update;

DELETE FROM customers
WHERE age > 60;

ROLLBACK TO before_update;

COMMIT;

✅ Final Result

employees

name	age
Carol	28

products

category	price
Electronics	25
Furniture	100

customers

name	age
Ram	65
Sita	45

✔ DELETE rolled back
✔ UPDATE rolled back
✔ INSERT kept

---

4️⃣ ROLLBACK TO SAVEPOINT

Question: Roll back only the UPDATE, keep INSERT.

BEGIN TRANSACTION;

INSERT INTO employees (name, age)
VALUES ('David', 42);

SAVEPOINT before_update;

UPDATE products
SET price = 50
WHERE category = 'Electronics';

SAVEPOINT after_update;

DELETE FROM customers
WHERE age > 60;

ROLLBACK TO SAVEPOINT before_update;

COMMIT;

✅ Final Result

employees

name	age
David	42

products

category	price
Electronics	25
Furniture	100

customers

name	age
Ram	65
Sita	45

✔ INSERT kept
✔ UPDATE cancelled
✔ DELETE cancelled

---

5️⃣ SET TRANSACTION

Question: Set isolation level and commit changes.

BEGIN TRANSACTION;

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

INSERT INTO employees (name, age)
VALUES ('Emily', 35);

UPDATE products
SET price = 60
WHERE category = 'Electronics';

COMMIT;

✅ Result AFTER COMMIT

employees

name	age
Emily	35

products

category	price
Electronics	60
Furniture	100

✔ Controls transaction behavior
✔ Affects concurrency & consistency

---

🔥 TCL Command Comparison (Exam Favorite)

Command	Purpose
COMMIT	Save changes
ROLLBACK	Undo all changes
SAVEPOINT	Create checkpoint
ROLLBACK TO	Undo to checkpoint
SET TRANSACTION	Configure transaction

---

🧠 One-Line Memory Trick (TCL)

COMMIT saves, ROLLBACK cancels, SAVEPOINT bookmarks

---

If you want next:

• All SQL commands in ONE final master table

• MCQs with transaction output prediction

• ACID properties mapped to TCL

• Hindi + exam-trap explanations

Just say 👍