Offline Pessimistic Lock in Entity Framework (or any other ORM)

I’ve always found surprising that so few projects were started with data access concurrency in mind. I’ve heard many discussions about new fancy frameworks and UI controls the teams were about to use but possibility of concurrent access to users’ data didn’t appear to be a concern in their minds.

Wen you think about it, it seems to be very logical. People have a natural tendency to avoid problems they haven’t encountered directly. There are so few people with attitude of challenging obstacles not laying directly on their path. And after all, most of the questions managers tend to ask are like “when will you finish this use case”, “how much will this cost”. Eventually, the more technical aware PMs ask for iPad or recent web browser support. I’ve never heard a manager asking for example “what will happen when user A edits invoice while user B is issuing a warehouse document ?” question.

The fact that software tools providers does not mention concurrency handling (but not only that) in context of using their tools doesn’t help at all. There are still so many people who acquire their knowledge mostly from Microsoft marketing hype and don’t ask themselves these kind of tough questions. It is also very significant that Microsoft’s flag ORM product – Entity Framework – implements out of the box only very limited optimistic concurrency mechanism which is not enough for most of the real world systems.

To shed some light on the concurrency issue I decided to show a real live example of Offline Pessimistic Lock pattern I’ve implemented in my recent project.

The Offline Pessimistic Lock is very useful pattern. It allows you to ensure that, for example, only one user is altering an object’s data. In my example it happens by creating a separate lock object on the whole document. This object contains information about the type of locked object (i.e. domain entity), it’s key and a user which holds this lock.

The LockManager class is responsible for acquiring, releasing and ensuring these locks. The lock objects are stored in WriteLocks table.

