In my application I have to perform a distributed locking pattern. Because we already have an instance of SQL Server to use, we decided that it would be easiest to implement the locking at SQL layer of our web application.
A lock can be obtained based on numerous conditions including:
- The type of lock requested
- An arbitrary application identifier
For all intents and purposes, treat the above two conditions as
int data types.
In this pattern, we wish to treat all of our locks as FIFO, which I believe the
SERIALIZABLE isolation level will give us.
Here is how we propose to perform the "lock":
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE IF EXISTS (SELECT 1 FROM locks WHERE LockType = @LockType AND ApplicationIdentifier = @ApplicationIdentifier) BEGIN -- Awesome, the lock will be acquired INSERT INTO locks OUTPUT INSERTED.LockId VALUES (2,3) END ELSE BEGIN -- Someone already has the lock SELECT -1 END SET TRANSACTION ISOLATION LEVEL READ COMITTED
And the "unlock":
DELETE FROM locks WHERE LockId = @LockId
So my question is two-fold:
- Do I need to make the "unlock"
- Are there any other approaches that I could use/anything that I have forgotten?
SQL Server can either be 2008/2012