none
How does @@TRANCOUNT behave with escalated distributed transactions?

    Question

  • I understand that an explicit declaration of a distributed transaction (BEGIN DISTRIBUTED TRANSACTION) does not change the @@TRANCOUNT return value at all (and correct me if I'm wrong,  because I was unable to test it by myself due to the lack of MS DTC in my testing platform). But I'm concerned about what could possibly happen with this value when a normal, local transaction, automatically escalates to a distributed one because of a command referring to an external database.

    Any idea?

    Wednesday, July 24, 2013 2:31 AM

All replies

  • Hi John,

    As far as I know, Distributed transactions will not affect @@TRANCOUNT values. MSDN document states:

    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.

    For more detail information, you can refer to the following link:

    @@TRANCOUNT (Transact-SQL)
    http://msdn.microsoft.com/en-us/library/ms187967.aspx


    Allen Li
    TechNet Community Support

    Friday, July 26, 2013 2:01 AM
    Moderator
  • Ok, thanks!

    Anyway, my question was a bit different. Imagine this scenario. I begin a local transaction:

    BEGIN TRAN

    At this point, @@TRANCOUNT value equals 1. That I know.

    But now, imagine I do something like this:

    INSERT INTO RemoteServer.RemoteDB.RemoteSchema.RemoteDB (RemoteColumnName) VALUES (@RemoteValue)

    The transaction becomes a distributed one.

    How much is the @@TRANCOUNT RETURN VALUE at this point?

    That is the question. Sorry if I wasn't explicit enough.


    Friday, July 26, 2013 2:45 AM