Is using @@TRANCOUNT useful?

Question

I have a simple SP that will either do an INSERT or an UPDATE depending on the existence or non-existence of data in a table.

CREATE PROCEDURE [dbo].spUpsert 
    -- Parameters to Update / Insert a StudentSet
    @StudentSetId nvarchar(128),
    @Status_Id int

AS
BEGIN
    BEGIN TRY
        BEGIN TRANSACTION
            SET XACT_ABORT ON;
            SET NOCOUNT ON;

            IF EXISTS(SELECT StudentSetId FROM StudentSet WHERE StudentSetId = @StudentSetId)
                BEGIN
                    UPDATE StudentSet SET ModifiedDate = GETDATE(), Status_Id = @Status_Id
                    WHERE StudentSetId = @StudentSetId;
                END
            ELSE
                BEGIN

                    INSERT INTO StudentSet
                                (StudentSetId, Status_Id)
                     VALUES
                           (
                                @StudentSetId,
                                @Status_Id
                           )
                END
        COMMIT TRANSACTION
    END TRY

    BEGIN CATCH
        ROLLBACK TRANSACTION
    END CATCH

END

Wrote a method like so:

public void Upsert(string studentSetId, int statusId)
{
    this.DatabaseJobs.ExecuteSqlCommand(@"exec spUpsert 
                                     @StudentSetId = {0}, 
                                     @Status_Id = {10} ",
                                        studentSetId,
                                        statusId);
}

Here's how this is used: A student has a file, an xml to be precise, that is sent to a processor which calls this SP as part of the process. Multiple files can be uploaded and the processor is designed to work with 5 files spawning 5 threads.

For a batch of 5 files it throws this error:

Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 0. Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 0.

The number 5 is not a perfect one, it may happen when more that 5 files are uploaded. Lesser than that I haven't tried.

So I searched and found a solution that implements the usage of @@TRANCOUNT detailed here & here

@@TRANCOUNT is a global variable and it's usage as suggested in the articles seem's like it's local to the session. What I mean is that any process in SQL Server can increase the @TRANCOUNT and relying on that may not produce the expected result.

My question is what's a good way to handle this type of situation?

Thanks in advance.

you should start your transaction before you start the try-catch block.... — CeOnSql, Jul 17 '15 at 09:38
`this.DatabaseJobs.ExecuteSqlCommand` must be starting a transaction on its own, and `ROLLBACK TRANSACTION` within the procedure causes the rollback of this transaction. — Kuba Wyrostek, Jul 17 '15 at 09:38
Maybe this will help you: http://dba.stackexchange.com/questions/82681/how-to-rollback-when-3-stored-procedures-are-started-from-one-stored-procedure — Kuba Wyrostek, Jul 17 '15 at 09:43
@CeOnSql is it correct to call COMMIT TRAN inside BEGIN TRY just before the END TRY? Won't there be a mismatch the way I have it now? — Codehelp, Jul 17 '15 at 09:59
the commit and rollback is correct. but i always start transaction befor the try block - if something happens at `begin transaction` i don't want to get in catch block (because there is a rollback transaction -> and if begin transaction fails there is nothing to rollback) — CeOnSql, Jul 17 '15 at 10:02

score 2 · Answer 1 · answered Jul 17 '15 at 09:45

First, @@TRANCOUNT is informational - it tells you how many nested transactions are currently in progress in the current thread. In your case, a transaction is already in progress when the stored procedure is called hence the transaction count is 1.

Your problem is that ROLLBACK rolls back all transactions, including any nested transactions. If you wish to abort the whole batch, this is exactly what you want, and the error is simply telling you that it has happened.

However if you only want to roll back the transaction you created locally, you must do something slightly different. You have to save the transaction right at the start, then on error you can roll back to that point (before any work was done), and then commit it (with no work done).

BEGIN TRAN
DECLARE @savepoint varbinary(16) set @savepoint = newid()
SAVE TRAN @savepoint
BEGIN TRY
    -- Do some stuff here
    select 1/0; -- divide by zero error
    COMMIT TRAN
END TRY
BEGIN CATCH
    ROLLBACK TRAN @savepoint;
    COMMIT TRAN -- important!!!
    --re-raise the error if you want (or recover in some other way)

    RAISERROR('Rethrowing error', ERROR_SEVERITY(), ERROR_STATE()    );
END CATCH

score 0 · Answer 2 · answered Jul 17 '15 at 09:48

Well, if the transaction was started in .NET code, it would be good if it rolls back in the same code. However, if it's not possible, then you SHOULD check @@TRANCOUNT.

However, you are missing one important thing: what if transaction wasn't started at all? Your code is constructed in such a way that you need transaction. What if you (or someone else) executes procedure from SSMS?

I suggest you do the following:

at the beginning of your code store @@trancount locally (declare @mytrancount)
before you start your processing, check @mytrancount and if there is no transaction, start one
commit transaction at the end, but feel free to check the @mytrancount again before commit

EDIT

Of course, as Ben stated in his answer, you can save the transaction instead beginning it in the code. E.g., if there is a transaction, save it in order to be able to roll back only the part from SAVE to ROLLBACK. And if there is no transaction, start it in your procedure.

Remus Rusanu has the good template for that.

Is using @@TRANCOUNT useful?

2 Answers2