Concurrent edit with Entity Framework

Question

I've implemented a lock entity method for concurrent entity editing (user can't start editing before locking it). Information about locked entities is stored in table.

Here is the code that implements locking.

public virtual ObjectLockInfo Lock(int id)
{
        using (var context = DataContextFactory.GetContext())
        {                
             var i =
                context.ObjectLock.Any(
                    c =>
                        c.ObjectID == id && (c.ObjectType == (int) _lockObjectType) &&
                        c.LockExpireDate > DateTime.Now);

            if (i)
                return new ObjectLockInfo
                {
                    ErrorMessage = "Object is locked",
                    IsLocked = false
                };

            var lockLock = new ObjectLock
            {
                LockExpireDate = DateTime.Now.AddMinutes(20),
                LockObjectDate = DateTime.Now,
                ObjectID = id,
                ObjectType = (int) _lockObjectType,
                UserID = _currentUserID
            };

            context.ObjectLock.Add(lockLock);
            context.SaveChanges();
            return new ObjectLockInfo
            {
                Id = lockLock.ID,
                IsLocked = true,
                LockDate = lockLock.LockObjectDate,
                LockExpireDate = lockLock.LockExpireDate
            };
        }

    }

This code has a problem: if we get another lock request before context.SaveChanges() is executed (and it passes Any condition), we will get two locks on the table.

How to avoid it (without using INSTEAD OF INSERT trigger)?

Scott Chamberlain · Accepted Answer · 2015-06-30 15:51:59Z

Use a transaction with Serializable isolation, this will prevent "phantom rows" which is what you are concerned about.

public virtual ObjectLockInfo Lock(int id)
{
    using (var context = DataContextFactory.GetContext())
    using (var dbContextTransaction = context.Database.BeginTransaction(IsolationLevel.Serializable)) 
    {                
         var i =
            context.ObjectLock.Any(
                c =>
                    c.ObjectID == id && (c.ObjectType == (int) _lockObjectType) &&
                    c.LockExpireDate > DateTime.Now);

        if (i)
            return new ObjectLockInfo
            {
                ErrorMessage = "Object is locked",
                IsLocked = false
            };

        var lockLock = new ObjectLock
        {
            LockExpireDate = DateTime.Now.AddMinutes(20),
            LockObjectDate = DateTime.Now,
            ObjectID = id,
            ObjectType = (int) _lockObjectType,
            UserID = _currentUserID
        };

        context.ObjectLock.Add(lockLock);
        context.SaveChanges();

        dbContextTransaction.Commit();

        return new ObjectLockInfo
        {
            Id = lockLock.ID,
            IsLocked = true,
            LockDate = lockLock.LockObjectDate,
            LockExpireDate = lockLock.LockExpireDate
        };
    }

}

Now once the Any has been performed no other writer will be able to write to the table (or reader read from it if their own transaction is Read Commited or higher) until you commit your changes.

Collectives™ on Stack Overflow

Concurrent edit with Entity Framework

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related