Creating a cross-computer mutex using SQL Server

Question

I have a few computers using the same database (SQL Server 2008)

I'm trying to synchronize a task between all these computers using the database.

Each task is represented by a guid that is the lock-id (if comparing to Mutex, that would be the Mutex name)

I have a few thoughts, but I think they are kind of hacks, and was hoping someone here would have a better solution:

1. Create a new table "Locks" each row consists of a guid, lock the table row exclusively in a transaction - and complete/revert the transaction when finished.
2. use the sp_getapplock in a transaction where the lock name is the lock-id guid

I think that holding a transaction running is not so good... I thought maybe there's a solution that does not require me to hold an open transaction or session?

Collide

I know that it's not good practice to refer people to Google, but I think this is a valid exception. If OP chooses to do so, he can google "distributed synchronization" and find a lot of good resources on the topic. – Hearing

Thanks for your response, but this subject is generally very complex and is more related to synchronizing data between servers (replications, merging, etc.). I just want to hold an exclusive lock between these computers. – Collide

Answer 1

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.
}

Aden

I'd throw an exception if the sp returns < 0. See return code values at learn.microsoft.com/en-us/sql/relational-databases/… - btw. the result from ExecuteNonQuery is not the result from the sp – Amiens

Tried it out, could work. Had to make GlobalMutex IDisposable and open and close the SqlConnection and use it with "using". Going to give it a try with a concurrency test tomorrow. – Gnash

Answer 2

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.

Towery

The only shared resource I have is a database, I need to obtain an exclusive lock using this database. I can't create a shared/synchronized queue between these computers. – Collide

Sorry. But unfortunately this still doesn't help me. It's a user initiated task - it occurs on one of the computers, and while it's running I don't want users to be able to initiate a task on any of the other computers. I don't see how a queue would help me with that. – Collide

I see. Using session bound applocks (as opposed to transaction bound ones) should give you what you want. – Towery

Answer 3

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.

Oesophagus