Enforcing Mutex On A SQL Server Database – Part 2

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:

EstablishAppLock

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

CheckIsLocked

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

ReleaseLock

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

CheckIsUnlocked

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:

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

namespace SqlServerMutexExample
{
    class Program
    {
        static void Main(string[] args)
        {
            //examples of using DbMutex.IsLocked() statically and from an instance...

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

            Console.WriteLine(DbMutex.IsLocked(myConnectionString, "public", myResource, "exclusive", "session"));

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

                    //do stuff with DB here

                    Console.WriteLine(dbMutex.IsLocked());

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

            }

            Console.WriteLine(DbMutex.IsLocked(myConnectionString, "public", myResource, "exclusive", "session"));
            Console.ReadLine();

        }
    }

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

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

        public static bool IsLocked(string connectionString, string dbPrincipal, string resourceName, string lockMode, string lockOwner)
        {
            //transaction is needed if lock owner is transaction level
            var sql = string.Format("BEGIN TRAN " +
                                    "SELECT  APPLOCK_TEST ( '{0}','{1}','{2}','{3}' ) " +
                                    "COMMIT TRAN"
                                    , dbPrincipal, resourceName, lockMode, lockOwner);

            using (var connection = new SqlConnection(connectionString))
            {
                connection.Open();

                using (var command = new SqlCommand(sql, connection))
                {
                    var result = (int) command.ExecuteScalar() ;

                    return result == 0;
                }
            }

        }

        public bool IsLocked()
        {
            return IsLocked(_connection.ConnectionString, _dbPrincipal, _resource, LockMode, LockOwner);
        }

        public void GetAppLock()
        {
            const int timeOut = 0;

            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 = LockMode });
                command.Parameters.Add(new SqlParameter("@LockOwner", SqlDbType.NVarChar, 32) { Value = LockOwner });
                command.Parameters.Add(new SqlParameter("@LockTimeout", SqlDbType.Int) { Value = timeOut });
                command.Parameters.Add(new SqlParameter("@DBPrincipal", SqlDbType.NVarChar, 128) { Value = _dbPrincipal });

                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("@DBPrincipal", SqlDbType.NVarChar, 128) { Value = _dbPrincipal });

                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();
                }
            }

        }
    }
}

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 static IsLocked() method so the check can be done without needing to create an instance of the DbMutex class.

Advertisements

About Colin Legg

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

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