Understanding SQL Server Transaction Savepoints

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:

Resources

Community

Subscribe