Running Transactions in a Stored Procedure

A lot of times, I write a stored procedure to do several things. More often than not, I might be doing mass inserts, updates or deletions. Now, I’m not your average suspenders-and-belt guy. No, I go one step further and insist on wearing an overall over my suspenders and my belt, thank you very much. So, when I have to do that kind of thing, that potentially could do brain damage to my beloved database and not-amusing my favorite clients, I insist that the stored procedure run in a transaction. So, here we go:

CREATE PROCEDURE dbo.uspPotentialBrainDamage AS
BEGIN
BEGIN TRY
BEGIN TRANSACTION;

COMMIT;
END TRY BEGIN CATCH
--Something went wrong. Better undo...
ROLLBACK;
END CATCH;
END;

Great, we’re done, right! Easy as pie! Well, unfortunately, no.

Let’s say that we have this calling code:

BEGIN TRANSACTION;
EXEC dbo.uspPotentialBrainDamage;
COMMIT;

and suppose something did go wrong. What will happen? The data won’t be modified, great. But we’d get something like this:

Msg 266, Level 16, State 2, Procedure uspPotentialBrainDamage, Line 0
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 0.
Msg 3903, Level 16, State 1, Line 4
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

Huh? What is it talking about? We have a balancing BEGIN TRANSACTION and COMMIT/ROLLBACK in our calling code and in the procedure! Why am I getting this silly error?

Well, the truth is that for each BEGIN TRANSACTION we go into, it increments the @@TRANCOUNT by 1. Then for each COMMIT we execute, it decrements the @@TRANCOUNT by 1. BUT, when we get to the ROLLBACK, @@TRANCOUNT is set to zero, irrespective of what it was originally set to. So, the execution path is like this:

{in calling code}
BEGIN TRANSACTION (@@TRANCOUNT now 1)
{in stored procedure}
BEGIN TRANSACTION (@@TRANCOUNT now 2)
{error!}
ROLLBACK (@@TRANCOUNT now 0)
{back to calling code}
COMMIT (thinks we should be at 1 but gets 0 instead)

Now, here’s the annoying thing. This “error” is actually informational. It’s not a bona fide error. Unfortunately, when you execute the code via a passthrough query or via an ADO command object, it implicitly wraps a transaction and thus trips up that error which give you that annoying message. Maybe something really did go wrong but maybe you do want to rollback and not treat it as an error. This “informational” message only serve to obscure and confuse the underlying issue that prevents the stored procedure from doing what it needs to do.

So, what are we to do about that silly message? Well, we can work around this by revising how we handle our errors and transactions:

CREATE PROCEDURE dbo.uspPotentialBrainDamage AS
BEGIN
SET XACT_ABORT ON;
DECLARE @trans int;

IF @trans = 0
COMMIT;
END TRY BEGIN CATCH
--Something went wrong. Better undo...
IF XACT_STATE() <> 0 AND @trans = 0
BEGIN
ROLLBACK TRANSACTION;
END;
DECLARE
@err_msg nvarchar(4000) = ERROR_MESSAGE(),
@err_sev int = ERROR_SEVERITY(),
@err_st int = ERROR_STATE();
RAISERROR(@err_msg,@err_sev,@err_st);
END CATCH;
END;

1) We specified that XACT_ABORT be on, which ensures that any kind of error will cause the transaction to be terminated. Without that, there may be some errors that won’t cause the transaction to stop and frankly, it’s not really cool. We don’t want to wrap things in a transaction under the promise “that it must all succeed together, even with one error!” Huh? No, I much prefer “it must all succeed together, without any kind of errors!”, thanks.

2) We used a local variable to check the @@TRANCOUNT. That way, we only wrap the process in a transaction if it’s not already in one. Otherwise, we can just be assured that we’re covered by the transaction initiated by the calling code.

3) Since we may be inside a transaction that was initiated by the calling code, we don’t want to tell the calling code, “yeah, we succeeded with errors handled.” This might let the calling code do more things that we might not really want it to do, if there was a problem. Therefore, to force the calling code to stop and abandon its own transaction, we need to throw the error back to the calling code, and that’s the purpose of the RAISERROR. (Note: for those on 2012 and newer, the recommended keyword is now THROW. However, 2012 and 2014 are too new so for those still toting along on 2005/2008/2008R2, the RAISERROR is quite serviceable.)

With the original error bubbled up to the calling code, that also rids us of that not-so-informational error message about @@TRANCOUNT count being off and let us deal with the actual error that caused the whole thing to fall to the floor. I’m looking for broom now.

Several people have come up with variants of this pattern. One such example which served as an inspiration for the pattern can be found at Stack Overflow.

Happy transacting!