SQL Server Performance

Nested Transaction in Stored Procedure

Discussion in 'T-SQL Performance Tuning for Developers' started by essamughal, Feb 14, 2005.

  1. essamughal New Member

    Hi ;

    I am implementing Transaction to one of the big stored procedure which calls so many other nested stored procedures and so on. There is 4 level nesting, means nesting stored procedure also calls another nesting stored procedure and that stored procedure calls another.

    It is look like that:

    Calling SP
    Nested SP
    Nested SP
    Nested SP

    Now, I want to implement Begin Transaction, Commit Transaction and Rollback Transaction to that stored procedure.

    My Question:

    1- What would be the best way to implement Transaction on that?
    2- Few Nested Stored Procedure can be called separately as well, so they should have there own Begin Trans/Commit Trans/Rollback Tran
    3- Should I have to use Begin Tran/Commit Tran/Rollback Tran in each nested stored procedure as well?

    I know about Rollback, it would set @@Transcount to 0 and rollback all the transaction before that point where it is called, but it could handled with Save Points.

    Thanks.



    Essa, M. Mughal
    Software Engineer
  2. satya Moderator

    As per the basic rule:
    Committing inner transactions is ignored by SQL. 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.
    The @@TRANCOUNT function records the current transaction nesting level. Each BEGIN TRANSACTION statement increments @@TRANCOUNT by one. Each COMMIT TRANSACTION or COMMIT WORK statement decrements @@TRANCOUNT by one.

    And refer to this articlehttp://www.codeproject.com/database/sqlservertransactions.asp for more information nested transactions.

    HTH

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  3. essamughal New Member

    Thanks Satya, your comments are pretty clear to me and I have been read that article and it is good article to understand Nested Transaction.

    Thanks.

    Essa, M. Mughal
    Software Engineer
  4. benwilson New Member

    Hey guys,
    I dont think it works quite the same in nested stored procedures- from BOL:

    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 trigger completes than the @@TRANCOUNT value when the stored procedure was called produces an informational message. This message does not affect subsequent processing.

    I am working through this at the moment, and it is a lot messier! One book i am looking at suggests using an IF statement at the begining to check if a transaction is open already. If it is, then set a savepoint and rollback to this savepoint if there is an error. You would then also return something to the calling procedure to let it know that the error occurred. If there is no transaction currently open, you then declare and commit as per usual.

    Ben
  5. satya Moderator

    You can use savepoints in rolling back portions of transactions to predefined locations. Keep in mind that SQL updates and rollbacks generally are expensive operations. So savepoints are useful only in situations where errors are unlikely and checking the validity of an update beforehand is relatively costly.


    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

Share This Page