5.6 TCL — COMMIT, ROLLBACK, SAVEPOINT

Level: 5 | Version: v1.1 | Author: Meptrasoft

Overview

TCL (Transaction Control Language) is commonly used to control database transactions.

The main commands are:

Command	Purpose
`COMMIT`	Save transaction changes
`ROLLBACK`	Undo uncommitted changes
`SAVEPOINT`	Create a point to roll back to

Think of a transaction like a series of steps:

BEGIN
  ↓
SQL OPERATIONS
  ↓
COMMIT / ROLLBACK

TCL controls what happens to changes made during a transaction.

COMMIT

COMMIT permanently saves the changes made during the current transaction.

Example:

BEGIN;

UPDATE employees
SET salary = 48000.00
WHERE emp_id = 1;

COMMIT;

After COMMIT, the salary change becomes part of the committed database state.

COMMIT → Save changes

ROLLBACK

ROLLBACK undoes uncommitted changes made during the current transaction.

Example:

BEGIN;

DELETE FROM employees
WHERE emp_id = 2;

ROLLBACK;

The DELETE is undone, so the row for employee 2 remains.

ROLLBACK → Undo uncommitted changes

SAVEPOINT

A SAVEPOINT creates a checkpoint inside a transaction.

You can roll back to that point without undoing all earlier changes in the transaction.

Example:

BEGIN;

UPDATE employees
SET salary = 46000.00
WHERE emp_id = 3;

SAVEPOINT after_salary_update;

DELETE FROM employees
WHERE emp_id = 3;

ROLLBACK TO after_salary_update;

COMMIT;

Here:

The salary update remains.
The delete is undone.
The final COMMIT saves the remaining change.

Simple Flow

UPDATE
  ↓
SAVEPOINT
  ↓
DELETE
  ↓
ROLLBACK TO SAVEPOINT
  ↓
DELETE UNDONE
  ↓
COMMIT

SAVEPOINT → Create a checkpoint inside a transaction

COMMIT vs ROLLBACK vs SAVEPOINT

Command	Main Action
`COMMIT`	Save transaction changes
`ROLLBACK`	Undo uncommitted changes
`SAVEPOINT`	Create a rollback checkpoint

Easy Memory Trick

COMMIT     → SAVE
ROLLBACK   → UNDO
SAVEPOINT  → CHECKPOINT

Important Difference

Consider:

BEGIN
  ↓
UPDATE
  ↓
SAVEPOINT
  ↓
DELETE
  ↓
ROLLBACK TO SAVEPOINT

Only the work after the savepoint is undone.

But:

BEGIN
  ↓
UPDATE
  ↓
DELETE
  ↓
ROLLBACK

rolls back the entire uncommitted transaction.

So:

ROLLBACK → Undo the transaction's uncommitted changes
ROLLBACK TO SAVEPOINT → Undo changes back to a specific checkpoint

Practice Table

This topic reuses the employees table from 5.2 DDL.

emp_id	emp_name	department	salary
1	Ravi Kumar	IT	45000
2	Anjali Mehta	HR	38000
3	Suresh Rao	Finance	42000
4	Priya Nair	IT	50000

Common Beginner Mistakes

✗ Mistake	✓ Correct Understanding
`COMMIT` undoes changes	`COMMIT` saves the transaction
`ROLLBACK` saves changes	`ROLLBACK` undoes uncommitted changes
`SAVEPOINT` commits a transaction	It only creates a checkpoint inside the transaction
`ROLLBACK TO` undoes the entire transaction	It rolls back to the specified savepoint
`COMMIT` can normally be followed by `ROLLBACK` to undo it	Normal rollback cannot undo already committed work
TCL changes table structure	TCL controls transaction changes

Placement Quick Points

COMMIT
→ SAVE CHANGES

ROLLBACK
→ UNDO UNCOMMITTED CHANGES

SAVEPOINT
→ CREATE CHECKPOINT

TCL is commonly used to control database transactions.
COMMIT saves transaction changes.
ROLLBACK undoes uncommitted transaction changes.
SAVEPOINT creates a checkpoint within a transaction.
ROLLBACK TO SAVEPOINT returns the transaction to that checkpoint.
Transaction behavior can vary depending on the database system and transaction settings.

Interview Questions

What is TCL?

TCL stands for Transaction Control Language and is commonly used to control transaction changes.

What does COMMIT do?

COMMIT saves the changes made by the current transaction.

What does ROLLBACK do?

ROLLBACK undoes uncommitted changes made during the current transaction.

What is a SAVEPOINT?

A savepoint is a checkpoint created inside a transaction so that the transaction can be rolled back to that point.

What is the difference between ROLLBACK and ROLLBACK TO SAVEPOINT?

ROLLBACK → Undo uncommitted transaction changes

ROLLBACK TO SAVEPOINT → Undo changes after a specific checkpoint

What happens after COMMIT?

The transaction's changes are committed and become part of the database's committed state.

Give a real-world example of SAVEPOINT.

A long transaction may contain several steps. A savepoint allows the application to undo only the later steps while keeping earlier work.

Practice & Hands-On Exercises

Using the employees table:

Update an employee's salary and use COMMIT:

BEGIN;
UPDATE employees SET salary = 48000.00 WHERE emp_id = 1;
COMMIT;

Delete an employee and use ROLLBACK:

BEGIN;
DELETE FROM employees WHERE emp_id = 2;
ROLLBACK;

Perform an update, create a savepoint, perform another change, and use ROLLBACK TO the savepoint:

BEGIN;
UPDATE employees SET salary = 46000.00 WHERE emp_id = 3;
SAVEPOINT after_salary_update;
DELETE FROM employees WHERE emp_id = 3;
ROLLBACK TO after_salary_update;
COMMIT;

Explain which changes remain after the final COMMIT.
Explain the difference between ROLLBACK and ROLLBACK TO SAVEPOINT.

💡 Tip: Test your queries using the in-browser interactive runner above.

Key Takeaway

COMMIT
→ SAVE

ROLLBACK
→ UNDO

SAVEPOINT
→ CHECKPOINT

TCL controls transaction changes: COMMIT saves them, ROLLBACK undoes uncommitted changes, and SAVEPOINT creates a checkpoint for partial rollback.

End of TCL — COMMIT, ROLLBACK, SAVEPOINT