entity framework 6 and pessimistic concurrency

Question

I'm working on a project to gradually phase out a legacy application. In the proces, as a temporary solution we integrate with the legacy application using the database.

The legacy application uses transactions with serializable isolation level. Because of database integration with a legacy application, i am for the moment best off using the same pessimistic concurrency model and serializable isolation level.

These serialised transactions should not only be wrapped around the SaveChanges statement but includes some reads of data as well.

I do this by

• Creation a transactionScope around my DbContext with serialised isolation level.
• Create a DbContext
• Do some reads
• Do some changes to objects
• Call SaveChanges on the DbContext
• Commit the transaction scope (thus saving the changes)

I am under the notion that this wraps my entire reads and writes into on serialised transaction and then commits.

I consider this a way form of pessimistic concurrency.

However, reading this article, https://docs.microsoft.com/en-us/aspnet/mvc/overview/getting-started/getting-started-with-ef-using-mvc/handling-concurrency-with-the-entity-framework-in-an-asp-net-mvc-application states that ef does not support pessimistic concurrency.

My question is:

• A: Does EF support my way of using a serializable transaction around reads and writes
• B: Wrapping the reads and writes in one transaction gives me the guarantee that my read data is not changed when committing the transaction.
• C: This is a form of pessimistic concurrency right?

Answer 1

One way to acheive pessimistic concurrency is to use sonething like this:

var options = new TransactionOptions
{
   IsolationLevel = System.Transactions.IsolationLevel.Serializable,
   Timeout = new TimeSpan(0, 0, 0, 10)
};

using(var scope = new TransactionScope(TransactionScopeOption.RequiresNew, options))
{ ... stuff here ...}

In VS2017 it seems you have to rightclick TransactionScope then get it to add a reference for: Reference Assemblies\Microsoft\Framework.NETFramework\v4.6.1\System.Transactions.dll

However if you have two threads attempt to increment the same counter, you will find one succeeds whereas the other thread thows a timeout in 10 seconds. The reason for this is when they proceed to saving changes they both need to upgrade their lock to exclusive, but they cannot because other transaction is already holding a shared lock on the same row. SQL Server will then detect the deadlock after a while fails one transactions to solve the deadlock. Failing one transaction will release shared lock and the second transaction will be able to upgrade its shared lock to exclusive lock and proceed with execution.

The way out of this deadlocking is to provide a UPDLOCK hint to the database using something such as:

private static TestEntity GetFirstEntity(Context context) {
return context.TestEntities
              .SqlQuery("SELECT TOP 1 Id, Value FROM TestEntities WITH (UPDLOCK)")
              .Single();
}

This code came from Ladislav Mrnka's blog which now looks to be unavailable. The other alternative is to resort to optimistic locking.

Answer 2

The document states that EF does not have a built in pessimistic concurrency support. But this does not mean you can't have pessimistic locking with EF. So YOU CAN HAVE PESSIMISTIC LOCKING WITH EF!

The recipe is simple:

• use transactions (not necessarily serializable, cause it will lead to poor perf.) - readcommitted is ok to use...but depends...
• do your changes, call dbcontext.savechanges()
• do lock your table - execute T-SQL manually, or feel free to use the code att. below.
• the given T-SQL command with the hints will keep that database locked until the duration of the given transaction.
• there's one thing you need to take care: your loaded entities might be obsolete at the point you do the lock, so all entities from the locked table should be re-fetched (reloaded).

I did a lot of pessimistic locking, but optimistic locking is better. You can't go wrong with it.

A typical example where pessimistic locking can't help is a parent child relation, where you might lock the parent and treat it like an aggregate (so you assume you are the only one having access to the child too). So if other thread tries to access the parent object, it won't work (will be blocked) until the other thread releases the lock from the parent table. But with an ORM, any other coder can load the child independently - and from that point 2 threads will make changes to the child object... With pessimistic locking you might mess up the data, with optimistic you'll get an exception, you can reload valid data and do try to save again...

So the code:

public static class DbContextSqlExtensions
{
    public static void LockTable<Entity>(this DbContext context) where Entity : class
    {
        var tableWithSchema = context.GetTableNameWithSchema<Entity>();
        context.Database.ExecuteSqlCommand(string.Format("SELECT null as dummy FROM {0} WITH (tablockx, holdlock)", tableWithSchema));
    }
}

public static class DbContextExtensions
{
    public static string GetTableNameWithSchema<T>(this DbContext context)
                where T : class
    {
        var entitySet = GetEntitySet<T>(context);
        if (entitySet == null)
            throw new Exception(string.Format("Unable to find entity set '{0}' in edm metadata", typeof(T).Name));

        var tableName = GetStringProperty(entitySet, "Schema") + "." + GetStringProperty(entitySet, "Table");
        return tableName;
    }

    private static EntitySet GetEntitySet<T>(DbContext context)
    {
        var type = typeof(T);
        var entityName = type.Name;
        var metadata = ((IObjectContextAdapter)context).ObjectContext.MetadataWorkspace;

        IEnumerable<EntitySet> entitySets;
        entitySets = metadata.GetItemCollection(DataSpace.SSpace)
                         .GetItems<EntityContainer>()
                         .Single()
                         .BaseEntitySets
                         .OfType<EntitySet>()
                         .Where(s => !s.MetadataProperties.Contains("Type")
                                     || s.MetadataProperties["Type"].ToString() == "Tables");
        var entitySet = entitySets.FirstOrDefault(t => t.Name == entityName);
        return entitySet;
    }

    private static string GetStringProperty(MetadataItem entitySet, string propertyName)
    {
        MetadataProperty property;
        if (entitySet == null)
            throw new ArgumentNullException("entitySet");
        if (entitySet.MetadataProperties.TryGetValue(propertyName, false, out property))
        {
            string str = null;
            if (((property != null) &&
                (property.Value != null)) &&
                (((str = property.Value as string) != null) &&
                !string.IsNullOrEmpty(str)))
            {
                return str;
            }
        }
        return string.Empty;
    }
}