SQL Server Performance Forum – Threads Archive

Nested Transaction in Stored Procedure

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

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.

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

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

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.

]]>