CREATE TABLE [dbo].[WriteLocks](
	[OBJECT_TYPE] [nvarchar](255) NOT NULL,
	[OBJECT_ID] [nvarchar](50) NOT NULL,
	[ACCOUNT_ID] [int] NOT NULL,
	[ACQ_TIMESTAMP] [datetime] NOT NULL,
 CONSTRAINT [PK_WriteLocks] PRIMARY KEY CLUSTERED 
(
	[OBJECT_TYPE] ASC,
	[OBJECT_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
namespace ATMS.Core.Concurrency
{
    public class LockManager
    {
        /// <summary>
        /// Utility method for showing information about existing lock on object
        /// </summary>
        public void ShowNotification(Control parent)
        {
            MessageBox.Show(Res.Lock_ObjectBeingLocked, string.Empty, MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
        }

        /// <summary>
        /// Ensures that lock on the object already exists
        /// </summary>
        /// <typeparam name="TObj">Entity type</typeparam>
        /// <param name="id">Entity key</param>
        /// <returns>Returns true if lock acquired by current user exists; false otherwise</returns>
        public bool EnsureLock<TObj>(object id)
        {
            var user = UserInfo.Current;

            if (user == null)
                throw new InvalidOperationException("User not logged in - can not release write lock");

            var typeStr = typeof(TObj).FullName;
            var idStr = id.ToString();

            using (var ctx = CoreEntitiesBuilder.Build())
            {
                var exist = ctx.WriteLocks.FirstOrDefault(x => x.OBJECT_TYPE == typeStr && x.OBJECT_ID == idStr && x.ACCOUNT_ID == user.AccountId);
                return exist != null;
            }
        }

        /// <summary>
        /// Removes lock acquired on the object hence it is available for locking by other users
        /// </summary>
        /// <typeparam name="TObj">Entity type</typeparam>
        /// <param name="id">Entity key</param>
        /// <returns>Returns true if there was a lock to release; false otherwise</returns>
        public bool ReleaseLock<TObj>(object id)
        {
            var user = UserInfo.Current;

            if (user == null)
                throw new InvalidOperationException("User not logged in - can not release write lock");

            var typeStr = typeof(TObj).FullName;
            var idStr = id.ToString();

            using (var ctx = CoreEntitiesBuilder.Build())
            {
                var exist = ctx.WriteLocks.FirstOrDefault(x => x.OBJECT_TYPE == typeStr && x.OBJECT_ID == idStr);

                if (exist == null)
                    return true;

                if (exist != null && exist.ACCOUNT_ID == user.AccountId)
                {
                    ctx.WriteLocks.Remove(exist);
                    ctx.SaveChanges();
                    return true;
                }
                else
                    return false;
            }
        }

        /// <summary>
        /// Removes a batch of locks acquired on objects
        /// </summary>
        /// <typeparam name="TObj">Entity type</typeparam>
        /// <param name="ids">Entity keys</param>
        /// <returns>Returns true if all locks were successfully released; false otherwise</returns>
        public bool ReleaseLocks<TObj>(object[] ids)
        {
            var user = UserInfo.Current;

            if (user == null)
                throw new InvalidOperationException("User not logged in - can not release write lock");

            var typeStr = typeof(TObj).FullName;
            var idStr = ids.Select(x => x.ToString());

            using (var ctx = CoreEntitiesBuilder.Build())
            {
                var exist = ctx.WriteLocks.Where(x => x.OBJECT_TYPE == typeStr && idStr.Contains(x.OBJECT_ID)).ToArray();

                var canRemove = exist.Where(x => x.ACCOUNT_ID == user.AccountId).ToArray();
                var canNotRemove = exist.Where(x => x.ACCOUNT_ID != user.AccountId).ToArray();

                ctx.WriteLocks.RemoveRange(canRemove);
                ctx.SaveChanges();

                return !canNotRemove.Any();
            }
        }

        /// <summary>
        /// Acquire locks on list of objects
        /// </summary>
        /// <typeparam name="TObj">Entity type</typeparam>
        /// <param name="ids">Entity keys</param>
        /// <returns>Returns true when all locks where successfully acquired; false otherwise</returns>
        public bool AcquireLocks<TObj>(object[] ids)
        {
            var user = UserInfo.Current;

            if (user == null)
                throw new InvalidOperationException("User not logged in - can not acquire write lock");

            var typeStr = typeof(TObj).FullName;
            var idStr = ids.Select(x => x.ToString());
            var result = false;

            using (var ctx = CoreEntitiesBuilder.Build())
            {
                var existing = ctx.WriteLocks.Where(x => x.OBJECT_TYPE == typeStr && idStr.Contains(x.OBJECT_ID)).ToArray();

                if (!existing.Any() ||
                    existing.All(x => x.ACCOUNT_ID == user.AccountId))
                {
                    foreach (var ex in existing)
                        ex.ACQ_TIMESTAMP = DateTime.Now;

                    var notExisting = idStr
                        .Where(x => !existing.Any(e => e.OBJECT_ID == x))
                        .Select(x => new WriteLocks()
                        {
                            ACCOUNT_ID = user.AccountId,
                            ACQ_TIMESTAMP = DateTime.Now,
                            OBJECT_ID = x,
                            OBJECT_TYPE = typeStr
                        });

                    try
                    {
                        ctx.WriteLocks.AddRange(notExisting);
                        ctx.SaveChanges();

                        result = true;
                    }
                    catch (SqlException)
                    {
                        result = false;
                    }
                }
                else
                    result = false;
            }

            return result;
        }

        /// <summary>
        /// Acquire lock on a single object
        /// </summary>
        /// <typeparam name="TObj">Entity type</typeparam>
        /// <param name="id">Entity key</param>
        /// <returns>Returns true when lock was acquired successfully; false otherwise</returns>
        public bool AcquireLock<TObj>(object id)
        {
            var user = UserInfo.Current;

            if (user == null)
                throw new InvalidOperationException("User not logged in - can not acquire write lock");

            var typeStr = typeof(TObj).FullName;
            var idStr = id.ToString();
            var result = false;

            using (var ctx = CoreEntitiesBuilder.Build())
            {
                var exist = ctx.WriteLocks.FirstOrDefault(x => x.OBJECT_TYPE == typeStr && x.OBJECT_ID == idStr);

                if (exist != null && exist.ACCOUNT_ID == user.AccountId)
                {
                    exist.ACQ_TIMESTAMP = DateTime.Now;
                    ctx.SaveChanges();

                    result = true;
                }
                else if (exist != null && exist.ACCOUNT_ID != user.AccountId)
                {
                    result = false;
                }
                else
                {
                    try
                    {
                        ctx.WriteLocks.Add(new WriteLocks()
                        {
                            ACCOUNT_ID = user.AccountId,
                            ACQ_TIMESTAMP = DateTime.Now,
                            OBJECT_ID = idStr,
                            OBJECT_TYPE = typeStr
                        });
                        ctx.SaveChanges();

                        result = true;
                    }
                    catch (SqlException)
                    {
                        result = false;
                    }
                }
            }

            return result;
        }
    }
}

Here are some examples of LockManager class usage:

private void btnSave_Click(object sender, EventArgs e)
{
	this.WithDbErrorHandling(
		() =>
		{
			if (!_lockMan.EnsureLock<rcr_CVDocument>(_currentDocument.ID))
			{
				_lockMan.ShowNotification(this);
				
				// ... 
			}
			else
				SaveDocumentData(_currentDocument.ID);
			
			// .... update UI controls etc.
		}
}

private void btnDelete_Click(object sender, EventArgs e)
{
	if (!_lockMan.AcquireLock<rcr_CVDocument>(_currentDocument.ID))
	{
		_lockMan.ShowNotification(this);
		return;
	}

	try
	{
		_currentDocument.TO_DELETE = true; //real delete handled by backend worker
		_entities.SaveChanges();
	}
	finally
	{
		_lockMan.ReleaseLock<rcr_CVDocument>(_currentDocument.ID);
	}
}

For further reading I suggest:

Advertisements

4 Responses to Offline Pessimistic Lock in Entity Framework (or any other ORM)

  1. Pingback: dotnetomaniak.pl

  2. marek says:

    Nice code – but this problem is not so often. Usually optimistic locking is enough (imo in 90% of cases). But i got one question – where is lock for lockmanager?:) What will happen if two users in same time will try to lock particular object? Last win 🙂

    • karczas says:

      1) In some domains this kind of problem is not very often, in others it appears every time. The disadvantage of optimistic locking (event offline) is that in highly concurrent environments there is a big chance of wasting your user’s time by forcing her to enter all the data and then being noticed that somebody have already changed her document (imagine entering a document for an hour and then not being able to save your changes).

      2) There is no such lock (there is a unique constraint on locks table and duplicate error handling code). I think that probability of two users clicking “Edit” button simultaneously is very small. Even if that happens we are not wasting user’s time by forcing her to apply all the changes again.

    • karczas says:

      P.S. Marek, please check out this presentation https://youtu.be/uj25PbkHb94?t=500 – it shows why & when pure optimistic concurrency is not sufficient. I’m sure that Sławek’s solutions are transferable to NHibernate and strongly doubt that there is a similar mechanism built in EF which make me think that it was not intended (nor should be used) for an enterprise systems.

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

%d bloggers like this: