Binaryworld - SAVE TRAN and Save Points. ... [ SQL Server -> Locks-Transactions ]

Savepoints offer a mechanism to roll back portions of transactions. A user can set a savepoint, or marker, within a transaction. The savepoint defines a location to which a transaction can return if part of the transaction is conditionally canceled. SQL Server allows you to use savepoints via the SAVE TRAN statement, which doesn't affect the @@TRANCOUNT value. A rollback to a savepoint (not a transaction) doesn't affect the value returned by @@TRANCOUNT, either. However, the rollback must explicitly name the savepoint: using ROLLBACK TRAN without a specific name will always roll back the entire transaction.

The following script demonstrates how savepoints can be used :

Click here to copy the following block

USE pubs
SELECT 'Before BEGIN TRAN main', @@TRANCOUNT
-- The value of @@TRANCOUNT is 0

BEGIN TRAN main
  SELECT 'After BEGIN TRAN main', @@TRANCOUNT
-- The value of @@TRANCOUNT is 1
  DELETE sales
  SAVE TRAN sales -- Mark a save point
  SELECT 'After SAVE TRAN sales', @@TRANCOUNT
-- The value of @@TRANCOUNT is still 1

  BEGIN TRAN nested
    SELECT 'After BEGIN TRAN nested', @@TRANCOUNT
    -- The value of @@TRANCOUNT is 2
    DELETE titleauthor
    SAVE TRAN titleauthor -- Mark a save point
    SELECT 'After SAVE TRAN titleauthor', @@TRANCOUNT
    -- The value of @@TRANCOUNT is still 2
  ROLLBACK TRAN sales

  SELECT 'After ROLLBACK TRAN sales', @@TRANCOUNT
   -- The value of @@TRANCOUNT is still 2

  SELECT TOP 5 au_id FROM titleauthor

IF (@@TRANCOUNT > 0) BEGIN
  ROLLBACK TRAN
  SELECT 'AFTER ROLLBACK TRAN', @@TRANCOUNT
  -- The value of @@TRANCOUNT is 0 because
  -- ROLLBACK TRAN always rolls back all transactions and sets @@TRANCOUNT
  -- to 0.
END

SELECT TOP 5 au_id FROM titleauthor