This post has already been read 85 times!

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.

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

Leave a Reply

Post Navigation