Optimistic concurrency with Entity Framework and MySQL

2
votes

I'm currently developing an app using Entity Framework 4.1 and MySQL. I want to use optimistic concurrency and therefore need to create a table structure which allows EF to detect concurrency issues. My goal is something similar to this: http://blogs.msdn.com/b/alexj/archive/2009/05/20/tip-19-how-to-use-optimistic-concurrency-in-the-entity-framework.aspx.

My problem is that the timestamp-type is different in MySQL compared to MS SQL Server. In addition to that neither timestamp nor datetime offer sub-second precision in MySQL (http://feedblog.org/2007/05/26/why-doesnt-mysql-support-millisecond-datetime-resolution/). These types would therefore be quite bad in detecting concurrency-problems.

What other data-type could I use to solve this? I was thinking of maybe using a Guid. There are two potential problems with this approach though: 1. MySQL stores Guids as char(36) making them very inefficient. 2. I'm not sure if the EF requires the row-version to be strictly increasing or if it's enough that it's unique.

3
c#.netmysqlentity-frameworkconcurrency
how complex is the system? could you, for example, use a pre-submit hook to increment an integer on the row (like a manually implemented, row-specific ROWVERSION)? - Marc Gravell
All insertions and updates are run through the same generic method so I could possibly increment the row-version there. However, the code could potentially run on several different machines so it could be hard to sync that across the servers. - Yrlec
you don't have to sync it, surely; if you are guarding against changes inside the unit of work, it should already be automatic; if you are holding the rowversion between calls (at the client), then just compare to the rowversion on the record you fetched...? - Marc Gravell
Cool, that was what I thought. I was just not sure exactly how the concurrency-checks were implemented in EF. So the only thing that's important is that you can detect updates after your fetch and this solution gives you that. Even though the updated rowversion in the context might be identical to the rowversion of the concurrently updated row in the database. Am I understanding things correctly? - Yrlec

3 Answers

3
votes

Big caveat: NOT TESTED - just thinking aloud.

EF supports override of SaveChanges, so perhaps one option is to define an interface such as:

interface IVersionedRow {
    int RowVersion {get;set;}
}

and add an int RowVersion property/field to both your model class(es) and the database table(s), and use partial class to implement this interface (using implicit interface implementation):

partial class Customer : IVersionedRow {}
partial class Order : IVersionedRow {}
...

Then override SaveChanges, something like:

public override int SaveChanges(SaveOptions options)
{    
    foreach (ObjectStateEntry entry in
        ObjectStateManager.GetObjectStateEntries(EntityState.Modified))
    {
        var v = entry.Entity as IVersionedRow;
        if(v != null) v.RowVersion++;
    }
    return base.SaveChanges(options);
}

that should then function (in theory - untested) as a manually implemented row-version counter. Leave change-validation enabled for RowVersion, and that should serve.

1
votes

This is tested solution (for EF6 and above).

Here what you have to do in your model:

    [Table("some_table")]
    public class SomeEntity : IVersionedRow //define an interface as described previously and replace int type to long
    {
        ...
        [Column("row_version")]
        public long RowVersion { get; set; }
    }

Then in your context:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder.Entity<SomeEntity>()
        .Property(p => p.RowVersion).IsConcurrencyToken();
    base.OnModelCreating(modelBuilder);
}

public override int SaveChanges()
{    
    var objectContextAdapter = this as IObjectContextAdapter;
    if (objectContextAdapter != null) {
        objectContextAdapter.ObjectContext.DetectChanges();
        foreach (ObjectStateEntry entry in objectContextAdapter.ObjectContext.ObjectStateManager.GetObjectStateEntries(EntityState.Modified)) {
            var v = entry.Entity as IVersionedRow;
            if (v != null) 
                v.RowVersion++;
        }
    }
    return base.SaveChanges();
}

Now when you do update or delete operation don't forget to include DbUpdateConcurrencyException . It works perfect for me.

1
votes

I have just submitted a PR to MySQL .NET Connector v6.9.10 that provides a solution for this issue that provides optimistic locking between EF and non-EF applications. See https://stackoverflow.com/a/50147396/365261 for details.