In May I wrote about how application-level mutual exclusivity could easily be implemented on SQL Server by using sp_getapplock and sp_releaseapplock:

sp_getapplock tries to get a lock and returns one of a possible set of values which indicate various forms of success or fail

sp_releaseapplock tries to release a given lock and gives a similar result set to sp_getapplock

I then finished the post with a C# class wrapper which implements these two calls.

That’s all well and good, but what if you just want to check if an application lock has already been established by another session without actually creating a lock in doing so? SQL Server offers a built-in function called APPLOCK_TEST which does this nicely. The key points on this function are:

It returns 0 if there is a lock or 1 if there isn’t a lock

For transaction level locks it must be called within a transaction. For session level locks such as in the previous post, it does not need to be called in a transaction.

Let’s test it. First, establish a lock in a given session:

Next, go to a different session and check for the lock:

Now go back to the original session and release the lock:

Finally, go to the other session and check for the lock again:

This functionality can be added to the C# class in the other post pretty easily. Again, I’m not a C# expert by any stretch of the imagination, but here’s what I came up with:

I’ve added a parameterless IsLocked() method which can be called if you do have an instance of the class already running. I’ve then overloaded it with a staticIsLocked() method so the check can be done without needing to create an instance of the DbMutex class.