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.

Nested Transactions

SQL Server includes the concept of nested transactions, where one transaction is started within the scope of another. You might conclude that this would allow you to create a nested transaction, perform some actions and then roll back the changes of the nested transaction, allowing the outer transaction to continue as normal and be rolled back or committed later. Unfortunately, nested transactions do not behave in this manner. You cannot use them to reverse parts of a transaction, which can lead to errors when transactions are nested inadvertently.

To demonstrate the problem, we need a simple table. Create one in a test database with the following script:

CREATE TABLE People
(
    Name VARCHAR(30)
)

Consider the code below. Here we start by creating a new transaction. Let's call this the outer transaction. We then insert a row into the People table, providing the name, "Tom". Next we create a nested transaction and insert another row with the name, "Dick". The final two lines attempt to complete the transactions, rolling back the inner transaction and committing the outer. If this was valid, the People table would receive one new row with the name, "Tom", as the "Dick" row is inserted during the rolled back transaction.

BEGIN TRAN

INSERT INTO People VALUES ('Tom')

BEGIN TRAN

INSERT INTO People VALUES ('Dick')

ROLLBACK TRAN

COMMIT TRAN

If you execute the above sample you will find that it generates the error:

This error suggests that the final line of the script, which commits the outer transaction, failed because the outer transaction no longer exists. Indeed, this is the case. The problem is that nested transactions do not behave in the manner you might expect. When you try to rollback a nested transaction, all active transactions are cancelled.

We can see this by reading the @@TRANCOUNT variable at several points in the script. @@TRANCOUNT returns an integer containing the number of active transactions. It is incremented each time a BEGIN TRAN command is encountered. A value of zero indicates that no transaction is currently active.

Run the following script and view the messages that are outputted. Note that each BEGIN TRAN increments the transaction count but that the rollback operation cancels both the inner and outer transactions.

SET NOCOUNT ON

BEGIN TRAN
PRINT 'First Transaction: ' + CONVERT(VARCHAR,@@TRANCOUNT)

INSERT INTO People VALUES ('Tom')

BEGIN TRAN
PRINT 'Second Transaction: ' + CONVERT(VARCHAR,@@TRANCOUNT)

INSERT INTO People VALUES ('Dick')

ROLLBACK TRAN
PRINT 'Rollback: ' + CONVERT(VARCHAR,@@TRANCOUNT)

/* MESSAGES

First Transaction: 1
Second Transaction: 2
Rollback: 0

*/

This behaviour means that nested transactions do not allow us to perform partial rollbacks. In addition, nested transactions can introduce error conditions. For example, imagine you have a stored procedure or trigger that contains a transaction that is rolled back. If the process calling the procedure also uses a transaction, it's transaction could be unexpectedly rolled back by the stored procedure.

A solution to both of these problems is the use of transaction savepoints.

Transaction Savepoints

Within a transaction you can create one or more transaction savepoints. These mark a point in a transaction to which you may wish to rollback. When you rollback to a savepoint, all of the database updates performed after that savepoint are reversed. Updates that happened after the transaction started but before the savepoint was declared are not affected.

You can create multiple savepoints within a single transaction and roll them back individually. However, it's important to note that rolling back to a savepoint also removes any savepoints that were created later. For example, if you created savepoints named "s1", "s2" and "s3" in that order, rolling back savepoint "s2" would remove savepoint "s3". Savepoint "s1" would still be active.

You never commit a savepoint manually. The updates following a savepoint that has not been rolled back will be stored or discarded according to whether the containing transaction is committed or rolled back.

To create a savepoint, use the SAVE TRAN or SAVE TRANSACTION command. You must provide a name for the savepoint. This should be a string of up to 32 characters. If the name is longer than 32 characters the additional text is ignored. You can provide the name within a variable if desired.

Rolling back a partial transaction to a savepoint position is achieved with the standard ROLLBACK TRAN or ROLLBACK TRANSACTION statement, providing the name of the savepoint to roll back to.

We can now modify our original, failing example to use savepoints. Below we are creating the initial transaction in the same manner as before. The nested transaction has been replaced by a savepoint, with the name, "Savepoint1". After inserting the row for Dick, we rollback to the savepoint before committing the transaction.

Try executing the example. You should find that "Tom" is inserted into the table as expected but that "Dick" is not. You can see from the outputted messages that only one transaction is used for the process.

SET NOCOUNT ON

BEGIN TRAN
PRINT 'First Transaction: ' + CONVERT(VARCHAR,@@TRANCOUNT)

INSERT INTO People VALUES ('Tom')

SAVE TRAN Savepoint1
PRINT 'Second Transaction: ' + CONVERT(VARCHAR,@@TRANCOUNT)

INSERT INTO People VALUES ('Dick')

ROLLBACK TRAN Savepoint1
PRINT 'Rollback: ' + CONVERT(VARCHAR,@@TRANCOUNT)

COMMIT TRAN
PRINT 'Complete: ' + CONVERT(VARCHAR,@@TRANCOUNT)

/* MESSAGES

First Transaction: 1
Second Transaction: 1
Rollback: 1
Complete: 0

*/