Implementing Savepoints for Partial Rollback in SQL Transactions

Savepoints in Transactions

Write a SQL script that uses savepoints to partially roll back a transaction.

Solution:

BEGIN TRANSACTION;

-- Insert data into the Products table.
INSERT INTO Products (ProductID, Name) VALUES (101, 'Laptop');

SAVE TRANSACTION SavePoint1; -- Create a savepoint.

-- Insert data into the Suppliers table.
INSERT INTO Suppliers (SupplierID, Name) VALUES (201, 'Tech Corp');

-- Rollback to the savepoint if needed.
ROLLBACK TRANSACTION SavePoint1;

COMMIT TRANSACTION;

Explanation:

• Purpose of the Query :
• The goal is to demonstrate how savepoints allow partial rollback within a transaction.
• Key Components :
• SAVE TRANSACTION: Creates a savepoint.
• ROLLBACK TRANSACTION SavePoint1: Rolls back to the savepoint.
• Why use Savepoints? :
• Savepoints allow fine-grained control over transaction rollback without affecting the entire transaction.
• Real-World Application :
• Useful in complex workflows where certain steps may need to be undone while keeping others intact.

Additional Notes:

• Savepoints allow partial rollback within a transaction without affecting the entire operation.
• They are particularly useful in complex workflows where certain steps may need to be undone while keeping others intact.
• Note that savepoints do not release locks acquired during the transaction.

For more Practice: Solve these Related Problems:

• Write a SQL query to create a savepoint after inserting data into a table and rollback to that savepoint if an error occurs later in the transaction.
• Write a SQL query to use savepoints to undo specific steps in a multi-step transaction without affecting the entire operation.
• Write a SQL query to demonstrate how savepoints can be used to retry a failed operation within a transaction.
• Write a SQL query to simulate a complex workflow where certain steps are rolled back using savepoints while others are committed.

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous SQL Exercise: Demonstrate Isolation Level REPEATABLE READ.
Next SQL Exercise: Locking Hints.