Nested stored procedures containing the TRY CATCH ROLLBACK pattern?

Question

I am interested in the side effects and potential problems of the following pattern:

CREATE PROCEDURE [Name] AS BEGIN BEGIN TRANSACTION BEGIN TRY [...Perform work, call nested procedures...] END TRY BEGIN CATCH ROLLBACK TRANSACTION RAISERROR [rethrow caught error using @ErrorNumber, @ErrorMessage, etc] END CATCH END 

As far as I understand, this template sounds when used with one procedure - the procedure will either execute all its instructions without errors, or roll back all the actions and report an error.

However, when one stored procedure calls another stored procedure to do some part of the work (on the understanding that the smaller procedure is sometimes called by itself), I see a problem associated with rollbacks - an informational message (Level 16) is issued with the message The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION. . This I assume, because rollback in a subroutine always rolls back an external transaction, not just a transaction running in a subroutine.

I want everything to be thrown back and aborted if any error occurs (and the error message was reported to the client as an SQL error), I'm just not sure about all the side effects that arise from the outer layers trying to roll back the transaction, which already retractable. Perhaps checking @@TRANCOUNT before rolling back at each TRY CATCH level?

Finally, there is a client end (Linq2SQL) that has its own transaction level:

 try { var context = new MyDataContext(); using (var transaction = new TransactionScope()) { // Some Linq stuff context.SubmitChanges(); context.MyStoredProcedure(); transactionComplete(); } } catch { // An error occured! } 

In case the stored procedure, "MySubProcedure", called inside MyStoredProcedure, causes an error, can I be sure that everything that was done earlier in MyStoredProcedure will be canceled, all Linq operations made by SubmitChanges will be discarded and finally what error will be logged? Or what I need to change in my template to ensure that the entire operation is atomic, while still allowing the use of child parts separately (i.e., Subprocedures should have the same atomic protection).

Answer 1

This is our template (error removal)

It is meant to be processed.

• Paul Randal's article, “There Is No Such Thing As a Nested Transaction in SQL Server”
• Error 266
• Trigger Rollback

Explanations:

• all TXN start and commit / rollbacks must be paired so that @@TRANCOUNT will be the same when entering and exiting

• @@TRANCOUNT cause error 266 because

  • BEGIN TRAN increases @@TRANCOUNT

  • COMMIT decrements @@TRANCOUNT

  • ROLLBACK returns @@TRANCOUNT to zero

• You cannot decrease @@TRANCOUNT for the current area
  This is what you consider an “internal transaction”

• SET XACT_ABORT ON suppresses error 266 caused by @@TRANCOUNT
  It also addresses issues such as SQL Server Transaction Timeout on dba.se

• This allows the use of TXN on the client side (e.g. LINQ). One stored procedure can be part of a distributed or XA transaction, or simply initiated in client code (e.g. .net TransactionScope)

Application:

• Each stored proc must match the same pattern.

Summary

• Therefore, do not create more TXNs than you need.

The code

 CREATE PROCEDURE [Name] AS SET XACT_ABORT, NOCOUNT ON DECLARE @starttrancount int BEGIN TRY SELECT @starttrancount = @@TRANCOUNT IF @starttrancount = 0 BEGIN TRANSACTION [...Perform work, call nested procedures...] IF @starttrancount = 0 COMMIT TRANSACTION END TRY BEGIN CATCH IF XACT_STATE() <> 0 AND @starttrancount = 0 ROLLBACK TRANSACTION; THROW; --before SQL Server 2012 use --RAISERROR [rethrow caught error using @ErrorNumber, @ErrorMessage, etc] END CATCH GO 

Notes:

• The rollback check is actually redundant due to SET XACT_ABORT ON . However, it makes me feel better, looks weird without, and allows situations in which you don't want it.

• Remus Rusanu has a similar shell that uses savepoints. I prefer atomic DB calls and don't use partial updates like their article

Answer 2

I'm not a Linq guy (and not one of them is Erland), but he wrote absolute bibles for error handling. Beyond complications, Linq can add to your problem; all your other questions should be answered here:

http://www.sommarskog.se/error_handling/Part1.html

(Old link: http://www.sommarskog.se/error_handling_2005.html )

Answer 3

To solve the issue of returning the error number and line number mentioned by @AlexKuznetsov, you can raise the error as such:

 DECLARE @ErrorMessage NVARCHAR(4000) DECLARE @ErrorSeverity INT DECLARE @ErrorState INT DECLARE @ErrorLine INT DECLARE @ErrorNumber INT SELECT @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE(), @ErrorNumber = ERROR_NUMBER(), @ErrorLine = ERROR_LINE() RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState, @ErrorNumber, @ErrorLine) 

Answer 4

- the @Amanda method above does not return the correct error number

 DECLARE @ErrorMessage nvarchar(4000), @ErrorSeverity int, @ErrorState int, @ErrorLine int, @ErrorNumber int BEGIN TRY SELECT 1/0; -- CATCH me END TRY BEGIN CATCH DECLARE @err int = @@ERROR PRINT @err -- 8134, divide by zero PRINT ERROR_NUMBER() -- 8134 SELECT @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE(), @ErrorNumber = ERROR_NUMBER(), @ErrorLine = ERROR_LINE() -- error number = 50000 :( RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState, @ErrorNumber, @ErrorLine) END CATCH -- error number = 8134 SELECT 1/0 

Answer 5

In the absence of special error handling in CATCH, except for repeated and saved procs call chains, not too long, it may be advisable to use such a simple template:

 create procedure someNestedSP as SET XACT_ABORT ON begin transaction -- do some work or call some other similar SP commit transaction 

It will also roll back the root transaction with all the "nested" ones in case of any error, but the code is shorter and simpler than the @gbn solution. However, XACT_ABORT takes care of most of the issues mentioned there.

It’s possible that overhead may occur when transactions are imposed, but perhaps they are not too high.