Transaction counts with named transactions

  • According to the BOL:

    The BEGIN TRANSACTION statement increments @@TRANCOUNT by 1. ROLLBACK TRANSACTION decrements @@TRANCOUNT to 0, except for ROLLBACK TRANSACTION savepoint_name, which does not affect @@TRANCOUNT. COMMIT TRANSACTION or COMMIT WORK decrement @@TRANCOUNT by 1.

    And this code:

    CREATE TABLE #TEMP (

    value int

    )

    SET NOCOUNT ON;

    PRINT @@TRANCOUNT

    BEGIN TRANSACTION;

    PRINT @@TRANCOUNT

    insert into #TEMP select 1;

    insert into #TEMP select 2;

    PRINT @@TRANCOUNT

    COMMIT TRANSACTION;

    PRINT @@TRANCOUNT

    SELECT * FROM #TEMP;

    PRINT '-----';

    PRINT @@TRANCOUNT

    BEGIN TRANSACTION MyTran;

    PRINT @@TRANCOUNT

    insert into #TEMP select 3;

    insert into #temp select 4;

    PRINT @@TRANCOUNT

    ROLLBACK TRANSACTION MyTran;

    PRINT @@TRANCOUNT

    select * from #TEMP;

    drop table #temp;

    SET NOCOUNT OFF;

    Why does 'ROLLBACK TRANSACTION MyTran;' affect the rowcount? Isn't this what BOL was talking about? Why would it be useful for 'ROLLBACK TRANSACTION MyTran;' to not affect @@TRANSACOUNT?

    ---
    Dlongnecker

  • If you re-read the BOL entry you will see it is behaving exactly as defined. I think you may be confusing a rollback of a named transaction (which is what your code is doing) with a rollback to a savepoint (which must be defined with a savepoint statement).

    Named transactions are a bit of an oddity really as the rollback always rolls back the lot - personally I have never found any benefit in named transactions.

    Mike John

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply