• 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)

    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)
    	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)
            RAISERROR ( 'Unable to acquire exclusive Lock on uspInsertProduct', 16, 1 )        
        --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      
    		IF @returnCode IN (0, 1)
    			 EXEC @returnCode = sp_releaseapplock 
    					@Resource = 'uspInsertProduct',                        
    					@LockOwner = 'Session',
    					@DbPrincipal  = 'public'
    		DECLARE @ErrMsg VARCHAR(4000)
    		SELECT @ErrMsg = ERROR_MESSAGE() 	    
    		RAISERROR(@ErrMsg, 15, 50)        
    	END CATCH  

    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,
    	[ProductID] ASC
    ) ON [PRIMARY]


    Step 1: Open SQL Server Management Studio

    Step 2: Open 2 SQL Queries windows

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

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

    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