Yesterday's news

Sometimes you want to ensure that multiple instances of your application cooperate nicely when using a shared resource. Maybe you want to ensure that only one user at a time can edit a sales order. Maybe you want to ensure that only very first instance of certain event is recorded in a table.

In the past I either used native SQL transactions (beware of deadlocks!), or had to implement my own locking table (easy to get wrong as complexity grows). Luckily, there is an alternative solution that is both easy to use and flexible. I’ve only learned about it today but apparently it was available since SQL Server 2005! The name is sp_getapplock.

Basically, sp_getapplock allows you to place a logical lock on virtual “resource” identified by a varchar key. It supports Shared/Exclusive semantics, timeouts, implicit or explicit lock release and more. Here’s the simplest scenario, assuming you want to implement a global equivalent of simple critical section (i.e. a lock that works across multiple instances of your application):

1. begin tran
2. exec sp_getapplock @resource=’my locking key’, @LockMode=Exclusive
3. …access the protected resource here…
4. commit tran

In this scheme the lock is implicitely released when transaction commits or rolls back. If you want your lock to live longer that typical transaction, you can increase its scope to SQL Connection level by setting @LockOwner = ‘Session’. The lock will be auto-released when SQL Connection is closed. (There’s no way to make the lock live accross multiple connections. At first this may sound like a deal-breaker, but if you think about it, this eliminates the need to worry about garbage-collecting the old locks remaining after your client application dies. As Stalin used to say: “There is no man — there is no problem”)

What I want to show today is how easy it is to use sp_getapplock from NHibernate to achieve safe alternative to ISession.BeginTransaction(IsolationLevel.Serializable). All you have to do is use ISession.CreateSQLQuery to create a wrapper around sp_getapplock:

using System;
using NHibernate;

namespace Sample
{
    public static class ApplicationLevelDatabaseLock
    {
        public static void ObtainApplicationLevelDatabaseLock(this ISession session, string resourceName, string lockMode, int timeoutMilliseconds)
        {
            int status = (int)session.CreateSQLQuery(
                    @"DECLARE @status INT;
                      exec @status = sp_getapplock @resource=:resourceName, @LockMode = :lockMode, @LockTimeout=:timeout;
                      SELECT @status as status")
               .AddScalar("status", NHibernateUtil.Int32)
               .SetParameter("resourceName", resourceName)
               .SetParameter("lockMode", lockMode)
               .SetParameter("timeout", timeoutMilliseconds)
               .UniqueResult();

            if (status < 0) throw new ApplicationException("Could not obtain application-level " + lockMode + " lock on " + resourceName);
        }
    }
}

Now you can use it like this:

using (var tran = session.BeginTransaction())
{
    session.ObtainApplicationLevelDatabaseLock("my exclusive resource 1", "Exclusive", 5000);
    // do your thing here, only one instance of your app will run this code at any given time.
}

Isn’t this nice? As one of my friends says (in a very dry tone, with almost British accent): “I’m all tingling with excitement”