SQL Server › Transactions & ACID

How do you use a SAVE TRANSACTION savepoint within a larger transaction to allow partial rollback of just a portion of the work?

Intermediate

Answer

You mark a specific point within your transaction using SAVE TRANSACTION with a given name, and if you later need to undo only the changes made after that point, you can roll back specifically to that savepoint using ROLLBACK TRANSACTION with the same name, without undoing the entire transaction from the very beginning.

BEGIN TRANSACTION;
UPDATE Orders SET Status = 'Processing' WHERE OrderId = 1;
SAVE TRANSACTION BeforeRiskyStep;
UPDATE Inventory SET Quantity = Quantity - 1 WHERE ProductId = 5;
-- If something goes wrong here
ROLLBACK TRANSACTION BeforeRiskyStep;
COMMIT TRANSACTION;

Real-world example An order processing transaction uses a savepoint before a risky inventory update step, allowing it to roll back just that specific risky part if it fails, while still keeping the earlier order status update intact and committing the overall transaction.

Common follow-ups: Can you have multiple savepoints within a single transaction?;What happens to the transaction if you never explicitly roll back to a savepoint?

Error Handling with TRY CATCH;Isolation & Locking