Template for Managing Microsoft T-SQL Nested Transactions

Introduction

The code below has been tested using Microsoft T-SQL 2008R2.  It avoid the problem of transactions:

Msg 266, Level 16, State 2, Procedure SkillsTestUpdate, Line 0
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. 
Previous count = 0, current count = 1.

Msg 266, Level 16, State 2, Line 0
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. 
Previous count = 0, current count = 1.

Background

My client started with the design of a database that was not intended for online transaction procession (OLTP) only to find that it has evolved into one. As such, we needed a way to alter existing stored procedures that alter data. Any procedure could be executed on its own, or it could be called n-levels deep from other procedures that also alter date.

Using the Code

The code uses the @@TRANCOUNT to determine if a transaction has been started. The existing logic was placed in the middle of the template.

//
// 

--  USE YourDatabaseName;

IF NOT EXISTS (SELECT 'Common'
    FROM information_schema.schemata 
    WHERE schema_name = 'Common' )
BEGIN
    EXEC sp_executesql N'CREATE SCHEMA Common;';
END

IF OBJECT_ID('[Common].[SetConfiguration]') IS NOT NULL -- Check if SP Exists
 DROP PROCEDURE [Common].[SetConfiguration]
GO
CREATE PROCEDURE [Common].[SetConfiguration] AS BEGIN SET LOCK_TIMEOUT 500; END  -- 1,000 Milliseconds = 1 second
go

--  This is the template for all stored procedures that modify data (UPDATE/DELETE/INSERT):

CREATE PROCEDURE <INSERT Procedure Name> 
AS
/*
  3/2015  bje  Added the transaction template.
*/
EXEC [Common].[SetConfiguration];
SET NOCOUNT ON;
DECLARE @TranStarted BIT = 0;
BEGIN TRY

    IF (@@TRANCOUNT = 0)        -- if no 'BEGIN TRAN" is in effect, start one
    BEGIN
        BEGIN TRANSACTION;
        SET @TranStarted = 1;    --  This is the SP that began the TRANsaction, 
    END;

    /*  add code that will modify db here */


    -- At END of code, if no error & if this code started the transaction, do commit 
    IF (@TranStarted = 1)        -- this is the "outermost" PROCEDURE
    BEGIN
        COMMIT TRANSACTION;
    END;
END TRY

BEGIN CATCH

DECLARE @ErrorNumber    INT, 
        @ErrorMessage   nVARCHAR(4000), 
        @ErrorSeverity  INT, 
        @ErrorState     INT,
        @ErrorProc      nVARCHAR(4000) = ISNULL(ERROR_PROCEDURE(),OBJECT_NAME(@@PROCID));

    SELECT    @ErrorNumber    = ISNULL(ERROR_NUMBER(), 50000),
            @ErrorMessage    = ISNULL(ERROR_MESSAGE(), 'Error has been generated in ' + @ErrorProc), 
            @ErrorSeverity    = ISNULL(ERROR_SEVERITY(), 16), 
            @ErrorState        = ISNULL(ERROR_STATE(), 1);

    IF ((@@TRANCOUNT > 0) AND (@TranStarted > 0))
    BEGIN
        ROLLBACK TRANSACTION;
    END

    EXEC [Common].[LogErrorMessage]
        @ErrorNumber    = @ErrorNumber,
        @ErrorMessage    = @ErrorMessage,
        @ErrorSeverity    = @ErrorSeverity,
        @ErrorState        = @ErrorState,
        @ErrorProc        = @ErrorProc;

    IF ((@ErrorNumber < 13000) OR (@ErrorNumber = 50000))
        RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState) WITH LOG;
    ELSE
        RAISERROR(@ErrorNumber, @ErrorMessage, @ErrorSeverity, @ErrorState) WITH SETERROR, LOG;

END CATCH
go

If there is a problem with deadlocks, you can use:

