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...
