SY Experiment No.6

📝 Experiment No. 06: Execute TCL Commands to Control Transactions

---

🔍 Theory Questions

1. Need for TCL Commands in SQL

• TCL commands are used to manage transactions in a database.

• A transaction = a logical unit of work that may consist of multiple SQL statements.

• Need arises when:

  • We want to ensure data consistency (e.g., bank transfer must update debit and credit together).

  • We need the ability to undo changes (ROLLBACK) if something goes wrong.

  • We want to save progress (SAVEPOINT) and finalize changes (COMMIT).

---

2. Difference between COMMIT and ROLLBACK

Command	Description	Effect
COMMIT	Saves all changes made by the transaction permanently in the database.	Cannot be undone.
ROLLBACK	Undoes all changes made in the current transaction since the last COMMIT.	Restores database to previous consistent state.

---

3. SAVEPOINT Command

• A SAVEPOINT is like a checkpoint inside a transaction.

• You can roll back to a specific savepoint without undoing the entire transaction.

✅ Syntax:

SAVEPOINT savepoint_name;
ROLLBACK TO savepoint_name;

📌 Purpose: Helps in partial rollbacks during large transactions.

---

4. States of a Transaction

A transaction typically goes through these states:

   [Active] → [Partially Committed] → [Committed]
       |                |
       ↓                ↓
   [Failed]        [Aborted → Rollback → Active]

• Active: Transaction is being executed.

• Partially Committed: After last statement, before changes are saved.

• Committed: All changes are permanently saved.

• Failed: Due to error or system crash.

• Aborted: Transaction rolled back.

---

5. ACID Properties of a Transaction

1. Atomicity – "All or nothing": either the whole transaction executes or none of it.

2. Consistency – Ensures database moves from one valid state to another valid state.

3. Isolation – Each transaction is independent; no interference.

4. Durability – Once committed, changes are permanent even after system failure.

---

💻 SQL Lab Exercises

Assume we have an EMP table:

CREATE TABLE EMP (
  empno INT PRIMARY KEY,
  ename VARCHAR(50),
  sal DECIMAL(10,2)
);

Sample Data:

INSERT INTO EMP VALUES (101, 'Amit', 40000);
INSERT INTO EMP VALUES (102, 'Sneha', 50000);
INSERT INTO EMP VALUES (103, 'Rahul', 35000);

---

Q1. TCL command to save all changes made so far

COMMIT;

📌 This makes all changes permanent in the database.

---

Q2. Delete one record and undo deletion

START TRANSACTION;

DELETE FROM EMP WHERE empno = 103;

-- Oops! We want to undo:
ROLLBACK;

✅ After ROLLBACK, the record with empno = 103 will be restored.

---

Q3. Set a savepoint named BeforeSalaryUpdate

START TRANSACTION;

SAVEPOINT BeforeSalaryUpdate;

✅ This allows you to later do:

ROLLBACK TO BeforeSalaryUpdate;

to undo only the changes made after this savepoint.

---

📊 Sample Workflow in MySQL

START TRANSACTION;

UPDATE EMP SET sal = sal + 5000 WHERE empno = 101;

SAVEPOINT BeforeSalaryUpdate;

UPDATE EMP SET sal = sal - 2000 WHERE empno = 102;

-- Realize mistake:
ROLLBACK TO BeforeSalaryUpdate;

-- Only the second update is undone, first update remains.

COMMIT;

---

✅ Result:

We understood the importance of transaction control in SQL.

We learned how to perform different changes on a table and still recover the original data using ROLLBACK, which helps us experiment safely without permanent damage.

We also practiced the use of COMMIT, ROLLBACK, and SAVEPOINT, and understood how the ACID properties ensure that every transaction is reliable and consistent.

---