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_getapplocksp_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:

Mutex MS3

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:

Mutex MS4

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

Mutex MS5

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:

 Mutex Console0 Mutex Console1

Advertisements

About Colin Legg

RAD Developer Microsoft MVP - Excel 2009 - 2014
This entry was posted in Microsoft SQL Server, Visual C# and tagged , , . Bookmark the permalink.

3 Responses to Enforcing Mutex On A SQL Server Database

  1. Sven says:

    Shouldn’t there be a decrease of _lockCount when successfully releasing the mutex? e.g. it should be

    case 0:
    _lockCount–;
    return;

    in ReleaseAppLock()

    Furthermore there could be some error checking with _lockCount, so ReleaseAppLock() should throw an Exception when being called with _lockCount==0 (e.g. someone tries to release a mutex that has never been acquired).

    Like

    • Colin Legg says:

      It’s been 2 years since I looked at this but, the way it was implemented above, ReleaseAppLock() has a private scope and is only called when all of the locks are being released in the Dispose() method using a For loop, so, as the code stands, I would say no, neither of those are necessary.

      Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s