IF OBJECT_ID('[Common].[MaxRetries]') IS NULL -- Check if SP Exists
 EXEC('CREATE FUNCTION [Common].[MaxRetries]( )  RETURNS int AS BEGIN RETURN(3); END') 
GO
ALTER FUNCTION [Common].[MaxRetries]( )  RETURNS int AS BEGIN RETURN(3); END
go

IF OBJECT_ID('[Common].[WaitForDelay]') IS NULL -- Check if SP Exists
 EXEC('CREATE PROCEDURE [Common].[WaitForDelay] AS BEGIN WAITFOR DELAY _
	''00:00:00.250''; END') -- 250 milliseconds = 1/4 second
GO
ALTER PROCEDURE [Common].[WaitForDelay] AS BEGIN WAITFOR DELAY '00:00:00.250'; END
GO


IF OBJECT_ID('[Common].[SetConfiguration]') IS NOT NULL -- Check if SP Exists
 DROP PROCEDURE [Common].[SetConfiguration]
GO
CREATE PROCEDURE [Common].[SetConfiguration] AS BEGIN SET LOCK_TIMEOUT 500; _
END  -- 1,000 Milliseconds = 1 second
go

IF OBJECT_ID('[Security].[UserUpdateOnAuthentication]') IS NULL -- Check if SP Exists
 EXEC('CREATE PROCEDURE [Security].[UserUpdateOnAuthentication] _
	AS SET NOCOUNT ON;') -- Create dummy/empty SP
GO

------------------------------

CREATE PROCEDURE <Insert procedure name>
    DECLARE @TranStarted BIT = 0;
    DECLARE @Error INT = 0;
    DECLARE @Retries INT = 0;
    DECLARE @WaitForDelay int = 250;
    DECLARE @MaxRetries int = [Common].[MaxRetries]( );

EXEC [Common].[SetConfiguration];
WHILE (@Retries < @MaxRetries)
BEGIN
BEGIN TRY
IF (@@TRANCOUNT = 0)            -- IF no 'BEGIN TRAN" is in effect, start one
    BEGIN
        BEGIN TRAN;
        SAVE TRAN TRAN_UserUpdateOnLogin;
        SET @TranStarted = 1;        -- This is the SP that began the TRANsaction, 
    END

< insert sp code here>

    IF (@TranStarted = 1)        -- this is the "outermost" PROCEDURE
        COMMIT TRAN TRAN_UserUpdateOnLogin;
     BREAK;
END TRY
BEGIN CATCH
    SET @Error = ERROR_NUMBER( );
    IF (@TranStarted = 1)
    BEGIN
        ROLLBACK TRAN TRAN_UserUpdateOnLogin;
    END

    IF ((@Error = 1205) or (@Error = 1222))
    BEGIN
        SET @Retries = @Retries + 1;
        EXEC XP_LOGEVENT 9999, '<Insert Procedure Name>: DEADLOCK', WARNING;
        IF (@Retries < @MaxRetries)
        BEGIN
            EXEC [Common].[WaitForDelay];    -- 500 milliseconds = 0.5 seconds
            CONTINUE;    --    branches to the outhermost WHILE loop to try again
        END
    END

    DECLARE @Error_No       INT, 
            @Error_Message  nVARCHAR(4000), 
            @Error_Severity INT, 
            @Error_State    INT ;

    SELECT    @Error_No        = ISNULL(ERROR_NUMBER(), 50000),
            @Error_Message    = ISNULL(ERROR_MESSAGE(), 'Error has been generated.'), 
            @Error_Severity = ISNULL(ERROR_SEVERITY(), 16), 
            @Error_State    = ISNULL(ERROR_STATE(), 1) ;

    IF ((@@TRANCOUNT > 0) AND (@TranStarted > 0)) BEGIN ROLLBACK; END
    RAISERROR(@Error_Message, @Error_Severity, @Error_State) WITH LOG;                       
    BREAK;
    --THROW ;
END CATCH
END    -- END WHILE LOOP
go

Hope this helps.