Enforcing Mutex On A SQL Server Database

I’ve been working on a project which involved several different components. These components had the potential to interact with a SQL Server database at the same time and  I had to come up with a way of ensuring that each component could have mutual exclusivity (mutex) on the database if it needed to.

One way to do this is to have a ‘mutex’ table in the database which has only one row. When the component requires mutual exclusivity on the database it begins a transaction and performs an update on the table. The transaction is left open until the component has finished its work. Other components can identify if they are allowed to go ahead and do their processing by trying to do the same thing: if they can’t get a lock then they can be told to either wait or throw an error.

SQL Server offers a better alternative via an API called sp_getapplock. sp_getapplock is extremely easy to use and offers transaction or session (the one I’m interested in here) level locks. In this case all I have to do is pick a unique name for my application – say RADExcelSpecialSolution – which won’t get confused with anyone else’s: it’s important to note that sp_getapplock applies at a server level rather than a database level. Let’s give it a test run in Management Studio:

The return value of 0 indicates a success. Passing a value of 0 into the @locktimeout parameter means that it will return an error code if the request can’t be granted immediately. If I keep that connection open and try to re-run the request from a different SPID then the procedure will execute successfully but will return a value of -1 which means that the lock request timed out:

The session level lock will be retained until the session applying the lock is closed or it calls sp_releaseapplock:

Pretty simple, eh? Normally I’d close out this sort of post with a VBA demo, but I thought it’d make a nice change to have a crack at doing it in C# instead:

using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;

namespace SqlServerMutexExample
{
    class Program
    {
        static void Main(string[] args)
        {

            var myConnectionString = ConfigurationManager.AppSettings["MyConnectionString"];
            var myResource = ConfigurationManager.AppSettings["MyResource"];

            using (var dbMutex = new DbMutex(myConnectionString, myResource))
            {
                try
                {
                    dbMutex.GetAppLock();
                    Console.WriteLine("Lock successful!");

                    //do stuff with DB here

                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex.Message);
                }

            }

            Console.ReadLine();

        }
    }

    public class DbMutex : IDisposable
    {
        private readonly SqlConnection _connection;
        private readonly string _resource;
        private int _lockCount;
        private const string LockOwner = "session";

        public DbMutex(string connectionString, string resource)
        {
            _connection = new SqlConnection(connectionString);
            _connection.Open();
            _resource = resource;
        }

        public void GetAppLock()
        {
            int result;
            string errMsg;

            using (var command = new SqlCommand())
            {
                command.Connection = _connection;
                command.CommandType = CommandType.StoredProcedure;
                command.CommandText = "sp_getapplock";

                command.Parameters.Add(new SqlParameter("@Resource", SqlDbType.NVarChar, 255) { Value = _resource });
                command.Parameters.Add(new SqlParameter("@LockMode", SqlDbType.NVarChar, 32) { Value = "Exclusive" });
                command.Parameters.Add(new SqlParameter("@LockOwner", SqlDbType.NVarChar, 32) { Value = LockOwner });
                command.Parameters.Add(new SqlParameter("@LockTimeout", SqlDbType.Int) { Value = 0 });
                command.Parameters.Add(new SqlParameter("@Result", SqlDbType.Int) { Direction = ParameterDirection.ReturnValue });

                command.ExecuteNonQuery();

                result =  (int) command.Parameters["@Result"].Value;
            }

            switch (result)
            {
                case 0:
                case 1:
                    _lockCount++;
                    return;
                case -1:
                    errMsg = "The lock request timed out.";
                    break;
                case -2:
                    errMsg = "The lock request was canceled.";
                    break;
                case -3:
                    errMsg = "The lock request was chosen as a deadlock victim.";
                    break;
                case -999:
                    errMsg = "Indicates a parameter validation or other call error.";
                    break;
                default:
                    errMsg = "Unexpected return value";
                    break;
            }

            throw new Exception(errMsg);

        }

        private void ReleaseAppLock()
        {
            int result;
            string errMsg;

            using (var command = new SqlCommand())
            {
                command.Connection = _connection;
                command.CommandType = CommandType.StoredProcedure;
                command.CommandText = "sp_releaseapplock";

                command.Parameters.Add(new SqlParameter("@Resource", SqlDbType.NVarChar, 255) { Value = _resource });
                command.Parameters.Add(new SqlParameter("@LockOwner", SqlDbType.NVarChar, 32) { Value = LockOwner });
                command.Parameters.Add(new SqlParameter("@Result", SqlDbType.Int) { Direction = ParameterDirection.ReturnValue });

                command.ExecuteNonQuery();
                result = (int)command.Parameters["@Result"].Value;
            }

            switch (result)
            {
                case 0:
                    return;
                case -999:
                    errMsg = "Indicates a parameter validation or other call error.";
                    break;
                default:
                    errMsg = "Unexpected return value";
                    break;
            }

            throw new Exception(errMsg);

        }

        public void Dispose()
        {
            try
            {
                for (var i = 0; i < _lockCount; i++)
                {
                    ReleaseAppLock();
                }
            }
            finally
            {
                if (_connection != null && _connection.State != ConnectionState.Closed)
                {
                    _connection.Close();
                }
            }

        }
    }
}

When a DbMutex object is created it opens a connection which is maintained until it is disposed.  A lock is established via the GetAppLock() method which should only need to be called once. The DbMutex class implements IDisposable so its Dispose() method is automatically called at the end of the Using block. Here are the relevant outputs in the console window depending on whether a lock was successfully established or not: