When it's necessary to check @@trancount > 0 in try catch block?

Question

Sometimes I saw the following code snippet. When is the if @@trancount > 0 necessary with begin try? Both of them? Or it's a safe way(best practice) to check it always in case it's rollback before the check?

begin tran
begin try
  ... just several lines of sql ...
  if @@trancount > 0 commit tran
end try
begin catch
  if @@trancount > 0 rollback tran
end catch

Finer

Answer 1

I can think of a few scenarios to consider when dealing with @@trancount:

1. The current transaction was called from another stored procedure which had its own transaction
2. The current transaction was called by some .NET code with its own transaction
3. The current transaction is the only transaction

I believe Remus Rusanu's Exception handling and nested transactions handles all these possibilities.

Chlorous

The example seems try to avoid nested transactions. Is it necessary? – Finer

To me, the idea is that if this new action is a nested transaction, then save it. If there is an error and if this is a nested transaction, only rollback the nested transaction so as not to affect the original caller. – Chlorous

4: when you're calling some rubbish encrypted API SP that doesn't rollback its own transaction if it decides there's a problem... – Particularism

So? So check @@trancount or not? – Dunaj

I'd say this accepted "answer" absolutely does NOT answer the question. – Veranda

Answer 2

To answer the question - the time to do a @@trancount check is if the code in the middle could potentially have already performed the commit or rollback of the transaction you started. So if you are calling stored procedures for example - then perform the checks at the end.

Incidentally rather than doing an if @@trancount > 0 I would suggest it is better to check the @@trancount at the start of your block of code, and then see if the count has gone up by the end, in which case do the commit or rollback, depending on try/catch.

Particularly if you are in a trigger, because the @@trancount will always be 1 there, so just doing a @@trancount > 0 could cause an error.

But even if your code is just in a stored procedure, supposed it was called by another procedure that itself has an open transaction, if your code errors and rolls back, then the outer stored procedure will have its transaction rolled back also (see https://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-2630-nested-transactions-are-real/).

So

BEGIN TRAN
PRINT @@TRANCOUNT

BEGIN TRAN
PRINT @@TRANCOUNT

ROLLBACK TRAN
PRINT @@TRANCOUNT

Will print this output:

1
2
0

So basically - if the code in the middle is calling other procedures, you need to perform the IF @@TRANCOUNT check.

Saltillo

Answer 3

when u don't use @@trancount, the error message of nested transaction stored procedure does not return the exact cause of error just reurtn "The rollback transaction request has no corresponding begin transaction",otherwise it gives exact cause of error, so its easy to handle the error with proper syntax.

Dittography

Answer 4

the reason of check is if you commit trans or rollback it when @@trancount=0 you get an exception with this error message : The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.

Pawsner

Answer 5

PRINT @@TRANCOUNT  
--  The BEGIN TRAN statement will increment the  
--  transaction count by 1.  
BEGIN TRAN  
    PRINT @@TRANCOUNT  
    BEGIN TRAN  
        PRINT @@TRANCOUNT  
--  The COMMIT statement will decrement the transaction count by 1.  
    COMMIT  
    PRINT @@TRANCOUNT  
COMMIT  
PRINT @@TRANCOUNT  
--Results  
--0  
--1  
--2  
--1  
--0  

Aperient