TCL

📘 What is TCL (Transaction Control Language)?

TCL (Transaction Control Language) is the part of SQL used to manage transactions — a group of operations that should either all happen together or none at all.

Think of TCL as the safety switch in SQL. It makes sure your database doesn’t end up in a half-updated, broken state.

---

🧩 First, What’s a Transaction?

A transaction is a single unit of work that may include multiple DML operations (e.g., INSERT, UPDATE, DELETE) — and it should be atomic, i.e., either all of it happens, or none of it does.

Example use case: Transferring money from account A to B.

-- Step 1: Withdraw from A
UPDATE accounts SET balance = balance - 100 WHERE id = 'A';
 
-- Step 2: Deposit to B
UPDATE accounts SET balance = balance + 100 WHERE id = 'B';

If step 1 happens but step 2 fails, the money disappears. That’s where TCL saves you.

---

🛠️ Key TCL Commands:

Command	Purpose
BEGIN / START TRANSACTION	Marks the beginning of a transaction
COMMIT	Saves all changes made during the transaction
ROLLBACK	Cancels all changes made during the transaction
SAVEPOINT	Sets a checkpoint inside a transaction so you can partially rollback
RELEASE SAVEPOINT	Deletes a savepoint so it can’t be rolled back to
SET TRANSACTION	Sets isolation level for consistency vs performance trade-offs

---

🔁 Example Flow:

BEGIN;
 
UPDATE accounts SET balance = balance - 100 WHERE id = 'A';
UPDATE accounts SET balance = balance + 100 WHERE id = 'B';
 
COMMIT; -- permanently saves both operations

If something fails before COMMIT:

 
ROLLBACK; -- cancels both updates

---

📌 SAVEPOINT Example:

BEGIN;
UPDATE users SET status = 'inactive' WHERE id = 1;
SAVEPOINT before_delete;
DELETE FROM users WHERE id = 2;
ROLLBACK TO before_delete;
COMMIT;

Only the delete is undone; the status update remains.

---

🔒 Transactions Must Follow ACID:

Property	Meaning
Atomicity	All or nothing
Consistency	Data must remain valid before and after the transaction
Isolation	Transactions don’t interfere with each other
Durability	Once committed, changes persist even if the system crashes

---

🧠 Interview Line:

“TCL manages transactions using commands like COMMIT, ROLLBACK, and SAVEPOINT to ensure data consistency and atomicity — crucial for operations that must succeed or fail as a whole.”