• How to prevent stored procedure running concurrently in SQL Server

    Posted on April 8, 2013 by in Database, SQL Server

    DOTNET exposes several ways to achieve mutual exclusion in code such as Mutex, Lock, SynLock, Manual reset event or Thread wait etc.  The same thing can be achieved even in SQL Server using application locks. It is achieved by using 2 system stored procs  (sp_getapplock and sp_releaseapplock)

    Permissions:
    However to be able to call sp_getapplock a user calling the stored procedure must meet one of these conditions:
    is dbo
    is in the db_owner role
    is the DB Principal ID (e.g. guest)
    is in the DB Principal ID role (e.g. public)

    sp_getapplock ReturnCode Values:

    Value Result
    0 The lock was successfully granted synchronously.
    1 The lock was granted successfully after waiting for other incompatible locks to be released.
    -1 The lock request timed out
    -2 The lock request was canceled.
    -3 The lock request was chosen as a deadlock victim.
    -999 Indicates a parameter validation or other call error.

    uspInsertProduct (Stored Proc)
    I have created a stored procedure [uspInsertProduct] which would insert a new product into tblProduct table. In this storedproc, we use sp_getapplock to lock the storedproc until we are done inserting the product. Once processing is done, we explicitly release the lock by calling sp_releaseapplock.

    For our testing, I have added a wait statement that would block the execution of stored procedure, or transaction until a specified time is reached.

    If an application lock owner is a transaction, the lock gets automatically released when the transaction ends.  If the application lock owner is a session, connection has to be closed or sp_releaseapplock has to be called to release the resource.

    CREATE PROCEDURE [dbo].[uspInsertProduct]
    @Name	VARCHAR(255),
    @ProductNumber	VARCHAR(255),
    @ListPrice	numeric(9,2)
    AS
    BEGIN 
    
    	DECLARE @returnCode INT
    	
    	BEGIN TRY     
    		            		
    	EXEC @returnCode = sp_getapplock 
    			-- name of the resource. unique name nvarchar(255), truncated to 255 if longer
    			@Resource = 'uspInsertProduct', 
    			--lock_mode is nvarchar(32) and has no default value. 
    			-- Shared, Update, IntentShared, IntentExclusive, or Exclusive.
    			@LockMode = 'Exclusive', 
    			--Is the owner of the lock, which is the lock_owner value when the lock was requested. lock_owner is nvarchar(32). 
    			--The value can be Transaction (the default) or Session. 
    			--When the lock_owner value is Transaction, by default or specified explicitly, sp_getapplock must be executed from within a transaction.
    			@LockOwner = 'Session', 
    			--Is a lock time-out value in milliseconds. The default value is the same as the value returned by @@LOCK_TIMEOUT. 
    			--To indicate that a lock request should return an error instead of wait for the lock when the request cannot be granted immediately, specify 0.
    			@LockTimeout = 50,
    			--Is the user, role, or application role that has permissions to an object in a database. 
    			--The caller of the function must be a member of database_principal, dbo, or the db_owner fixed database role to call the function successfully. 
    			--The default is public
    			@DbPrincipal  = 'public'
    	
    	IF @returnCode NOT IN (0, 1)
        BEGIN
            RAISERROR ( 'Unable to acquire exclusive Lock on uspInsertProduct', 16, 1 )        
            RETURN
        END 
        
        --SLEEP FOR 10 SECONDS
        WAITFOR DELAY '00:00:10';
                        
        --insert product
        INSERT INTO tblProduct([Name], [ProductNumber], [ListPrice]) 
        VALUES (@Name, @ProductNumber, @ListPrice)
        
        --release the lock on stored proc
        EXEC @returnCode = sp_releaseapplock 
                            @Resource = 'uspInsertProduct',                        
                            @LockOwner = 'Session',
                            @DbPrincipal  = 'public'
        
        END TRY      
        BEGIN CATCH
    		
    		IF @returnCode IN (0, 1)
    		BEGIN
    			 EXEC @returnCode = sp_releaseapplock 
    					@Resource = 'uspInsertProduct',                        
    					@LockOwner = 'Session',
    					@DbPrincipal  = 'public'
    		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
    

    Testing

    Step 1: Open SQL Server Management Studio

    Step 2: Open 2 SQL Queries windows

    Step 3: In one Window, copy below statement
    SELECT GETDATE()
    EXEC [uspInsertProduct] ‘test product 1’, ‘test product number 1’, 5000
    SELECT GETDATE()

    Step 4: In second Window, copy below statement
    SELECT GETDATE()
    EXEC [uspInsertProduct] ‘test product 2’, ‘test product number 2’, 5000
    SELECT GETDATE()

    Step 5: Go to First window press F5, immediately go to second window and press F5.

    Step 6: First Window Output
    It will successful get the lock and inserts the record. But sleeps for 10 seconds before it releases the lock.
    prevent stored procedure running concurrently

    Step 7: Second Window Output
    It fails to get the lock on sp. It will print the message and exits.
    prevent stored procedure running concurrently

    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]

    2 Responsesso far.

    1. Dhakshinamoorthy says:

      Complete and simple method to restrict only one user at a time to use procedure. Thank you.

    2. Santiago says:

      Fine!, thanks a lot!

    Leave a Reply