How to lock a object when using load balancing

Question

Background: I'm writing a function putting long lasting operations in a queue, using C#, and each operation is kind of divided into 3 steps:
1. database operation (update/delete/add data)
2. long time calculation using web service
3. database operation (save the calculation result of step 2) on the same db table in step 1, and check the consistency of the db table, e.g., the items are the same in step 1 (Pls see below for a more detailed example)

In order to avoid dirty data or corruptions, I use a lock object (a static singleton object) to ensure the 3 steps to be done as a whole transaction. Because when multiple users are calling the function to do operations, they may modify the same db table at different steps during their own operations without this lock, e.g., user2 is deleting item A in his step1, while user1 is checking if A still exists in his step 3. (additional info: Meanwhile I'm using TransactionScope from Entity framework to ensure each database operation as a transaction, but as repeat readable.)

However, I need to put this to a cloud computing platform which uses load balancing mechanism, so actually my lock object won't take effect, because the function will be deployed on different servers.

Question: what can I do to make my lock object working under above circumstance?

Answer 1

This is a tricky problem - you need a distributed lock, or some sort of shared state.

Since you already have the database, you could change your implementation from a "static C# lock" and instead the database to manage your lock for you over the whole "transaction".

You don't say what database you are using, but if it's SQL Server, then you can use an application lock to achieve this. This lets you explicitly "lock" an object, and all other clients will wait until that object is unlocked. Check out:

http://technet.microsoft.com/en-us/library/ms189823.aspx

I've coded up an example implementation below. Start two instances to test it out.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Transactions;

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            var locker = new SqlApplicationLock("MyAceApplication",
                "Server=xxx;Database=scratch;User Id=xx;Password=xxx;");

            Console.WriteLine("Aquiring the lock");
            using (locker.TakeLock(TimeSpan.FromMinutes(2)))
            {
                Console.WriteLine("Lock Aquired, doing work which no one else can do. Press any key to release the lock.");
                Console.ReadKey();
            }
            Console.WriteLine("Lock Released"); 
        }

        class SqlApplicationLock : IDisposable
        {
            private readonly String _uniqueId;
            private readonly SqlConnection _sqlConnection;
            private Boolean _isLockTaken = false;

            public SqlApplicationLock(
                String uniqueId,                 
                String connectionString)
            {
                _uniqueId = uniqueId;
                _sqlConnection = new SqlConnection(connectionString);
                _sqlConnection.Open();
            }

            public IDisposable TakeLock(TimeSpan takeLockTimeout)
            {
                using (TransactionScope transactionScope = new TransactionScope(TransactionScopeOption.Suppress))
                {
                    SqlCommand sqlCommand = new SqlCommand("sp_getapplock", _sqlConnection);
                    sqlCommand.CommandType = CommandType.StoredProcedure;
                    sqlCommand.CommandTimeout = (int)takeLockTimeout.TotalSeconds;

                    sqlCommand.Parameters.AddWithValue("Resource", _uniqueId);
                    sqlCommand.Parameters.AddWithValue("LockOwner", "Session");
                    sqlCommand.Parameters.AddWithValue("LockMode", "Exclusive");
                    sqlCommand.Parameters.AddWithValue("LockTimeout", (Int32)takeLockTimeout.TotalMilliseconds);

                    SqlParameter returnValue = sqlCommand.Parameters.Add("ReturnValue", SqlDbType.Int);
                    returnValue.Direction = ParameterDirection.ReturnValue;
                    sqlCommand.ExecuteNonQuery();

                    if ((int)returnValue.Value < 0)
                    {
                        throw new Exception(String.Format("sp_getapplock failed with errorCode '{0}'",
                            returnValue.Value));
                    }

                    _isLockTaken = true;

                    transactionScope.Complete();
                }

                return this;
            }

            public void ReleaseLock()
            {
                using (TransactionScope transactionScope = new TransactionScope(TransactionScopeOption.Suppress))
                {
                    SqlCommand sqlCommand = new SqlCommand("sp_releaseapplock", _sqlConnection);
                    sqlCommand.CommandType = CommandType.StoredProcedure;

                    sqlCommand.Parameters.AddWithValue("Resource", _uniqueId);
                    sqlCommand.Parameters.AddWithValue("LockOwner", "Session");

                    sqlCommand.ExecuteNonQuery();
                    _isLockTaken = false;
                    transactionScope.Complete();
                }
            }

            public void Dispose()
            {
                if (_isLockTaken)
                {
                    ReleaseLock();
                }
                _sqlConnection.Close();
            }
        }
    }
}