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:
