SQL 2005+

SQL Server Transaction Savepoints

by Richard Carr, published at http://www.blackwasp.co.uk/SQLSavepoints.aspx

Sometimes Transact-SQL (T-SQL) scripts require the ability to roll back some parts of a transaction whilst allowing other elements of a process to be committed to the database normally. This can be achieved with the use of transaction savepoints.

Savepoint Transaction Requirements

A transaction is required in order to create a savepoint. In some situations, such as when executing T-SQL code within a stored procedure, you cannot be sure whether your script will be executed within an existing transaction or not. If you decide to create your own transaction within the procedure and later roll it back, you may roll back a transaction created outside of the procedure, causing errors. However, if you do not create a transaction but use savepoints, your procedure will fail if a transaction does not already exist.

The answer is to avoid creating new transactions within stored procedures unless you first ensure that no transaction is active. To do so, you can check the @@TRANCOUNT variable and only begin a transaction if it is set to zero. Any updates that you want to be able to rollback should follow a savepoint.

The final example shows a script that could be included in a procedure. The script is rather unrealistic as it inserts a new row and immediately rolls back the change. However, it does demonstrate how to check for a transaction and determine whether to use a savepoint or create a new transaction.

The first IF statement checks the current transaction count. If no transaction is available, the @CreatedTransaction flag is set and a new transaction is created. If there is already a transaction in use, we should not affect it, so we set the flag to zero and create a new savepoint. When we need to roll back the insert, we check the @CreatedTransaction flag to determine whether we need to roll back the transaction or the savepoint.

DECLARE @CreatedTransaction BIT

IF (@@TRANCOUNT = 0)
BEGIN
    -- No transaction in use so create one
    SET @CreatedTransaction = 1
    BEGIN TRAN
END
ELSE
BEGIN
    -- A transaction is already present so create a savepoint
    SET @CreatedTransaction = 0
    SAVE TRAN Savepoint1
END

INSERT INTO People VALUES ('Harry')

IF (@CreatedTransaction = 1)
BEGIN
    -- We created the transaction so roll it back
    ROLLBACK TRAN
END
ELSE
BEGIN
    -- We did not create the transaction so roll back to the savepoint only
    ROLLBACK TRAN Savepoint1
END

Considerations

Savepoints can be very useful but there are considerations for their use. Firstly, savepoints have the limitation that they cannot be used in distributed transactions. Secondly, 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.