Tag Archives: StoredProcedure

Free download: Blog Yearbook 2016

I have made a compilation of all my blog posts of the past year, and it was more work than you would think.
Especially because it also contains a non-published new article.

After filling in the form you can download the e-book for free.

    I promise you the following:

    • I will not give or sell your email address to any third party.
    • I will not spam you.
    • If I start with a newsletter, you can easily unsubscribe or change the frequency.

    (c) 2016 hansmichiels.com – Do not steal the contents – spread the link instead – thank you.

    How to protect your stored procedures against multiple concurrent executions (SQL Server Series)

    Last update: 5 October, 2016: added examples of using sp_getapplock and sp_releaseapplock.

    Introduction

    I ran into this problem lately. I had a stored procedure that did some global stuff for a database, it would do the exact same thing every time you executed it. It had no parameters.
    It would at the best case be useless and use unnecessary server resources when executed multiple times concurrently, and in the worst case would cause deadlocks in that situation.
    And this is exactly what this post is about: a stored procedure that is not supposed to be executed multiple times concurrently.
    Examples are stored procedures that are purging a database log, updating derived tables or columns, etc.

    Problem

    You can start a stored procedure, and before it is finished, can start it again from a different connection.

    Solutions

    There are (at least) two possible solutions for this problem.

    1. Use sp_getapplock and sp_releaseapplock (the preferred solution). My credits go to Andy Novick who has published about this before on MSSQLTips. I have added examples of using ‘Session’ as lock owner, and therefore I hope that my article will still have added value.
    2. My initial solution using dynamic management views is still in this article for reference.

    Using sp_getapplock and sp_releaseapplock

    How it works

    For your convenience the links to MSDN for help on those two system stored procedures:
    MSDN page about sp_getapplock
    MSDN page about sp_releaseapplock

    Important to understand is that as @LockOwner you can either use a transaction or a session.
    I have included two examples for both types of lockowner.

    Using ‘Transaction’ as lock owner

    In this stored procedure example (of which you can download the script) sp_getapplock is used with @LockOwner = ‘Transaction’.
    When you use this example, it is really important to use the TRY .. CATCH code so that the lock is always released before the stored procedure execution ends.

    sp_StoredProcUsingAppLock.sql

    -------------- [sp].[StoredProcUsingAppLock] --------------
    IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[sp].[StoredProcUsingAppLock]') AND type in (N'P', N'PC'))
      DROP PROCEDURE [sp].[StoredProcUsingAppLock]
    GO
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    /*
    ==========================================================================================
    Author: Hans Michiels
    Create date: 5-oct-2016
    Description: Example of stored procedure that is using sp_getapplock by using a transaction.
    ==========================================================================================
    */

    CREATE PROCEDURE [sp].[StoredProcUsingAppLock]
    (
      @RaiseError BIT = 0
    )
    AS
    BEGIN
      SET NOCOUNT ON;
      DECLARE @RC INT
      DECLARE @message VARCHAR(500)

      BEGIN TRY

        --\
        ---) Protection Against Concurrent Executions
        --/
        BEGIN TRAN

        SELECT @message = CONVERT(VARCHAR(30), GETDATE(), 121) + ': Try to obtain a lock ..'
        RAISERROR(@message, 0, 1) WITH NOWAIT;

        EXEC @RC = sp_getapplock
            @Resource = 'StoredProcUsingAppLock',
            @LockMode = 'Exclusive',
            @LockOwner = 'Transaction',
            @LockTimeout = 60000 -- 1 minute

        IF @RC < 0
        BEGIN
            IF @@TRANCOUNT > 0 ROLLBACK TRAN;
            SELECT @message = CONVERT(VARCHAR(30), GETDATE(), 121) + ': Sorry, could not obtain a lock within the timeout period, return code was ' + CONVERT(VARCHAR(30), @RC) + '.'
            RAISERROR(@message, 0, 1) WITH NOWAIT;
            RETURN @RC
        END ELSE BEGIN
            SELECT @message = CONVERT(VARCHAR(30), GETDATE(), 121) + ': AppLock obtained ..'
            RAISERROR(@message, 0, 1) WITH NOWAIT;
        END
      
        --\
        ---) Stored procedure body
        --/
        -- Wait so that stored procedure has some considerable execution time.
        SELECT @message = CONVERT(VARCHAR(30), GETDATE(), 121) + ': Hello World!'
        RAISERROR(@message, 0, 1) WITH NOWAIT;

        DECLARE @i INT = 0

        WHILE @i < 6
        BEGIN
            WAITFOR DELAY '00:00:01';
            EXEC [sp].[SubStoredProc];
            SET @i = @i + 1
        END

        IF @RaiseError = 1
        BEGIN
            RAISERROR('An error on demand', 16, 1);
        END

        SELECT @message = CONVERT(VARCHAR(30), GETDATE(), 121) + ': Goodbye ..'
        RAISERROR(@message, 0, 1) WITH NOWAIT;

        COMMIT TRAN
        SELECT @message = CONVERT(VARCHAR(30), GETDATE(), 121) + ': Transaction committed, appLock released ..'
        RAISERROR(@message, 0, 1) WITH NOWAIT;

      END TRY
      BEGIN CATCH
        IF @@TRANCOUNT > 0 ROLLBACK TRAN;
        SELECT @message = CONVERT(VARCHAR(30), GETDATE(), 121) + ': Transaction rolled back, appLock released ..'
        RAISERROR(@message, 0, 1) WITH NOWAIT;
        THROW;
      END CATCH
    END
    GO

    When executed ..

    I would not be me if I did not include a test and a proof that it works.

    As you can see in the printscreens below, all executions as nicely after each other ..
    mssql-e05-354
    mssql-e05-356
    mssql-e05-355
    mssql-e05-357

    As you can see in the printscreens below, this even works well, when an error occurs, due to the TRY .. CATCH block ..
    mssql-e05-361
    mssql-e05-358
    mssql-e05-360
    mssql-e05-359

    Using ‘Session’ as lock owner

    In this stored procedure example (of which you can download the script) sp_getapplock is used with @LockOwner = ‘Session’.
    An “advantage” of using the session might be that you can also use it, when you do not want to use a transaction within your stored procedure.
    As with the previous example, now also it is really important to use the TRY .. CATCH code so that the lock is always released before the stored procedure execution ends.

    demo_setup_sp_StoredProcUsingAppLockNoTran.sql
    -------------- [sp].[StoredProcUsingAppLockNoTran] --------------
    IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[sp].[StoredProcUsingAppLockNoTran]') AND type in (N'P', N'PC'))
      DROP PROCEDURE [sp].[StoredProcUsingAppLockNoTran]
    GO
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    /*
    ==========================================================================================
    Author: Hans Michiels
    Create date: 5-oct-2016
    Description: Example of stored procedure that is using sp_getapplock without using a transaction.
    ==========================================================================================
    */

    CREATE PROCEDURE [sp].[StoredProcUsingAppLockNoTran]
    (
      @RaiseError BIT = 0
    )
    AS
    BEGIN
      SET NOCOUNT ON;
      DECLARE @RC INT
      DECLARE @message VARCHAR(500)

      BEGIN TRY

        --\
        ---) Protection Against Concurrent Executions
        --/
        SELECT @message = CONVERT(VARCHAR(30), GETDATE(), 121) + ': Try to obtain a lock ..'
        RAISERROR(@message, 0, 1) WITH NOWAIT;

        EXEC @RC = sp_getapplock
            @Resource = 'StoredProcUsingAppLockNoTran',
            @LockMode = 'Exclusive',
            @LockOwner = 'Session',
            @LockTimeout = 60000 -- 1 minute

        IF @RC < 0
        BEGIN
            SELECT @message = CONVERT(VARCHAR(30), GETDATE(), 121) + ': Sorry, could not obtain a lock within the timeout period, return code was ' + CONVERT(VARCHAR(30), @RC) + '.'
            RAISERROR(@message, 0, 1) WITH NOWAIT;
            RETURN @RC
        END ELSE BEGIN
            SELECT @message = CONVERT(VARCHAR(30), GETDATE(), 121) + ': AppLock obtained ..'
            RAISERROR(@message, 0, 1) WITH NOWAIT;
        END
      
        --\
        ---) Stored procedure body
        --/
        -- Wait so that stored procedure has some considerable execution time.
        SELECT @message = CONVERT(VARCHAR(30), GETDATE(), 121) + ': Hello World!'
        RAISERROR(@message, 0, 1) WITH NOWAIT;

        DECLARE @i INT = 0

        WHILE @i < 6
        BEGIN
            WAITFOR DELAY '00:00:01';
            EXEC [sp].[SubStoredProc];
            SET @i = @i + 1
        END

        IF @RaiseError = 1
        BEGIN
            RAISERROR('An error on demand', 16, 1);
        END

        SELECT @message = CONVERT(VARCHAR(30), GETDATE(), 121) + ': Goodbye ..'
        RAISERROR(@message, 0, 1) WITH NOWAIT;

        EXEC @RC = sp_releaseapplock @Resource='StoredProcUsingAppLockNoTran', @LockOwner='Session';
        SELECT @message = CONVERT(VARCHAR(30), GETDATE(), 121) + ': AppLock released ..'
        RAISERROR(@message, 0, 1) WITH NOWAIT;

      END TRY
      BEGIN CATCH
        EXEC @RC = sp_releaseapplock @Resource='StoredProcUsingAppLockNoTran', @LockOwner='Session';
        SELECT @message = CONVERT(VARCHAR(30), GETDATE(), 121) + ': AppLock released after error ..'
        RAISERROR(@message, 0, 1) WITH NOWAIT;
        THROW;
      END CATCH
    END
    GO

    When executed ..

    As you can see in the printscreens below, all executions as nicely after each other ..
    mssql-e05-363
    mssql-e05-362
    mssql-e05-365
    mssql-e05-364

    As you can see in the printscreens below, this even works well, when an error occurs, due to the TRY .. CATCH block ..
    mssql-e05-367
    mssql-e05-366
    mssql-e05-368
    mssql-e05-369

    Other considerations

    As you might have noticed, this only worked because the timeout was 60 seconds, and all the executions could be completed within those 60 seconds. If this would not be the case, the stored procedure execution that was waiting to obtain the applock would time out and would bail out due to a RETURN statement.
    So you might play a bit with the timeout time to influence this behavior.
    If you do not care that the non-first executions bail out, you could set the time out to a smaller value, e.g. 5000 (milliseconds).
    If you absolutely want all executions to continue after waiting, you could set the time out to a higher value, that is above the maximum execution time of the stored procedure.
    In this way you can choose to let non-first executions wait or not, as could be achieved by using the @WaitIfExecuted parameter in my initial solution.

    My initial solution using dynamic management views

    So if you want to create a stored procedure that is Protected Against Concurrent Executions, you have to do it yourself.
    Making a PACE stored procedure is not very difficult. There is however one big pitfall. The examples on the internet I found all have nasty bug in it, at least when your stored procedure executes other stored procedures: when this happens, the “main” stored procedure goes off the radar, when the dynamic management objects sys.dm_exec_requests and sys.dm_exec_sql_text() are used, that means, you cannot detect that it is running as long as the “sub” stored procedure is being executed.
    To make the challenge even bigger, the object_id of the “main” stored procedure is nowhere, so with the available dynamic management objects there is no way to solve this.
    Therefore an extra “user” dynamic management table is needed, I called it [udm_storedproc_executions]. It is automaticly cleaned up in the stored procedure [sp].[GetPace].
    This stored procedure is used to implement Protection Against Concurrent Executions.
    You execute it at the beginning in stored procedures that you want to protect against concurrent executions.

    How it works

    • You create the table [dbo].[udm_storedproc_executions] (or name it differently) in your database.
    • You create the stored procedure [sp].[GetPace] (or name it differently) in your database.
    • You add the code snippet below to stored procedures that are not supposed to run multiple times concurrently.

    Create the table

    udm_storedproc_executions.sql
    /*
    (c) Copyright 2016 - hansmichiels.com
     
    This program is free software: you can redistribute it and/or modify
    it under the terms of the GNU General Public License as published by
    the Free Software Foundation, either version 3 of the License, or
    (at your option) any later version.
     
    This program is distributed in the hope that it will be useful,
    but WITHOUT ANY WARRANTY; without even the implied warranty of
    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
    GNU General Public License for more details.
     
    You should have received a copy of the GNU General Public License
    along with this program. If not, see .
    */

    IF OBJECT_ID('[dbo].[udm_storedproc_executions]', 'U') IS NOT NULL
       DROP TABLE [dbo].[udm_storedproc_executions];
    GO
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_PADDING OFF
    GO

    CREATE TABLE [dbo].[udm_storedproc_executions](
     [start_time] [datetime] NOT NULL,
     [session_id] [smallint] NOT NULL,
     [connection_id] [uniqueidentifier] NOT NULL,
     [task_address] [varbinary](8) NOT NULL,
     [object_id] [int] NOT NULL,
      CONSTRAINT [PK_dbo_udm_storedproc_executions] PRIMARY KEY CLUSTERED
    ( [start_time] ASC,
      [session_id] ASC,
      [connection_id] ASC,
      [task_address] ASC
    ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    GO

    SET ANSI_PADDING OFF
    GO

    Create the stored procedure

    sp_GetPace.sql
    /*
    (c) Copyright 2016 - hansmichiels.com
     
    This program is free software: you can redistribute it and/or modify
    it under the terms of the GNU General Public License as published by
    the Free Software Foundation, either version 3 of the License, or
    (at your option) any later version.
     
    This program is distributed in the hope that it will be useful,
    but WITHOUT ANY WARRANTY; without even the implied warranty of
    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
    GNU General Public License for more details.
     
    You should have received a copy of the GNU General Public License
    along with this program. If not, see .
    */

    -------------- [sp].[GetPace] --------------
    IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[sp].[GetPace]') AND type in (N'P', N'PC'))
      DROP PROCEDURE [sp].[GetPace]
    GO
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    /*
    ==========================================================================================
    Author: Hans Michiels
    Create date: 17-sep-2016
    Description: Generic stored procedure that can give a different stored procedure
                 PACE (Protection Against Concurrent Execution).
    ==========================================================================================
    */

    CREATE PROCEDURE [sp].[GetPace]
    (
      @StoredProcedureName NVARCHAR(256), -- Quoted name including schema, e.g. [dbo].[SomeStoredProcedure]
      @WaitIfExecuted BIT = 0, -- Indication whether to wait when the stored procedure is already executing (and execute it afterwards).
      @PaceAction VARCHAR(8) OUTPUT, -- Domain values [ CONTINUE | WAIT | RETURN ]
      @InsertDmRow BIT = 0, -- Indication if a row must be inserted to table [dbo].[udm_storedproc_executions]
      @EnableDebugMessages BIT = 0 -- Speaks for itself
    )
    AS
    BEGIN
      SET NOCOUNT ON;

      --\
      ---) Declarations.
      --/
      DECLARE @SpidThatWins INT
      DECLARE @WaitTime CHAR(8) = '00:00:03'
      DECLARE @message NVARCHAR(500)

      --\
      ---) Insert new row into table [dbo].[udm_storedproc_executions].
      --/
      IF @InsertDmRow = 1
      BEGIN
          INSERT INTO [dbo].[udm_storedproc_executions]
            SELECT
                der.[start_time],
                der.[session_id],
                der.[connection_id],
                der.[task_address],
                OBJECT_ID(@StoredProcedureName, 'P') AS [object_id]
            FROM
                sys.dm_exec_requests der
            CROSS APPLY
                sys.dm_exec_sql_text(der.sql_handle) xst
            WHERE
                der.session_id = @@SPID
                AND der.[sql_handle] is not null
                AND xst.objectid = OBJECT_ID('[sp].[GetPace]', 'P');
      END

      -- We need to prevent that this stored procedure would be executed multiple times
      -- simultaneously.
      SELECT @SpidThatWins = -- The one that first started. If started at exact the same time, the lowest SPID wins.
        (
        SELECT TOP 1 der.[session_id]
        FROM
            sys.dm_exec_requests der
        JOIN
            dbo.udm_storedproc_executions spx
            ON spx.[session_id] = der.[session_id]
            AND spx.[start_time] = der.[start_time]
            AND spx.[connection_id] = der.[connection_id]
            AND spx.[task_address] = der.[task_address]
        WHERE
            der.[sql_handle] is not null
            AND spx.[object_id] = OBJECT_ID(@StoredProcedureName, 'P')
        ORDER BY
            der.[start_time] ASC,
            der.[session_id] ASC
        )

      SELECT @PaceAction =
      CASE
        WHEN @@SPID = @SpidThatWins THEN 'CONTINUE'
        WHEN @WaitIfExecuted = 0 THEN 'RETURN'
        WHEN @WaitIfExecuted = 1 THEN 'WAIT'
      END

      IF @PaceAction = 'WAIT'
      BEGIN
          -- Still executing, so wait until I can start.
          IF @EnableDebugMessages = 1
          BEGIN
              -- Still executing, so wait until I can start.
              SET @message = @StoredProcedureName + N' is waiting for other execution to finish .. ';
              RAISERROR(@message, 0, 1) WITH NOWAIT;
          END
          WAITFOR DELAY @WaitTime
      END
      IF @PaceAction = 'RETURN' AND @EnableDebugMessages = 1
      BEGIN
          -- Still executing, so wait until I can start.
          SET @message = N'Cancelling execution of stored proc ' + @StoredProcedureName;
          RAISERROR(@message, 0, 1) WITH NOWAIT;
      END

      --\
      ---) Cleanup old rows from [udm_storedproc_executions].
      --/
      DELETE FROM spx
        FROM
            [dbo].[udm_storedproc_executions] spx
        LEFT JOIN
            sys.dm_exec_requests der
            ON spx.[session_id] = der.[session_id]
            AND spx.[start_time] = der.[start_time]
            AND spx.[connection_id] = der.[connection_id]
            AND spx.[task_address] = der.[task_address]
        WHERE
            der.session_id IS NULL;
    END
    GO

    Add the code snippet

    code_snippet_sp_start.sql
    CREATE PROCEDURE [sp].[StoredProcGettingPace]
    AS
    BEGIN
      SET NOCOUNT ON;
      --\
      ---) PACE (Protection Against Concurrent Executions) code
      --/
      DECLARE @PaceAction VARCHAR(8), @InsertDmRow BIT = 1
      WHILE 1 = 1
      BEGIN
          EXECUTE [sp].[GetPace]
            @StoredProcedureName = '[sp].[StoredProcGettingPace]',
            @WaitIfExecuted = 1,
            @PaceAction = @PaceAction OUTPUT,
            @InsertDmRow = @InsertDmRow,
            @EnableDebugMessages = 1;

          IF @PaceAction = 'RETURN' RETURN 0;
          IF @PaceAction = 'CONTINUE' BREAK;
          SET @InsertDmRow = 0;
      END

      --\
      ---) Stored procedure body ..
      --/
    END
    GO

    Proof that it works

    For the demo I use the following stored procedures:

    • [sp].[SubStoredProc]
    • [sp].[StoredProcGettingPace]
    • [sp].[StoredProcGettingPaceNoWait]

    By the way, all the scripts can be downloaded here.

    [sp].[SubStoredProc] is being executed by both other stored procedures.

    [sp].[StoredProcGettingPace]

    First I will demonstrate the testresults with [sp].[StoredProcGettingPace].
    It is executed from 4 query windows at the same time. Debug messages show us what happened.

    mssql-e05-341The query that predicts the order in which the session_ids will be processed: the order is 60, 59, 57, 52.

    mssql-e05-342As expected session 60 is processed first.

    mssql-e05-343As expected session 59 is processed after 60. The “Hello world” time (the beginning of the actual stored procedure body) is after the “Goodbye” time of session 60.

    mssql-e05-344Then session 57. Again the “Hello world” time is after the “Goodbye” time of session 59.

    mssql-e05-345And finally session 52. It goes without saying that the “Hello world” time is after the “Goodbye” time of the previous session.

    [sp].[StoredProcGettingPaceNoWait]

    It is important to understand parameter @WaitIfExecuted of stored procedure [sp].[GetPace].
    In the previous example we used the value 1 (true), which means that every execution of a stored procedure protected with [sp].[GetPace] is eventually executed, but later.
    But if we use the value 0 (false) for @WaitIfExecuted, we actually cancel the execution of a stored procedure if it is already running.

    Here are the testresults of executing [sp].[StoredProcGettingPaceNoWait].
    Again it is executed from 4 query windows at the same time and debug messages show us what happened.

    mssql-e05-346As expected the stored procedure is executed only once. The other executions are cancelled (see below).

    mssql-e05-347

    mssql-e05-348

    mssql-e05-349

    Download all the scripts here.

    Conclusion / Wrap up

    In this blog post you could read about two different solutions that can prevent a stored procedure from being executed multiple times simultaneously.
    This can be convenient in certain situations, when the stored procedure does some “global” work in your database, and it would be unnecessary and potentially “dangerous” when it is executing multiple times concurrently.

    (c) 2016 hansmichiels.com – Do not steal the contents – spread the link instead – thank you.