locked
What's the point of having Nested Transaction if they cannot be committed independently RRS feed

  • Question

  • This page tells me that a rollback on Outer transaction rolls back the inner transaction even if the Inner transaction was committed.

    So, what's the whole point of providing Inner transactions in the first place and the ability to commit inner transactions. I understand that using Inner transactions, you can selectively rollback a portion of work of a large outer transaction, but other than this, do Nested transactions provide any other advantage??

    Also, it it possible to execute a DML operation in a transaction that does not gets rolled-back by the Outer transaction (short of doing it in a new connection using CLR integration or loopback linked servers in 2008)?? I mean is it possible to do it within the same connection.


    I always think tomorrow will have more time than today. And every today seems to pass-by faster than yesterday.
    Rahul Singla | http://www.rahulsingla.com
    Monday, May 23, 2011 9:23 PM

Answers

  • There isn't much use to nested transactions in SQL Server.  If you are not in a transaction, then doing a BEGIN TRAN seets the global variable @@TRANCOUNT to 1 and starts a transaction.  (@@TRANCOUNT is 0 if you are not in a transaction).  If you are already in a transaction, then doing a BEGIN TRAN does nothing except add 1 to the value of @@TRANCOUNT. 

    If you do a COMMIT and @@TRANCOUNT = 1 before the COMMIT, then all the work done in that outer transaction is committed.  If you do a COMMIT and @@TRANCOUNT > 1 (that is, you are in a nested transaction, nothing happens except that @@TRANCOUNT is decreased by 1.

    If you do a ROLLBACK (except for a rollback to a savepoint, see below), all of the changes made to permanent or temporary since the outermost BEGIN TRAN is rolled back and @@TRANCOUNT is set to 0.  (Note that table variables are NOT rolled back by a ROLLBACK, like any other variable, ROLLBACK does not affect them.

    Note that you cannot roll back a nested transaction without rolling back the outer transaction.  However, you can do a SAVE TRANSACTION and then later rollback to that SAVE TRANSACTION which will rollback to the save point - but that is done with SAVE TRANSACTION, not nested BEGIN TRAN's.  For more info, see SAVE TRAN in BOL.

    No, you cannot do DML to temporary or permanent tables in a transaction that will not be rolled back if the outer transaction is rolled back.  As noted above, you can do INSERTs/DELETEs/UPDATEs to table variables and those changes will not be affected by a ROLLBACK.

    Tom

    • Proposed as answer by Craig BrydenMVP Monday, May 23, 2011 10:00 PM
    • Marked as answer by Kalman Toth Saturday, May 28, 2011 8:09 AM
    Monday, May 23, 2011 9:52 PM

All replies

  • One of the uses of nested transactions is nested stored procedures. You may want to create a stored procedure in which multiple DML statements should be in the same transaction. Within the stored procedure you would encase the statements in a BEGIN TRAN / COMMIT pair. The stored procedure can then be executed on its own, maintaining integrity. However, you may also want to call that stored procedure from within another transaction. The outer transaction can either COMMIT or ROLLBACK as it seems fit, and again maintain integrity.

    As for having code not ROLLBACK, one way is with table variables. Like other variables, table variables are not scoped in the transaction, so if you populate a table variable within a transaction, then do a ROLLBACK, the temp table retains its data and can be manipulated further.

    HTH


    Vern Rabe
    Monday, May 23, 2011 9:34 PM
  • There isn't much use to nested transactions in SQL Server.  If you are not in a transaction, then doing a BEGIN TRAN seets the global variable @@TRANCOUNT to 1 and starts a transaction.  (@@TRANCOUNT is 0 if you are not in a transaction).  If you are already in a transaction, then doing a BEGIN TRAN does nothing except add 1 to the value of @@TRANCOUNT. 

    If you do a COMMIT and @@TRANCOUNT = 1 before the COMMIT, then all the work done in that outer transaction is committed.  If you do a COMMIT and @@TRANCOUNT > 1 (that is, you are in a nested transaction, nothing happens except that @@TRANCOUNT is decreased by 1.

    If you do a ROLLBACK (except for a rollback to a savepoint, see below), all of the changes made to permanent or temporary since the outermost BEGIN TRAN is rolled back and @@TRANCOUNT is set to 0.  (Note that table variables are NOT rolled back by a ROLLBACK, like any other variable, ROLLBACK does not affect them.

    Note that you cannot roll back a nested transaction without rolling back the outer transaction.  However, you can do a SAVE TRANSACTION and then later rollback to that SAVE TRANSACTION which will rollback to the save point - but that is done with SAVE TRANSACTION, not nested BEGIN TRAN's.  For more info, see SAVE TRAN in BOL.

    No, you cannot do DML to temporary or permanent tables in a transaction that will not be rolled back if the outer transaction is rolled back.  As noted above, you can do INSERTs/DELETEs/UPDATEs to table variables and those changes will not be affected by a ROLLBACK.

    Tom

    • Proposed as answer by Craig BrydenMVP Monday, May 23, 2011 10:00 PM
    • Marked as answer by Kalman Toth Saturday, May 28, 2011 8:09 AM
    Monday, May 23, 2011 9:52 PM