Implementing application locks within SQL Server (Distributed Locking Pattern)

Question

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:

1. Do I need to make the "unlock" SERIALIZABLE as well?
2. Are there any other approaches that I could use/anything that I have forgotten?

SQL Server can either be 2008/2012

Answer 1

Do I need to make the "unlock" SERIALIZABLE as well?

Despite the name, the serializable isolation level does not guarantee transactions will be executed sequentially, or in the order received. Rather, serializable guarantees transactions will have the same persistent effects on the database as if they had executed sequentially, in some undefined order (see the link for more details).

Are there any other approaches that I could use/anything that I have forgotten?

Yes. SQL Server already provides support for arbitrary application locks via:

• sp_getapplock
• sp_releaseapplock
• APPLOCK_MODE
• APPLOCK_TEST

These built-in applock features provide a broad range of options, and include automatic deadlock detection (though any transaction rollback required is the programmer's responsibility). For example, you can choose from transaction- and session-scoped locking, in a variety of modes. Using these features to implement the behaviour you need should be quite straightforward, and certainly simpler than building your own resilient and reliable lock manager from scratch.