By: Sergey Gigoyan | Updated: 2018-06-14 | Comments | Related: > Transactions
Problem
SQL Server savepoints are used to roll back transactions to a specified point. In the other words, this lets you roll back part of the transaction instead of the entire transaction. So, if we are rolling back transactions to a specific savepoint, only statements after the savepoint and before the rollback command will be rolled back. In this tip, we will illustrate the usage of savepoints and the behavior of transactions with savepoints.
Solution
Savepoints can be useful when it is necessary to roll back part of a SQL Server transaction. This is usually the case when there is a low possibility of error in part of the transaction, and the prior validation of the operation’s accuracy is too costly. Savepoints can be also used in stored procedures to be able to successfully manage transactions in the nesting processes.
We are going to illustrate the usage of savepoints with examples and note some important facts about them. Before that, we will create a test environment:
USE master GO CREATE DATABASE TestDB GO USE TestDB GO CREATE TABLE TestTable ( ID INT NOT NULL, Value INT NOT NULL, PRIMARY KEY (ID) ) GO
Setting a SQL Server Transaction Savepoint
To set a savepoint within a transaction, the SAVE TRANSACTION <savepoint_name> command is used, SAVE TRAN <savepoint_name> syntax is also acceptable. The <savepoint_name> can be an identifier with a maximum of 32 characters. It can also be a user-defined variable with a char, varchar, nchar, nnvarchar data type and must contain a name conforming to the rules of identifiers.
In the following example, the setting and usage of a savepoint is illustrated:
USE TestDB GO TRUNCATE TABLE TestTable BEGIN TRANSACTION INSERT INTO TestTable( ID, Value ) VALUES ( 1, N'10') -- this will create a savepoint after the first INSERT SAVE TRANSACTION FirstInsert INSERT INTO TestTable( ID, Value ) VALUES ( 2, N'20') -- this will rollback to the savepoint right after the first INSERT was done ROLLBACK TRANSACTION FirstInsert -- this will commit the transaction leaving just the first INSERT COMMIT SELECT * FROM TestTable
As we can see, only the first insert statement committed, because the transaction was rolled back to the savepoint before the second insert:
In the next example, the transaction rolls back to the second savepoint whose name is set using a variable:
USE TestDB GO TRUNCATE TABLE TestTable DECLARE @vSecondInsert NCHAR(50)='SecondInsert' BEGIN TRANSACTION INSERT INTO TestTable( ID, Value ) VALUES ( 1, N'10') -- this will create a savepoint after the first INSERT SAVE TRANSACTION FirstInsert INSERT INTO TestTable( ID, Value ) VALUES ( 2, N'20') -- this will create a savepoint after the second INSERT SAVE TRANSACTION @vSecondInsert INSERT INTO TestTable( ID, Value ) VALUES ( 3, N'30') ROLLBACK TRANSACTION @vSecondInsert -- can also use ROLLBACK TRANSACTION SecondInsert COMMIT SELECT * FROM TestTable
The result will be the same, if we use “ROLLBACK TRANSACTION @vSecondInsert” or “ROLLBACK TRANSACTION SecondInsert”.
Using SQL Server transaction savepoints with the same savepoint name
As mentioned above, a savepoint name can have a maximum of 32 characters. If a savepoint variable is used instead of a savepoint name and a string with more than 32 characters is assigned to it, only the first 32 characters will define the savepoint name and the remaining characters will be ignored.
It is likewise possible to have savepoints with the same names, but in the case of a rollback to the savepoint, the transaction will be rolled back to the latest SAVE TRANSACTION using that name. Here is an example.
USE TestDB GO TRUNCATE TABLE TestTable BEGIN TRANSACTION INSERT INTO TestTable( ID, Value ) VALUES ( 1, N'10') -- this will create a savepoint SAVE TRANSACTION DataInsert INSERT INTO TestTable( ID, Value ) VALUES ( 2, N'20') -- this will create a savepoint with same name SAVE TRANSACTION DataInsert INSERT INTO TestTable( ID, Value ) VALUES ( 3, N'30') -- this will rollback to the last savepoint named DataInsert ROLLBACK TRANSACTION DataInsert COMMIT SELECT * FROM TestTable
In the example above, duplicate savepoint names are used and the transaction rolled back to the second savepoint, therefore the result will be the following:
It is worth mentioning that, in case of rolling back the transaction to the savepoint, locks acquired after the savepoint will be released (but escalations and conversions are exceptions).
Rolling Back to the SQL Server Savepoint and @@TRANCOUNT Variable
Finally, it is essential to understand the impact that rolling back to a savepoint will have on @@TRANCOUNT variable. The short answer – it will not change the @@TRANCOUNT, however, we will discuss the details below.
A ROLLBACK statement will set the value of @@TRANCOUNT variable back to 0 if it is used to roll back the whole transaction without referencing a savepoint. However, if a transaction is rolled back to the savepoint, the @@TRANCOUNT variable is not changed.
The example below illustrates this phenomenon:
USE TestDB GO TRUNCATE TABLE TestTable -- nothing has happened yet, so value will be 0 SELECT @@TRANCOUNT AS '1. - @@TRANCOUNT before starting the first transaction (its value is 0)' BEGIN TRANSACTION -- trans 1 SELECT @@TRANCOUNT AS '2. - @@TRANCOUNT after starting the first transaction (its value is incremented by 1)' INSERT INTO TestTable(ID, Value) VALUES(1,'10') SAVE TRANSACTION FirstInsert BEGIN TRANSACTION -- trans 2 SELECT @@TRANCOUNT AS '3. - @@TRANCOUNT after starting the second transaction (its value again is incremented by 1)' INSERT INTO TestTable(ID, Value) VALUES(2,'20') ROLLBACK TRANSACTION FirstInsert SELECT @@TRANCOUNT AS '4. - @@TRANCOUNT after rolling back to the savepoint (its value is not changed)' BEGIN TRANSACTION -- trans 3 SELECT @@TRANCOUNT AS '5. - @@TRANCOUNT after starting the third transaction (its value is incremented by 1)' INSERT INTO TestTable(ID, Value) VALUES(3,'30') COMMIT -- trans 3 SELECT @@TRANCOUNT AS '6. - @@TRANCOUNT after committing the third transaction (its value is decremented by 1)' -- only id 1 and 3 remain SELECT * FROM TestTable COMMIT -- trans 2 SELECT @@TRANCOUNT AS '7. - @@TRANCOUNT after committing second transaction (its value is set to 1)' ROLLBACK -- trans 1 SELECT @@TRANCOUNT AS '8. - @@TRANCOUNT after rolling back first transaction (its value is set to 0)' -- no data remains after rollback SELECT * FROM TestTable
From this example, it can be observed that before starting the transaction, the @@TRANCOUNT value is 0. It is incremented by 1 by starting each transaction and it is reduced by 1 in case of any COMMIT. In the case of a ROLLBACK to the savepoint, the value of the @@TRANCOUNT is not changed. However, a general ROLLBACK statement that rolls back all transaction will set the value of the @@TRANCOUNT to 0.
Here is the output from the above code.
Conclusion
All in all, savepoints can be useful in transactions, so understanding their meaning, features and behavior will be very helpful to you as you build applications, data import/export routines and data conversions.
Next Steps
Useful information related to this article can be found below:
- https://docs.microsoft.com/en-us/sql/t-sql/language-elements/save-transaction-transact-sql
- https://docs.microsoft.com/en-us/sql/t-sql/language-elements/rollback-transaction-transact-sql
- https://www.mssqltips.com/sqlservertip/4897/handling-transactions-in-nested-sql-server-stored-procedures/
- https://www.mssqltips.com/sqlservertip/4018/sql-server-transaction-count-after-execute-indicates-a-mismatching-number-of-begin-and-commit-statements/
About the author
This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2018-06-14