• How to prevent a Stored Procedure from being executed twice at the same time

    Posted on April 9, 2013 by in C#, Database, Dotnet, SQL Server

    This can be done using SQL Server application locks (sp_getapplock and sp_releaseapplock). However you need permissions for the account under which the stored proc is executed. The same functionality can be achieved using GLOBAL TEMPORARY TABLES.

    uspInsertProduct (Stored Proc)

    In the stored proc, we first check if the global temporary table exists. If it exists it means that another instance of stored procedure is already running, so we simply throw an error and exit. If we don’t find a match, we create the temporary table and continue processing. Once we are done inserting the product record, we drop the global temp table. If for some reason, the sp fails we still want the stored proc is used by other instances of application or next time by the same application. So we enclose whole processing in the try catch block. In the catch block, we check if the global temp table is created by current instance of SP. If yes, we drop the temp table.

    CREATE PROCEDURE [dbo].[uspInsertProduct]
    @Name	VARCHAR(255),
    @ProductNumber	VARCHAR(255),
    @ListPrice	numeric(9,2)
    AS
    BEGIN 
    		
    	DECLARE @ReturnCode BIT
    	SET @ReturnCode = 0			
    	
    	BEGIN TRY     
    		            	
    	--check if the temp exists. if it exists it means another instance of SP is running, raise an error and exit		            		
    	 IF object_id('tempdb.dbo.##2AB6B08B3BC1034D663B5FA3B59D4FB3B8FC') IS NOT NULL 
    	 BEGIN
    		SET @ReturnCode = 0 --global table already exists. not created
    		RAISERROR ( 'Unable to acquire exclusive Lock on uspInsertProduct', 16, 1 )
            RETURN
         END
    	
    	--Create a temporary global table	    
    	CREATE TABLE ##2AB6B08B3BC1034D663B5FA3B59D4FB3B8FC (id INT )
    	SET @ReturnCode = 1 --global table created in this instance
    			    
        --SLEEP FOR 10 SECONDS
        WAITFOR DELAY '00:00:10';
                        
        --insert product
        INSERT INTO tblProduct([Name], [ProductNumber], [ListPrice]) 
        VALUES (@Name, @ProductNumber, @ListPrice)
        
        -- processing is completed. drop the temporary table
        DROP TABLE ##2AB6B08B3BC1034D663B5FA3B59D4FB3B8FC
        
        END TRY      
        BEGIN CATCH
    		
    		--if the sp fails for some other reason and the table is already created drop it
    		IF object_id('tempdb.dbo.##2AB6B08B3BC1034D663B5FA3B59D4FB3B8FC') IS NOT NULL AND @ReturnCode = 1
    		BEGIN
    			DROP TABLE ##2AB6B08B3BC1034D663B5FA3B59D4FB3B8FC
    		END	
        
    		DECLARE @ErrMsg VARCHAR(4000)
    		SELECT @ErrMsg = ERROR_MESSAGE() 	    
    		RAISERROR(@ErrMsg, 15, 50)        
    	
    	END CATCH          
    END
    

    tblProduct (Table):
    I have created a simple table tblProduct in AdventureWorks database for my example.

    CREATE TABLE [dbo].[tblProduct](
    	[ProductID] [int] IDENTITY(1,1) NOT NULL,
    	[Name] [varchar](255) NOT NULL,
    	[ProductNumber] [varchar](255) NOT NULL,
    	[ListPrice] [numeric](9, 2) NOT NULL,
     CONSTRAINT [PK_tblProduct_ProductID] PRIMARY KEY CLUSTERED 
    (
    	[ProductID] 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
    

    Tester Application
    I am going to create a console application to test this solution.

    I am going to use AdventureWorks for my example. So add the following connection string to your app.config or web.config. Since I am writing a console application, I am going to put in app.config.

    <configuration>
      <appSettings>
        <add key="Sql" value="Data Source=(local);Initial Catalog=AdventureWorks;User=testuser;Password=testuser;"/>
      </appSettings>
    </configuration>
    

    In the main method of the console app, I simply call InsertProduct method with some sample product information. In InsertProduct, I invoke uspInsertProduct stored proc with the input passed.

    using System;
    using System.Data;
    using System.Data.SqlClient;
    using System.Collections.Generic;
    using System.Xml;
    using System.Configuration;
    
    namespace TipsConsoleApp
    {
        public class Program1
        {
            public static void Main(string[] args)
            {
                InsertProduct("Adjustable Race", "AR-5381", 50);
            }
    
            public static void InsertProduct(string name, string productNumber, double listPrice)
            {
                try
                {
                    //fetch the connection string from app.config
                    string connString = ConfigurationSettings.AppSettings["Sql"];
    
                    //Open SQL Connection
                    using (SqlConnection conn = new SqlConnection(connString))
                    {
                        conn.Open();
                        //Initialize command object
                        using (SqlCommand cmd = new SqlCommand("uspInsertProduct", conn))
                        {
                            //set the command type  to stored procedure
                            cmd.CommandType = CommandType.StoredProcedure;
    
                            //add parameters to the command object
                            cmd.Parameters.AddWithValue("@Name", name);
                            cmd.Parameters.AddWithValue("@ProductNumber", productNumber);
                            cmd.Parameters.AddWithValue("@ListPrice", listPrice);
    
                            //execute the stored procedure
                            cmd.ExecuteNonQuery();
    
                            Console.WriteLine("Product inserted successfully ...");
                        }
                    }
                }
                catch (Exception sqlEx)
                {
                    Console.WriteLine(sqlEx.Message);
                }
            }
        }
    }
    

    Testing:

    Step 1: Compile the solution

    Step 2: Go to the {solution root}\{project root}\bin\debug folder

    Step 3:  Create 2 new folders. Copy1 & Copy2

    Step 4: Open these 2 folders in two different windows

    Step 5. Double-Click on the exe in first folder (Copy1) and immediately do the same in the second folder  (Copy2).

    Step 6: The exe ran from the first folder (Copy1) succeeds. It will successfully insert the record. But the Stored proc sleeps for 10 seconds before it releases the lock.
    prevent a Stored Procedure from being executed twice

    Step 7: The exe ran from the second folder (Copy2) fails, as the exe from the first folder is still running.
    prevent a Stored Procedure from being executed twice

    Be Sociable, Share!

    Written by

    Software architect with over 10 years of proven experience in designing & developing n-tier and web based software applications, for Finance, Telecommunication, Manufacturing, Internet and other Commercial industries. He believes that success depends on one's ability to integrate multiple technologies to solve a simple as well as complicated problem.

    View all articles by

    Email : [email protected]

    Leave a Reply