SQL Server Performance

Nested Transaction

Discussion in 'SQL Server 2008 General Developer Questions' started by reddevil, Nov 15, 2009.

  1. reddevil New Member

    Hi ,
    I have written following code..
    Begin Tran
    Begin Tran
    Rollback

    Rollback
    Will this code work ??.. And if it works... if my inner rollback is fired...both transactions will b rolled back...or i will get changes of outer transaction.. ??

  2. satya Moderator

    Welcome to the forums.
    I believe you need to refer to SQL Books ONline where the subject is explainedwith code examples, for instance:
    Explicit transactions can be nested. This is primarily intended to support transactions in stored procedures that can be called either from a process already in a transaction or from processes that have no active transaction.
    Similary each call to COMMIT TRANSACTION or COMMIT WORK applies to the last executed BEGIN TRANSACTION. If the BEGIN TRANSACTION statements are nested, then a COMMIT statement applies only to the last nested transaction, which is the innermost transaction. Even if a COMMIT TRANSACTION transaction_name statement within a nested transaction refers to the transaction name of the outer transaction, the commit applies only to the innermost transaction.
  3. TommCatt New Member

    However, ROLLBACK applies to all transactions. There is no way, afaik, to roll back only to a nested transaction level, named or not. For that reason, it's probably a good idea to place all ROLLBACKs at the outermost level.
  4. FrankKalis Moderator

    [quote user="TommCatt"]However, ROLLBACK applies to all transactions. There is no way, afaik, to roll back only to a nested transaction level, named or not. For that reason, it's probably a good idea to place all ROLLBACKs at the outermost level.[/quote]
    True. I think it therefore makes sense to think about using nested transactions altogether, whether they are useful or not. Having a proper & robust procedure skeleton with transaction handling is quite a bit of work, but it's certainly worth it.
    One point we consider, for example is: We always check whether we are already in a transaction using @@TRANCOUNT. If we are, there is no need to start yet another one, if we are not, then and just then start a new one.
  5. satya Moderator

    Further notes on this confusing topic, the COMMIT TRANSACTION statement does not use the transaction name, only a ROLLBACK uses the transaction name, and only the outermost transaction name.
    It is not legal for the transaction_name parameter of a ROLLBACK TRANSACTION statement to refer to the inner transactions of a set of named nested transactions. transaction_name can refer only to the transaction name of the outermost transaction.
    If a ROLLBACK TRANSACTION transaction_name statement using the name of the outer transaction is executed at any level of a set of nested transactions, all the nested transactions are rolled back. If a ROLLBACK WORK or ROLLBACK TRANSACTION statement without a transaction_name parameter is executed at any level of a set of nested transaction, it rolls back all the nested transactions, including the outermost transaction.
  6. Madhivanan Moderator

    In this case, use savepoint transaction and rollback transaction

Share This Page