SAVEPOINT
Learn how SAVEPOINT is used to create a specific point inside a SQL Server transaction and roll back to that point.
What is a SAVEPOINT?
A SAVEPOINT is a named point created inside a transaction.
It allows us to roll back only part of a transaction instead of rolling back the entire transaction.
In SQL Server, the command SAVE TRANSACTION is used to create a savepoint.
Why Do We Use SAVEPOINT?
- To create a specific point inside a transaction.
- To roll back only part of a transaction.
- To avoid rolling back the complete transaction.
- To provide more control over transaction operations.
SAVEPOINT Syntax
BEGIN TRANSACTION;
SAVE TRANSACTION SavePointName;
-- SQL statements
ROLLBACK TRANSACTION SavePointName;
COMMIT;
Creating a SAVEPOINT
We can create a savepoint using the SAVE TRANSACTION statement.
BEGIN TRANSACTION;
SAVE TRANSACTION StudentSavePoint;
Here, StudentSavePoint is the name of the savepoint.
Simple SAVEPOINT Example
BEGIN TRANSACTION;
INSERT INTO Students
(
StudentId,
StudentName,
City,
Age
)
VALUES
(
401,
'Rahul',
'Pune',
22
);
SAVE TRANSACTION StudentSavePoint;
UPDATE Students
SET City = 'Mumbai'
WHERE StudentId = 401;
ROLLBACK TRANSACTION StudentSavePoint;
COMMIT;
In this example, the INSERT operation remains part of the transaction, while the UPDATE performed after the savepoint is rolled back.
ROLLBACK to a SAVEPOINT
To roll back to a specific savepoint, use ROLLBACK TRANSACTION followed by the savepoint name.
ROLLBACK TRANSACTION StudentSavePoint;
This rolls back the transaction to the specified savepoint rather than rolling back the entire transaction.
Multiple SAVEPOINTS
Multiple savepoints can be created during a transaction.
BEGIN TRANSACTION;
INSERT INTO Students
(
StudentId,
StudentName,
City,
Age
)
VALUES
(
402,
'Priya',
'Pune',
21
);
SAVE TRANSACTION Point1;
UPDATE Students
SET Age = 22
WHERE StudentId = 402;
SAVE TRANSACTION Point2;
UPDATE Students
SET City = 'Mumbai'
WHERE StudentId = 402;
ROLLBACK TRANSACTION Point2;
COMMIT;
Here, the transaction contains more than one savepoint, allowing more precise control over the transaction.
SAVEPOINT vs ROLLBACK
| SAVEPOINT | ROLLBACK |
|---|---|
| Creates a point inside a transaction. | Undoes transaction changes. |
| Allows partial rollback. | Can roll back the entire transaction or to a specified savepoint. |
| Created using SAVE TRANSACTION. | Performed using ROLLBACK TRANSACTION. |
Real-World Example
Consider an online order where several operations are performed in sequence:
- Create an order.
- Add order items.
- Update product stock.
- Process additional operations.
A savepoint can be created after an important stage. If a later operation fails, the transaction can be rolled back to that savepoint instead of starting everything again.
Important Points
- SQL Server uses SAVE TRANSACTION to create a savepoint.
- A savepoint must be created inside a transaction.
- A transaction can contain multiple savepoints.
- ROLLBACK TRANSACTION followed by a savepoint name rolls back to that point.
- A savepoint provides more control than rolling back the complete transaction.
- COMMIT is used when the final transaction changes should be permanently saved.
Summary
A SAVEPOINT is a named point inside a transaction. It allows us to roll back a portion of the transaction without rolling back everything.
SQL Server creates savepoints using SAVE TRANSACTION and rolls back to them using ROLLBACK TRANSACTION.