Optimistic concurrency with MySQL and Entity Framework

Implementing optimistic concurrency for a table in SQL Server is extremely simple. Just add a column of type TIMESTAMP and configure it like this in the Entity Framework model:

Property(x => x.Version).IsRequired()
    .IsConcurrencyToken()
    .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Computed);

A SQL Server TIMESTAMP column is guaranteed to be updated with a new value each time a row is changed, and is a perfect fit for use with optimistic locking. For MySQL the situation is different because it has no datatype or functionality which replaces SQL Server’s TIMESTAMP type. You can get something similar by declaring a column like this:

TIMESTAMP type with default CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

The problem is that the MySQL TIMESTAMP type is a datetime value with second as the highest precision. This makes it useless for this purpose. Even if the datatype and CURRENT_TIMESTAMP had microseconds as highest precision, it would not be guaranteed to be unique for each update.

I ended up using GUID as the datatype for the version column, and assign new values to the version property before Entity Framework performs updates to the database.

Version property definition;

public System.Guid Version { get; set; }

Version property configuration:

Property(x => x.Version).IsRequired()
    .IsConcurrencyToken()
    .HasDatabaseGeneratedOption(DatabaseGeneratedOption.None);

Next, I declared an interface which will be added to each versioned entity:

public interface IVersionedEntity
{
    Guid Version { get; set; }
}

Last, in my DbContext class I override SaveChanges and update the Version property for all required entities before calling base.SaveChanges():

var concurrencyTokenEntries = ChangeTracker.Entries<IVersionedEntity>();
foreach (var entry in concurrencyTokenEntries)
{
    if (entry.State == EntityState.Unchanged)
    {
        continue;
    }
    entry.Entity.Version = Guid.NewGuid();
}

An exception of type DbUpdateConcurrencyException will now be thrown in case the optimistic concurrency check fails when updating a row in the database.

Etiketter: ,