Nested stored procedures containing TRY CATCH ROLLBACK pattern?

Question

I'm 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

To the best of my understanding this pattern is sound when used with a single procedure - the procedure will either complete all of its statements without error, or it will rollback all actions and report the error.

However when one stored procedure calls another stored procedure to do some sub-unit of work (with the understanding that the smaller procedure is sometimes called on its own) I see an issue coming about with relation to rollbacks - an informational message (Level 16) is issued stating The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.. This I assume is because the rollback in the sub-procedure is always rolling back the outer-most transaction, not just the transaction started in the sub-procedure.

I do want the whole thing rolled back and aborted if any error occurs (and the error reported to the client as an SQL error), I'm just not sure of all the side effects that come from the outer layers trying to rollback a transaction that has already been rolled back. Perhaps a check of @@TRANCOUNT before doing a rollback at each TRY CATCH layer?

Finally there is the client end (Linq2SQL), which has it's own transaction layer:

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

In the event that a stored procedure, "MySubProcedure", called inside MyStoredProcedure raises an error, can I be sure that everything previously done in MyStoredProcedure will be rolled back, all the Linq operations made by SubmitChanges will be rolled back, and finally that the error will be logged? Or what do I need to change in my pattern to ensure the whole operation is atomic, while still allowing the child parts to be used individually (i.e. the sub-procedures should still have the same atomic protection)

Answer 1

I am not a Linq guy (and neither is Erland), but he wrote the absolute bibles on error handling. Outside of the complications Linq might add to your problem, all of your other questions should be answered here:

http://www.sommarskog.se/error_handling_2005.html

Answer 2

This is our template (error logging removed)

Notes:

• Without XACT_ABORT, all TXN begin and commit/rollbacks must be paired
• A commit decrements @@TRANCOUNT
• A rollback returns @@TRANCOUNT to zero so you'd get error 266
• You can not ROLLBACK the current layer only (eg decrement @@TRANCOUNT on rollback)
• XACT_ABORT suppresses error 266
• Each stored proc must conform to the same template so each call is atomic
• The rollback check is actually redundant because of XACT_ABORT. However, it makes me feel better, looks odd without, and allows for situations where you don't want it on
• This allows for client side TXNs (like LINQ)
• Remus Rusanu has a similar shell that uses save points. I prefer an atomic DB call and don't use partial updates like his/her article

...so don't create more TXNs than you need

However,

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
    RAISERROR [rethrow caught error using @ErrorNumber, @ErrorMessage, etc]
END CATCH
GO