How to implement optimistic concurrency lock with EF 6?

c# concurrency entity-framework-6


I have a question that mix SQL server, Entity Framework and Web API.

I need to manage a serial number database. To do so I created a sequence table in my SQL server. I use EF6 code first.

public class Sequence : ITrackingDate
    public string Name { get; set; }
    public long CurrentValue { get; set; }
    public long Increment { get; set; }
    public long MinimumValue { get; set; }
    public long MaximumValue { get; set; }
    public bool IsCycling { get; set; }
    public DateTime CreateDate { get; set; }
    public DateTime ModifyDate { get; set; }

ITrackingDate is used to update my CreateDate and ModifyDate in datacontext of EF. I override SaveChange.

This should work as a sequence (like in SQL server 2012) but I want to manage it myself in a specific database because I will have to create many sequences and most of them will come with more data that what we only have in the base sequence system. One feature I need to have is the possibility to update the sequence by range.

var serial = context.Sequences.SingleOrDefault(y => y.Name == serialName);
                var firstValue = serial.CurrentValue + 1;
                var lastValue = serial.CurrentValue + range;
                serial.CurrentValue = lastValue;

This code is behind a webservice so it may be possible 2 requests done at same moment that perform this section of code at same moment return the same value. This should never happens.

So the SaveChanges() method must also check what I'm saving was not safe by another thread since the read().

11/7/2018 3:30:11 PM

Popular Answer

It is certainly possible to make this safe. First note, that concurrent accesses will necessarily have to come one after the other. Optimistic concurrency would not help here.

There are two basic strategies to solve this problem:

  1. Use a RepeatableRead transaction. This will lead to deadlocks which you can solve by retrying. Retrying is safe and is guaranteed to make progress.
  2. U-lock the row as the first action in your transaction. Entity Framework cannot do this directly so you need manual SQL for that. The correct locking hints are UPDLOCK, HOLDLOCK, ROWLOCK.
11/7/2018 4:33:40 PM

Related Questions


Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow