SQL TCL Commands (COMMIT, ROLLBACK, SAVEPOINT)

---

1. AIM

To study and execute SQL Transaction Control Language (TCL) commands — COMMIT, ROLLBACK, and SAVEPOINT — in MySQL using example tables.

---

2. THEORY

TCL (Transaction Control Language) commands control changes made by DML statements (INSERT, UPDATE, DELETE) and manage transactions.

A transaction is a group of operations performed as a single logical unit of work.

✔ TCL Commands

Command	Purpose
COMMIT	Saves all changes made during the current transaction
ROLLBACK	Undoes changes since the last COMMIT
SAVEPOINT	Creates a checkpoint inside a transaction
ROLLBACK TO SAVEPOINT	Rolls back to a specific savepoint
SET AUTOCOMMIT = 0	Disables automatic commit in MySQL

MySQL automatically commits each statement unless AUTOCOMMIT is OFF.

---

3. TABLE CREATION FOR LAB

Use the following table to perform TCL commands.

CREATE TABLE accounts (
    acc_id INT PRIMARY KEY,
    acc_name VARCHAR(50),
    balance INT
);

INSERT INTO accounts VALUES
(1, 'Alice', 5000),
(2, 'Bob', 7000),
(3, 'Charlie', 9000);

---

4. TCL COMMANDS WITH LAB EXAMPLES

---

Experiment 1: COMMIT

Step 1 — Turn Off Auto-commit

SET AUTOCOMMIT = 0;

Step 2 — Delete a row

DELETE FROM accounts WHERE acc_id = 3;

Step 3 — Check Table

SELECT * FROM accounts;

Output (before COMMIT):

acc_id	acc_name	balance
1	Alice	5000
2	Bob	7000

Step 4 — COMMIT the changes

COMMIT;

After COMMIT, the deletion becomes permanent.

---

Experiment 2: ROLLBACK

Step 1 — Turn off Auto-commit

SET AUTOCOMMIT = 0;

Step 2 — Update a record

UPDATE accounts SET balance = balance + 2000 WHERE acc_id = 1;

Step 3 — Check balance

SELECT * FROM accounts;

Output (before rollback):

acc_id	acc_name	balance
1	Alice	7000

Step 4 — Undo changes

ROLLBACK;

Step 5 — Check again

SELECT * FROM accounts;

Output (after rollback):

acc_id	acc_name	balance
1	Alice	5000

Rollback restored original balance.

---

Experiment 3: SAVEPOINT and ROLLBACK TO SAVEPOINT

Step 1 — Start transaction

SET AUTOCOMMIT = 0;

Step 2 — Reduce Bob’s balance

UPDATE accounts SET balance = balance - 1000 WHERE acc_id = 2;

Step 3 — Create SAVEPOINT

SAVEPOINT S1;

Step 4 — Apply another change

UPDATE accounts SET balance = balance - 500 WHERE acc_id = 2;

Step 5 — Rollback to savepoint

ROLLBACK TO S1;

Check results

SELECT * FROM accounts;

Output:
Bob’s balance is rolled back only to the savepoint stage, not to original state.

---

Experiment 4: Deleting With Savepoints

SET AUTOCOMMIT = 0;

DELETE FROM accounts WHERE acc_id = 1;
SAVEPOINT del1;

DELETE FROM accounts WHERE acc_id = 2;

ROLLBACK TO del1;

After rollback, only deletion of Alice remains reversed.

---

5. PRACTICE QUERIES FOR STUDENTS

Q1. Increase Charlie’s balance by 3000, then rollback.

Q2. Insert a new account, create a savepoint, update it, roll back to savepoint.
Q3. Delete two records using two savepoints and rollback to the second one.
Q4. Try committing after creating a savepoint — observe the effect.
Q5. Disable autocommit and perform a series of transactions manually.

---

6. RESULT

Students successfully used COMMIT, ROLLBACK, and SAVEPOINT to manage transactions in MySQL. They learned how to control the execution and reversal of DML operations using TCL commands.