I have this statement block with transaction and try/catch:
begin try
begin transaction;
insert ....
update ....
delete ....
commit transaction;
end try
begin catch
if (@@trancount > 0)
rollback transaction;
throw;
end catch;
I am using @@trancount here without fully understanding what happens.
Why exactly could it ever happen that @@trancount is zero in this case?
The most common scenario to receive @@trancount = 0 requires a bit more elaborated logic than your sample uses.
If you have other stored procedures called in the try block, they might have their own understanding of how transactions should be managed, and either due to a poorly written code, or some other mishap, they can either commit the outer transaction by accident, or roll it back (remember, there is no really such thing in SQL Server as a nested transaction, so any rollback statement that doesn't reference a previously declared savepoint wipes out everything). The error in this case might vary, either the one that caused the inner procedure to misbehave in the first place, or if nothing else, you'll get error 266, "Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = %ld, current count = %ld."
Note that this behaviour can also be caused by rollback done by a trigger, as well.
It is possible that there might be some other situations when you end up with no current transaction in the catch block, but apparently they are so rare that I can't think of anything else off the top of my head.
Personally, I use the following template for all my stored procedures whenever possible:
create procedure dbo.ProcTemplate
(
@Error int = null output,
@Message nvarchar(2048) = null output
) as
/*
20191223, RW - to be completed
*/
set nocount, quoted_identifier, ansi_nulls, ansi_warnings, ansi_padding, concat_null_yields_null, arithabort on;
set xact_abort, implicit_transactions, numeric_roundabort off;
declare @XTran bit = cast(sign(@@trancount) as bit);
begin try
if @XTran = 0
begin tran;
-- Put your code here
if @XTran = 0
commit;
end try
begin catch
if nullif(@Error, 0) is null
select @Error = error_number(), @Message = error_message();
if @@trancount > 0 and @XTran = 0
rollback;
end catch;
return;
go
One can argue that explicitly issuing set xact_abort off might result in some unpleasant side effects, such as a batch-terminating error (208, for instance) skips the catch and leaves the current transaction open. That's up to you; the trade-offs here are:
