SQL Server Performance

Nested Transaction and Savepoint

Discussion in 'SQL Server 2005 General Developer Questions' started by anandchatterjee, May 25, 2008.

  1. anandchatterjee New Member

    Another silly question!I can define a savepoint inside a transaction and rollback it through the name and also I can do the same through nested transaction! Then what is the basic difference between savepoint and nested transaction? Could you please show me a example?
  2. satya Moderator

    Without further confusion, giving you what has been defined in BOL:
    ROLLBACK TRANSACTION erases all data modifications made from the start of the transaction or to a savepoint. It also frees resources held by the transaction.
    ROLLBACK TRANSACTION without a savepoint_name or transaction_name rolls back to the beginning of the transaction. When nesting transactions, this same statement rolls back all inner transactions to the outermost BEGIN TRANSACTION statement. In both cases, ROLLBACK TRANSACTION decrements the @@TRANCOUNT system function to 0. ROLLBACK TRANSACTION savepoint_name does not decrement @@TRANCOUNT.
    A ROLLBACK TRANSACTION statement specifying a savepoint_name releases any locks acquired beyond the savepoint, with the exception of escalations and conversions. These locks are not released, and they are not converted back to their previous lock mode.
    ROLLBACK TRANSACTION cannot reference a savepoint_name in distributed transactions started either explicitly with BEGIN DISTRIBUTED TRANSACTION or escalated from a local transaction.
    A transaction cannot be rolled back after a COMMIT TRANSACTION statement is executed.
    Within a transaction, duplicate savepoint names are allowed, but a ROLLBACK TRANSACTION using the duplicate savepoint name rolls back only to the most recent SAVE TRANSACTION using that savepoint name.
    In stored procedures, ROLLBACK TRANSACTION statements without a savepoint_name or transaction_name roll back all statements to the outermost BEGIN TRANSACTION. A ROLLBACK TRANSACTION statement in a stored procedure that causes @@TRANCOUNT to have a different value when the stored procedure completes than the @@TRANCOUNT value when the stored procedure was called produces an informational message. This message does not affect subsequent processing.
    If a ROLLBACK TRANSACTION is issued in a trigger:
    • All data modifications made to that point in the current transaction are rolled back, including any made by the trigger.

      • The trigger continues executing any remaining statements after the ROLLBACK statement. If any of these statements modify data, the modifications are not rolled back. No nested triggers are fired by the execution of these remaining statements.

        • The statements in the batch after the statement that fired the trigger are not executed.

      • @@TRANCOUNT is incremented by one when entering a trigger, even when in autocommit mode. (The system treats a trigger as an implied nested transaction.)
        ROLLBACK TRANSACTION statements in stored procedures do not affect subsequent statements in the batch that called the procedure; subsequent statements in the batch are executed. ROLLBACK TRANSACTION statements in triggers terminate the batch containing the statement that fired the trigger; subsequent statements in the batch are not executed.
        A ROLLBACK TRANSACTION statement does not produce any messages to the user. If warnings are needed in stored procedures or triggers, use the RAISERROR or PRINT statements. RAISERROR is the preferred statement for indicating errors.
        The effect of a ROLLBACK on cursors is defined by these three rules:
        1. With CURSOR_CLOSE_ON_COMMIT set ON, ROLLBACK closes, but does not deallocate all open cursors.

          • With CURSOR_CLOSE_ON_COMMIT set OFF, ROLLBACK does not affect any open synchronous STATIC or INSENSITIVE cursors or asynchronous STATIC cursors that have been fully populated. Open cursors of any other type are closed but not deallocated.

            • An error that terminates a batch and generates an internal rollback deallocates all cursors that were declared in the batch containing the error statement. All cursors are deallocated regardless of their type or the setting of CURSOR_CLOSE_ON_COMMIT. This includes cursors declared in stored procedures called by the error batch. Cursors declared in a batch before the error batch are subject to rules 1 and 2. A deadlock error is an example of this type of error. A ROLLBACK statement issued in a trigger also automatically generates this type of error.
          • ____________________________________
            Nested transactions are 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. And what BOL refers again on Nested Trnsaction that:
            Committing inner transactions is ignored by the SQL Server Database Engine. The transaction is either committed or rolled back based on the action taken at the end of the outermost transaction. If the outer transaction is committed, the inner nested transactions are also committed. If the outer transaction is rolled back, then all inner transactions are also rolled back, regardless of whether or not the inner transactions were individually committed.
            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. anoopsihag New Member

    You can not Rollback the transaction to savepoint when you have doomed transaction which is part of SQL Server 2005 and later version
    You will have error message :
    The current transaction cannot be committed and cannot be rolled back to a savepoint. Roll back the entire transaction.
    Any idea?

Share This Page