@@TRANCOUNT - always 0 or 1 ?..

I am trying to debug our production problem, and I'm stuck...

A C# .NET 1.1 method creates a SqlTransaction object on a SqlConnection. [This method is called from an ASP.NET 1.1 web application.]

The command invokes a stored procedure on SQL Server 2000 SP4 database.

The stored procedure itself is inside a transaction (that is, all batch code is between BEGIN TRANSACTION and COMMIT/ROLLBACK...).

DECLARE @.a_tran_count INT

SET @.a_tran_count = @.@.TRANCOUNT

DECLARE @.a_tran_name CHAR(13)

SET @.a_tran_name = CONVERT(CHAR(13), @.a_tran_count)

BEGIN TRANSACTION

DECLARE @.a_error INT

<Statements>

SET @.a_error = @.@.ERROR

IF 0 = @.a_error

<Statements>

SET @.a_error = @.@.ERROR

IF 0 = @.a_error

<Statements>

SET @.a_error = @.@.ERROR

IF @.@.TRANCOUNT > @.a_tran_count

BEGIN

IF 0 <> @.a_error

ROLLBACK TRANSACTION @.a_tran_name

COMMIT TRANSACTION

IF 0 = @.a_error

END

RETURN(@.a_error)

Apparently, there is an error in the last IF block (there should be ELSE just before COMMIT), and I don't like the error handling (it would be much more efficient to just rollback the transaction as soon as @.@.ERROR <> 0), but what I got stuck with is the following:

Is it possible that @.@.TRANCOUNT server variable will ever be other than 0 or 1?

I wrote some test page in C#, and it proved that SqlConnection indeed cannot support parallel transactions. But that's a .NET object, NOT a SQL Server's transaction...

I researched BOL and MSDN, but failed to find an answer to the question: if there can be only one transaction per one connection, then what's the point of using @.@.TRANCOUNT? So far, the only usage of it that I can imagine is for checking whether the current transaction has been started...

Also, using the @.a_tran_count (which is @.@.TRANCOUNT) to build the transaction name is kinda confusing as well... Is this name really needed in a procedure like this one?.. And what happens if there is another transaction running on this server which name is the same (such as "0")? Which transaction will be rolled back?

If the value of the @.@.TRANCOUNT can (at least, theoretically, - under certain rare circumstances) be greater than 1, it would mean I have found the cause of our current critical production issue and would be able to fix it relatively easily. So, any help would be greatly appreciated.

Thank you.

@.@.trancount is incremented by one for each "begin tran" and is decremented by one for each "commit":

if @.@.trancount > 0 rollback

begin tran

select @.@.trancount --1

begin tran

select @.@.trancount --2

commit

select @.@.trancount --1

commit

select @.@.trancount --0

It is set to zero when a "rollback" is issued:

if @.@.trancount > 0 rollback

begin tran

select @.@.trancount --1

begin tran

select @.@.trancount --2

rollback

select @.@.trancount --0

Because of the fact that a "rollback" sets the transaction count to zero, SQL Server is not usually considered to fully support "nested transactions".

Ron

|||as explained...@.@.trancount count does increase with each transaction added...rollback its it to zero just to add that... commit does @.@.trancount - 1...