Transactions: Rolling back a transaction inside a stored procedure.

4

January 6, 2014 by Kenneth Fisher

So over the last couple of posts I’ve talked about the fact that the ROLLBACK command will roll back an entire transaction no matter how many layers down the ROLLBACK is executed. Well this has an interesting implication with a stored procedure. If a ROLLBACK command is issued inside of a stored procedure then any transactions begun outside of the stored procedure will be rolled back as well and @@TRANCOUNT will be set to 0.

CREATE TABLE tb_TransactionTest (value int)
GO
-- This stored procedure will roll back a transaction if the 
-- @ROLLBACK parameter is a 1.
CREATE PROCEDURE usp_TransactionTest @Value int, @RollBack bit
AS 
BEGIN
	BEGIN TRANSACTION
	INSERT INTO tb_TransactionTest VALUES (@Value)
	IF @Rollback = 1 
		-- If the procedure is called from within a transaction
		-- this is going to cause us to have a different 
		-- @@TRANCOUNT when we exit the procedure than when we
		-- started it.
		ROLLBACK TRANSACTION
	ELSE
		COMMIT
END
GO

-- Begin a new transaction
BEGIN TRANSACTION
INSERT INTO tb_TransactionTest VALUES (1)
-- Run the sp with the param to roll back a transaction
-- This will return an error because tran count has changed
EXEC usp_TransactionTest 2,1
-- Run the commit to close the initial transaction
-- This will return an error because there is no valid transaction
-- to commit.
COMMIT
-- No rows are in the table because the initial insert was
-- rolled back.
SELECT * FROM tb_TransactionTest
GO

The obvious problem here is that any code that uses that stored procedure is going to have to check @@TRANCOUNT before issuing a ROLLBACK or a COMMIT or risk an error because there is no transaction to close. The less obvious problem is that SQL doesn’t like it if the transaction count is different after the execution of a stored procedure. So in the example above we are actually going to get two errors and no data in the tb_TransactionTest table. The solution to both problems is to use the SAVE TRANSACTION command inside the stored procedure.

-- This stored procedure will roll back a saved transaction if 
-- the @ROLLBACK parameter is a 1.
ALTER PROCEDURE usp_TransactionTest @Value int, @RollBack bit
AS 
BEGIN
    BEGIN TRANSACTION
    SAVE TRANSACTION TranTest
    INSERT INTO tb_TransactionTest VALUES (@Value)
    IF @Rollback = 1 
        -- Roll back to the saved point.
        -- The transaction is not closed and  
        -- @@TRANCONT is not changed.
        ROLLBACK TRANSACTION TranTest
    -- Close the transaction created at the beginning of the SP
    COMMIT
END
GO

TRUNCATE TABLE tb_TransactionTest
-- Begin a new transaction
BEGIN TRANSACTION
INSERT INTO tb_TransactionTest VALUES (1)
-- Run the sp with the param to roll back a transaction
EXEC usp_TransactionTest 2,1
-- Run the commit to close the initial transaction
COMMIT
SELECT * FROM tb_TransactionTest
GO

This time at the end of the batch we have no errors and a row with a 1 in tb_TransactionTest. Now this was a very simple example and there is a much better one in BOL under SAVE TRANSACTION that I highly recommend reviewing before dealing with a transaction inside a stored procedure.

Transactions are a big subject which I’m going to explore over several posts. I am by no means going to cover the subject exhaustively but if you have any subjects you would like me to cover or think I’ve missed something feel free to comment or email me.

About these ads

4 thoughts on “Transactions: Rolling back a transaction inside a stored procedure.

  1. steve says:

    Thanks Kenneth, but your second example is flawed. If you run EXEC usp_TransactionTest 2,0 it will break because you’re committing the transaction twice, once inside at the end of the procedure and again after coming out of it. I’m guessing you meant to have a BEGIN TRANSACTION before the SAVE TRANSACTION, so that there’s two nested transactions and two commits.

  2. Steve says:

    – Since we are rolling back to a saved transaction
    — @@TRANCONT will go back to what it was right before
    — the SAVE TRANSACTION

    I think you are confusing savepoints and transactions. A savepoint is a marker for a unit of work within a transaction and does not equate to a new nested transaction, hence rolling back to a savepoint does not decrement the @@TRANCOUNT.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 338 other followers

%d bloggers like this: