Transactions are a great way to group statements together in an all or nothing batch.
However, nesting transactions is where things get a little messy, some people even call them evil.

I note here that a big part of the reason people don’t like nested transactions is because they bloat the transaction log file, something which I will not be going into here.

The Problem with Nested Transactions

The biggest problem that I’ve seen with nested transactions is to do with handling errors and rollbacks.

A transaction is still all or nothing, no matter if you have one or ten.

When a rollback happens, it rolls back to the top transaction – everything. Naming the transaction doesn’t help it just causes an error.

Try this…

BEGIN TRANSACTION one
BEGIN TRANSACTION two
ROLLBACK TRANSACTION two
PRINT 'Trans Count = ' + CAST(@@TRANCOUNT AS NVARCHAR(5))
ROLLBACK TRANSACTION one
PRINT 'Trans Count = ' + CAST(@@TRANCOUNT AS NVARCHAR(5))

You would expect the output to be like this…
Trans Count = 1
Trans Count = 0

But instead you get this…
Cannot roll back two. No transaction or savepoint of that name was found.
Trans Count = 2
Trans Count = 0

The reason for the error is that you can only rollback the primary or first transaction. Transaction two is added to the SYSPROCESSES table and increases the transaction count, but is not a transaction that can be rolled back.

Therefore, in the above example, there is no transaction two to roll back, so you get an error and the count stays at 2, but after rolling back transaction one, it rolls back everything and the count is 0.

Got that? I hope so.

Save Points for Nesting Transactions

The best practice way of doing nested transactions is using savepoints. Savepoints can be set within a transaction to define a location to which a transaction can be rolled back to.

A savepoint looks like this…

CREATE TABLE dbo.TestTable (ID INT IDENTITY NOT NULL, Value NVARCHAR(50) DEFAULT 'test' NOT NULL)

GO
BEGIN TRANSACTION
    INSERT INTO dbo.TestTable DEFAULT VALUES

    -- set a save point
    SAVE TRANSACTION saveOne
    INSERT INTO dbo.TestTable DEFAULT VALUES

    -- set a second save point
    SAVE TRANSACTION saveTwo
    INSERT INTO dbo.TestTable DEFAULT VALUES

    -- rollback to first save point
    ROLLBACK TRANSACTION saveOne

-- need to commit the transaction for anything to save
COMMIT TRANSACTION

-- check the results
SELECT * FROM dbo.TestTable

-- clean up
DROP TABLE IF EXISTS dbo.TestTable

In this example the rollback rolls back to the first save, so the results are only the first insert.

You will notice is that there is no commit for the save, a commit only happens on a transaction.

It is important to note that the ROLLBACK looks the same as the transaction ROLLBACK. This is why I recommend you only name your save rollbacks, not your transaction rollback.

Handling Nested Rollbacks

Sometimes we don’t have much choice but to nest transactions, this can easily occur when we are using an existing Stored Procedure which has a transaction of its own inside our transaction.

In this case, assess if you need the outer transaction, if you do you will need to wrap the execute inside error handling because, if the inside transaction is rolled back, you will get the error…

Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 0.

I talk about error handling for nested transactions in part 2.

Main Points

• A Rollback on a Transaction will roll back all nested transactions and save points.
• Use savepoints as nested transactions
• Don’t use a name on a transaction rollback – it’ll only confuse things at best, or cause an error at worst.