Place and Drop Lock Sprocs

DropLockInstance.sql

IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'DropLockInstance')
BEGIN		
	DROP PROCEDURE locking.DropLockInstance
END
GO

CREATE PROCEDURE locking.DropLockInstance
	@lockValue varchar(150),
	@scope varchar(50),
	@system varchar(50),	
	@instanceIdentifier varchar(200)
AS
	SET NOCOUNT ON
	DECLARE @lockIsNoLongerPresent BIT = 0;
	
    -- Start a transaction.  This is needed because we scope sp_getapplock to the transaction level.  
	-- This means that the applicaiton lock it aquires will be dropped when we finish this transaction. 
	BEGIN TRANSACTION

	-- sp_getapplock uses the relevent lookup rows for our lock table.  Because of that, there is no chance
	-- of another appliation updating what we are working on.  Because of that, we can safely set the isolation level
	-- to dirty reads.  This prevents lock escalation when a lot of different locks are needed all at once.
	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

	DECLARE @lockResult INT
	DECLARE @combinedLockName nvarchar(255) = @lockValue + @scope + @system
	-- Get a lock on our combinedLockName.  This ensures that no one else is trying to get this same lock in our 
	-- table at the same time we are.  This is needed even when droping the lock to prevent a race condition where
	-- someone is placing a lock while we are dropping it.  (Or we may delete the newly created record from the Lock table)
	--   Possible Values for @lockResult
	--    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.	
	EXEC @lockResult = sp_getapplock @Resource=@combinedLockName, @LockMode='Exclusive', @LockOwner='Transaction', @LockTimeout=500

	IF @lockResult >= 0 
	BEGIN
		-- We were able to get the lock (meaning no one else is trying for this lock at the exact same time as we are deleting it.).
		-- Now we want to delete the lock if there was one.
		
		-- ----> NOTE: Maybe need ROWLOCK and READPAST here???		
		
		DELETE FROM locking.Lock
		WHERE		LockValue = @lockValue 
					AND Scope = @scope 
					AND System = @system
					-- We use the  @instanceIdentifier passed to prevent a race condition that can cause a faulty lock release
					-- This can happen like this:
					-- 1. Lock A is placed
					-- 2. Lock A times out
					-- 3. Lock B gets placed (on the same resource as lock A)
					-- 4. A process tries to drop lock A (but really drops lock B)
					-- Checking the InstanceIdentifier prevents step 4 from happening (because the InstanceIdentifier of A and B don't match in the below check)
					AND InstanceIdentifier = @instanceIdentifier
        
		SET @lockIsNoLongerPresent = 1		       
	END
	ELSE
    BEGIN
		SET @lockIsNoLongerPresent = 0
    END
    
	SELECT @lockIsNoLongerPresent AS LockRemoved	
	COMMIT TRANSACTION

	RETURN @lockIsNoLongerPresent
	SET NOCOUNT OFF
GO

GRANT EXECUTE ON locking.DropLockInstance TO DistributedLocker
GO 

DropLockTable.sql

IF EXISTS (SELECT * FROM sysobjects WHERE type = 'U' AND name = 'lock')
BEGIN
	DROP TABLE locking.lock
END
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE locking.Lock(
	[LockId] BIGINT IDENTITY(1,1) PRIMARY KEY NONCLUSTERED,
	[LockValue] [VARCHAR](150) NOT NULL,
	[Scope] [varchar](50) NOT NULL,
	[System] [varchar](50) NOT NULL,	
	[InstanceIdentifier] [VARCHAR](200) NOT NULL,
	[ExpiresBy] [datetime2] NOT NULL,
) ON [PRIMARY]

GO

CREATE CLUSTERED INDEX [CI_Lock_LockName_Scope_Application_ExpiresBy] ON locking.Lock
(
	[LockValue],
	[Scope],
	[System],
	[ExpiresBy],
	[InstanceIdentifier]
	
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

GRANT SELECT ON locking.Lock TO DistributedLocker
GO 

PlaceLock.sql

IF EXISTS (SELECT * FROM sysobjects WHERE type = 'p' AND name = 'PlaceLock')
BEGIN
	DROP PROCEDURE locking.PlaceLock
END
GO

CREATE PROCEDURE locking.PlaceLock
	@lockValue varchar(150),
	@scope varchar(50),
	@system varchar(50),	
	@instanceIdentifier VARCHAR(200),
	@millisecondsTillLockExpiration int
AS

	SET NOCOUNT ON
	DECLARE @LockGranted BIT = 0
	-- Start a transaction.  This is needed because we scope sp_getapplock to the transaction level.  
	-- This means that the applicaiton lock it aquires will be dropped when we finish this transaction. 
	BEGIN TRANSACTION

	-- sp_getapplock the relevant lookup rows for our lock table.  Because of that, there is no chance
	-- of another appliation updating what we are working on.  Because of that, we can safely set the isolation level
	-- to dirty reads.  This prevents lock escalation when a lot of different locks are needed all at once.
	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

	DECLARE @lockResult INT
	DECLARE @combinedLockName nvarchar(255) = @lockValue + @scope + @system
	-- Get a lock on our combinedLockName.  This ensures that no one else is trying to get this same lock in our 
	-- table at the same time we are.
	--   Possible Values for @lockResult
	--    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.
	EXEC @lockResult = sp_getapplock @Resource=@combinedLockName, @LockMode='Exclusive', @LockOwner='Transaction', @LockTimeout=500
	
	IF @lockResult >= 0 
	BEGIN
		-- We were able to get the app lock (meaning no one else is trying for this lock at the exact same time as us).
		-- Now we want to see if there is a lock recorded in the lock table for what we are doing.
		IF (EXISTS( SELECT  lock.LockId
					FROM	locking.Lock lock
					WHERE	lock.LockValue = @lockValue
							AND lock.Scope = @scope
							AND lock.System = @system
							-- If the record is past the "expires by" date then it does not count as a recorded lock
							AND lock.ExpiresBy > SYSDATETIME()))
		BEGIN
			-- If there is an existing record (inside its expires by window) in the lock table, then this is a locked record.
			SET @LockGranted = 0
			PRINT 'Lock Failed'            
		END
		ELSE
		BEGIN
			-- There are no locks on what we are planning to lock.  We can grant the lock.
			-- There may be expired locks for this lock.  If there are this will clean them up.
			DELETE FROM locking.Lock
			WHERE	LockValue = @lockValue 
					AND Scope = @scope 
					AND System = @system

			-- Insert our new lock in to the lock table.
			INSERT INTO locking.Lock ( LockValue, Scope, System, InstanceIdentifier, ExpiresBy ) VALUES ( @lockValue,@scope, @system, @instanceIdentifier, DATEADD(MILLISECOND, @millisecondsTillLockExpiration, SYSDATETIME()))
			
			SET @LockGranted = 1
			PRINT 'Lock Succeeded'
		END
	END
	ELSE
    BEGIN
		-- Someone else already has an system lock on what we are going for.  We cannot grant the lock.
		SET @LockGranted = 0
		PRINT 'Lock Failed'
	END
    
	SELECT @LockGranted AS LockGranted
	COMMIT TRANSACTION	
	SET NOCOUNT OFF
GO

GRANT EXECUTE ON locking.PlaceLock TO DistributedLocker
GO