ROLLBACK
Learn how the ROLLBACK statement is used to undo uncommitted changes made during a SQL Server transaction.
What is ROLLBACK?
ROLLBACK is a SQL Server transaction command used to undo changes made during the current transaction.
ROLLBACK is useful when an error occurs or when we decide that the changes made during the transaction should not be saved.
Why Do We Use ROLLBACK?
- To undo unwanted changes.
- To handle errors during a transaction.
- To prevent incorrect data from being saved.
- To restore the database to the state before the current transaction.
ROLLBACK Syntax
BEGIN TRANSACTION;
-- SQL statements
ROLLBACK;
Simple ROLLBACK Example
Suppose we insert a student record but later decide that the operation should not be saved.
BEGIN TRANSACTION;
INSERT INTO Students
(
StudentId,
StudentName,
City,
Age
)
VALUES
(
301,
'Rahul',
'Pune',
22
);
ROLLBACK;
The INSERT operation is undone because the transaction is rolled back.
UPDATE with ROLLBACK
BEGIN TRANSACTION;
UPDATE Students
SET City = 'Mumbai'
WHERE StudentId = 301;
ROLLBACK;
The UPDATE is performed inside the transaction. ROLLBACK then undoes the uncommitted change.
DELETE with ROLLBACK
BEGIN TRANSACTION;
DELETE FROM Students
WHERE StudentId = 301;
ROLLBACK;
The DELETE operation is undone because ROLLBACK cancels the uncommitted transaction changes.
COMMIT vs ROLLBACK
| COMMIT | ROLLBACK |
|---|---|
| Permanently saves changes. | Undoes uncommitted changes. |
| Used when the transaction is successful. | Used when the transaction should be cancelled or an error occurs. |
| Completes the transaction. | Rolls back the transaction changes. |
ROLLBACK After an Error
ROLLBACK is commonly used when an error occurs during a transaction.
BEGIN TRANSACTION;
UPDATE Students
SET Age = Age + 1
WHERE StudentId = 301;
-- If an error occurs
ROLLBACK;
The transaction changes can be undone instead of leaving the database with unwanted changes.
ROLLBACK with Multiple Operations
BEGIN TRANSACTION;
INSERT INTO Students
(
StudentId,
StudentName,
City,
Age
)
VALUES
(
302,
'Priya',
'Pune',
21
);
UPDATE Students
SET Age = 22
WHERE StudentId = 302;
DELETE FROM Students
WHERE StudentId = 302;
ROLLBACK;
All uncommitted changes made during the transaction are rolled back.
Important Points
- ROLLBACK undoes uncommitted transaction changes.
- ROLLBACK is commonly used when an error occurs.
- ROLLBACK can undo INSERT, UPDATE and DELETE operations performed in the transaction.
- Once changes are committed, a normal ROLLBACK cannot undo those committed changes.
- ROLLBACK helps prevent unwanted data changes from being permanently saved.
Summary
ROLLBACK is used to undo changes made during a transaction that have not been committed.
It is especially useful when an error occurs or when the transaction should not be saved.