T-SQL: SQL Server and Transaction Savepoints

  • Article

Introduction

This article describes Transaction Savepoints in SQL Server. 

Problem 

 Sometimes we come across situations when we need to use more than one transaction (nested transactions). However, a rollback operation affects all open transactions.

Solution

 
Create a simple table to illustrate:

CREATE TABLE  MYTABLE 
(
    ID INT  IDENTITY(1, 1), 
    EMPLOYEE VARCHAR(30), 
    DATE DATETIME
)

Then we use BEGIN TRANSACTION to open three transactions (TRAN1, TRAN2 and TRAN3) and execute some operations to insert and update. Finally, we made transaction rollback on TRAN1.

BEGIN TRANSACTION  TRAN1
 
INSERT INTO  MYTABLE VALUES  ('Jon Skeet', GETDATE())
 
BEGIN TRANSACTION  TRAN2
 
INSERT INTO  MYTABLE VALUES  ('Ed Price', GETDATE())
 
BEGIN TRANSACTION  TRAN3
 
UPDATE MYTABLE SET EMPLOYEE = 'Bob Ward', DATE  = GETDATE() WHERE  EMPLOYEE = 'Ed Price'
 
ROLLBACK TRANSACTION  TRAN1

SQL Server prints three rows affected. We expect the employee inserted into tran1 be discarded and that the employee informed in TRAN3 has overridden the employee in TRAN2. But conducting a search in the table realized that nothing was entered despite the SQL Server has not triggered any error in the execution of the t-sql.

Performing an DBCC to inspect opened transactions returns no active transactions.

DBCC TRACEON(3604)
DBCC OPENTRAN()

This means that the rollback has affected the three transactions even specifying TRAN1. To circumvent this type of obstacle you can use savepoints in transactions.

Alter the t-sql to:

BEGIN TRANSACTION
 
INSERT INTO  MYTABLE VALUES  ('Jon Skeet', GETDATE())
 
SAVE TRANSACTION  SAVEPOINT1
 
INSERT INTO  MYTABLE VALUES  ('Ed Price', GETDATE())
 
ROLLBACK TRANSACTION  SAVEPOINT1
 
UPDATE MYTABLE SET EMPLOYEE = 'Bob Ward', DATE  = GETDATE() WHERE  EMPLOYEE = 'Ed Price'
 
COMMIT TRANSACTION

First, we inserted Jon Skeet and created a savepoint called SAVEPOINT1. Then we inserted Ed Price and made the rollback SAVEPOINT1. So overridden Ed Price (if exists) to Bob Ward and commited the transaction. Performing SELECT * FROM MYTABLE gets only Jon Skeet that is the correct. Because of the savepoint we can rollback only a defined part of the transaction.

Note

You should note that locks created during a transaction are retained when rolling back to a savepoint. They are released only when the entire transaction is committed or rolled back.

Conclusion

We showed that how Transaction Savepoints in SQL Server works. 

See Also

Other Resources