Creating a Cross-Computer Mutex Using Sql Server

I have put together a little class will test and feedback

public class GlobalMutex
{
    private SqlCommand _sqlCommand;
    private SqlConnection _sqlConnection;

    string sqlCommandText = @"

        declare @result int
        exec @result =sp_getapplock @Resource=@ResourceName, @LockMode='Exclusive', @LockOwner='Transaction', @LockTimeout = @LockTimeout

    ";

    public GlobalMutex(SqlConnection sqlConnection, string unqiueName, TimeSpan lockTimeout)
    {
        _sqlConnection = sqlConnection;
        _sqlCommand = new SqlCommand(sqlCommandText, sqlConnection);
        _sqlCommand.Parameters.AddWithValue("@ResourceName", unqiueName);
        _sqlCommand.Parameters.AddWithValue("@LockTimeout", lockTimeout.TotalMilliseconds);
    }

    private readonly object _lockObject = new object();
    private Locker _lock = null;
    public Locker Lock
    {
        get
        {
            lock(_lockObject)
            {
                if (_lock != null)
                {
                    throw new InvalidOperationException("Unable to call Lock twice"); // dont know why
                }
                _lock = new Locker(_sqlConnection, _sqlCommand);
            }
            return _lock;
        }
    }

    public class Locker : IDisposable
    {
        private SqlTransaction _sqlTransaction;
        private SqlCommand _sqlCommand;

        internal Locker(SqlConnection sqlConnection, SqlCommand sqlCommand)
        {
            _sqlCommand = sqlCommand;
            _sqlTransaction = sqlConnection.BeginTransaction();
            _sqlCommand.Transaction = _sqlTransaction;
            int result = sqlCommand.ExecuteNonQuery();
        }

        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }

        protected virtual void Dispose(bool disposing)
        {
            if (disposing) 
            {
                _sqlTransaction.Commit(); // maybe _sqlTransaction.Rollback() might be slower
            }
        }
    }
}

Usage is:

GlobalMutex globalMutex = new GlobalMutex(
    new SqlConnection(""),
    "myGlobalUniqueLockName",
    new TimeSpan(0, 1, 0)
);


using (globalMutex.Lock)
{
    // do work.
}

I would recommend something rather different: use a queue. Rather than explicitly lock the task, add the task to a processing queue and let the queue handler dequeue the task and perform the work. The additional decoupling will also help scalability and throughput.

If the only shared resource you have is the database, then using a transaction lock as part of the solution may be your best option. And if I'm understanding the articled linked by @Remus Rusanu in the other answer, it also requires the dequeuing to be in a transaction.

It depends somewhat on how long you plan to hold open these locks. If you are...

1. Forcing serialization for a brief operation on the lock ID in question
2. Already opening a transaction anyway to complete that operation

...then your option 2 is probably easiest and most reliable. I've had a solution like it in a production system for several years with no issues. It becomes even easier if you bundle the creation of the mutex with the creation of the transaction and wrap it all in a "using" block.

using (var transaction = MyTransactionUtil.CreateTransaction(mutexName))
{
    // do stuff
    transaction.Commit();
}

In your CreateTransaction utility method you call sp_getapplock right after creating the transaction. Then the whole thing (including the mutex) commits or rolls back together.