SQL Topics
ROLLBACK in SQL
title: ROLLBACK in SQL
In the previous lesson, you learned about COMMIT, which permanently saves all successful changes made during a transaction.
However, not every transaction completes successfully.
Sometimes:
System Failure
Network Failure
Application Error
Incorrect Data Entry
Business Rule Violationmay occur during transaction execution.
If partial changes remain in the database, data inconsistency can occur.
To prevent this problem, SQL provides the ROLLBACK statement.
ROLLBACK cancels all changes made during the current transaction and restores the database to its previous state.
It acts as a safety mechanism that protects data integrity when something goes wrong.
What is ROLLBACK?
ROLLBACK is a transaction control command that undoes changes made during a transaction.
Think of it as:
Undo ChangesWhen ROLLBACK executes:
Transaction Fails
↓
Undo All Changes
↓
Restore Previous StateWhy is ROLLBACK Important?
Consider a banking transaction.
Steps:
1. Withdraw ₹1000
2. Deposit ₹1000Suppose:
Without ROLLBACK:
Money Disappearsbecause only one operation succeeded.
With ROLLBACK:
Entire Transaction Undoneand data remains consistent.
How ROLLBACK Works
Transaction Flow:
BEGIN TRANSACTION
↓
Execute Statements
↓
Error Occurs
↓
ROLLBACK
↓
Undo ChangesTransaction Lifecycle
Successful Transaction:
BEGIN
↓
Execute SQL
↓
COMMIT
↓
Save ChangesFailed Transaction:
BEGIN
↓
Execute SQL
↓
Error
↓
ROLLBACK
↓
Undo ChangesBasic ROLLBACK Syntax
BEGIN TRANSACTION;
SQL Statements;
ROLLBACK;Example:
BEGIN TRANSACTION;
UPDATE Employees
SET Salary = 80000
WHERE EmployeeID = 1;
ROLLBACK;Result:
Salary Restored
To Previous ValueCreating Sample Table
CREATE TABLE Employees (
EmployeeID INT,
EmployeeName VARCHAR(100),
Salary DECIMAL(10,2)
);Insert sample data:
INSERT INTO Employees VALUES
(1,'Rahul',50000),
(2,'Priya',60000);First ROLLBACK Example
BEGIN TRANSACTION;
UPDATE Employees
SET Salary = 70000
WHERE EmployeeID = 1;
ROLLBACK;Before:
| EmployeeID | Salary |
|---|---|
| 1 | 50000 |
After ROLLBACK:
| EmployeeID | Salary |
|---|---|
| 1 | 50000 |
No permanent change occurs.
ROLLBACK with INSERT
Example:
BEGIN TRANSACTION;
INSERT INTO Employees
VALUES
(
3,
'Amit',
70000
);
ROLLBACK;Result:
Employee Not Addedbecause insertion is undone.
ROLLBACK with UPDATE
Example:
BEGIN TRANSACTION;
UPDATE Employees
SET Salary = 90000
WHERE EmployeeID = 2;
ROLLBACK;Result:
Original Salary RestoredROLLBACK with DELETE
Example:
BEGIN TRANSACTION;
DELETE FROM Employees
WHERE EmployeeID = 2;
ROLLBACK;Result:
Deleted Row RestoredBanking Example
Initial Data:
| Account | Balance |
|---|---|
| A | 10000 |
| B | 5000 |
Transaction:
BEGIN TRANSACTION;
UPDATE Accounts
SET Balance = Balance - 1000
WHERE AccountID = 1;
ERROR OCCURS;
ROLLBACK;Final Data:
| Account | Balance |
|---|---|
| A | 10000 |
| B | 5000 |
Database remains consistent.
E-Commerce Example
Transaction:
Create Order
Update Inventory
Process PaymentSuppose:
Payment FailsThen:
ROLLBACK;Result:
Order Cancelled
Inventory RestoredPayroll System Example
Transaction:
Calculate Salary
Deduct Tax
Update AccountsIf any step fails:
ROLLBACK;No incorrect payroll records are saved.
ROLLBACK vs COMMIT
COMMIT:
Save ChangesROLLBACK:
Undo ChangesComparison:
| Feature | COMMIT | ROLLBACK |
|---|---|---|
| Saves Changes | Yes | No |
| Undoes Changes | No | Yes |
| Permanent Update | Yes | No |
| Ends Transaction | Yes | Yes |
ROLLBACK and SAVEPOINT
Normally:
ROLLBACK;undoes the entire transaction.
However:
ROLLBACK TO SavePointName;undoes only part of the transaction.
This feature is covered in the SAVEPOINT lesson.
Automatic Rollback
Many database systems automatically rollback transactions when:
Server Crash
Power Failure
Transaction Failureoccurs.
This protects database consistency.
Advantages of ROLLBACK
Protects Data Integrity
Incorrect data is removed.
Supports Error Recovery
Failed transactions can be reversed.
Prevents Partial Updates
All-or-nothing behavior.
Improves Reliability
Applications remain consistent.
Essential for Critical Systems
Banking and financial systems depend on rollback functionality.
Disadvantages of ROLLBACK
Lost Work
All transaction changes are discarded.
Additional Processing
Undo operations require resources.
Long Transactions Cost More
Large rollbacks may take time.
Performance Considerations
Small rollbacks:
FastLarge rollbacks:
May Require Significant ResourcesEspecially when millions of rows are involved.
Common Errors
Forgetting ROLLBACK
Failed transactions may leave temporary changes.
Assuming COMMIT Can Be Rolled Back
Once committed:
ROLLBACK Cannot Undo ItLong Transactions
Large rollbacks become expensive.
Poor Error Handling
May leave transactions open.
Best Practices
Use Proper Error Handling
Always handle failures.
Keep Transactions Short
Minimize rollback cost.
Validate Data Early
Prevent unnecessary rollbacks.
Test Failure Scenarios
Verify rollback behavior.
Monitor Transaction Logs
Useful for troubleshooting.
Common Interview Questions
What is ROLLBACK?
A transaction control command that undoes transaction changes.
When is ROLLBACK used?
When errors occur during transaction execution.
What happens after ROLLBACK?
The database returns to its previous state.
Can ROLLBACK undo COMMIT?
No.
Committed changes are permanent.
Why is ROLLBACK important?
It protects data consistency and supports error recovery.
Summary
ROLLBACK is a critical transaction control command that undoes changes made during a transaction. It protects database integrity, prevents partial updates, and ensures that failed transactions do not leave inconsistent data behind.
In this lesson, you learned:
- What ROLLBACK is
- Why ROLLBACK is important
- How ROLLBACK works
- ROLLBACK syntax
- ROLLBACK with INSERT
- ROLLBACK with UPDATE
- ROLLBACK with DELETE
- Banking examples
- E-commerce examples
- COMMIT vs ROLLBACK
- Performance considerations
- Best practices
Mastering ROLLBACK is essential because every reliable database system requires a mechanism to safely recover from errors.
Next Step
Continue to the next lesson:
SAVEPOINT in SQL →
Exam Focus
Revise definitions, diagrams, examples, and short-answer points for ROLLBACK in SQL.
Interview Use
Prepare one clear explanation, one practical example, and one common mistake for this SQL topic.
Search Terms
sql, sql complete guide, sql tutorial, sql notes, complete, guide, transactions, rollback
Related SQL Topics