SELECT and UPDATE as atomic operation in Entity Framework Core

Question

I'm using Entity Framework Core 7 and I have a case like that: entity with columns: Id, TakenBy which is mapped to table in SQL Server 2018.

Now I have few processes working on the same table (for some reasons) and each process takes a look at that table and checks if there is any row with TakenBy = null. If so then it takes responsibility of that row by updating it TakenBy value and start doing some operation.

Question is: how to make sure, ideally using Entity Framework Core without running a stored procedure or anything like that, that once one process can see that the row is ready to be taken, the process will take it, and not override another process if it has taken it in the meantime?

Answer 1

There are two key considerations when working with potentially highly concurrent systems when it comes to Entity Framework.

The first is dealing with any possible caching. Ideally you want DbContexts in concurrent applications to be as "fresh" as possible, so for instance having a using scope for a DbContext around just the operation. This ensures that the DbContext won't be tracking any references that might be slightly stale when you go to fetch a row. However it is common to have things like dependency injection manage a DbContext lifetime scope.

This can be mitigated using the following: (Where "Row" represents the entity you want to pull concurrently)

var row = _context.Rows.Local.FirstOrDefault(x => x.Id == rowId);
if (row != null)
    await _context.Entry(row).ReloadAsync(); // Force reloading from DB.
else
    row = _context.Rows.Single(x => x.Id == rowId); // Fetch from DB.

Once you have it and want to reserve it, set your flag and save the change immediately:

if(row.TakenBy == null)
{
   row.TakenBy = myTakenById;
   await _context.SaveChangesAsync();
} 
else
{ // TODO: Handle scenario where row cannot be reserved...
}

The second consideration is concurrency. There is still a very short race window where two processes read this row and both try to update the TakenBy. This has to be detected and enforced with help from the database. For SQL Server that is the RowVersion/Timestamp column. By adding a Timestamp type column to the table and telling EF that it is the RowVerion/Concurrency marker where the database automatically updates the marker when a change is saved.

Within the Entity, something like this:

    [Timestamp, Column("RowVersion")]
    public virtual byte[] RawRowVersion { get; internal set; }
    [NotMapped]
    public virtual long RowVersion
    {
        get { return BitConverter.ToInt64(RawRowVersion, 0); }
    }

This is a pattern I use to handle a RowVersion timestamp column in the DB which EF only supports the byte array type, where in the case of SQL Server it is a 64 bit value so I have an unmapped long version which makes it easier to output for logging etc.

Then in the case where two reads happen and the second call tries to update the row after the first has made a change, the concurrency marker does not match so the update is rejected.

if(row.TakenBy == null)
{
   row.TakenBy = myTakenById;
   try
   {
       await _context.SaveChangesAsync();
   }
   catch (DbConcurrencyUpdateException)
   {
      // TODO: Handle scenario where missed reservation window.
   }
else
{ // TODO: Handle scenario where row cannot be reserved...
}

That exception should not occur anywhere nearly as often as when the TakenBy check comes back as "taken" but it will catch the scenario where two updates are attempted at roughly the same time.