Implement Pessimistic Concurrency in Entity Framework Core

ConcurrencyIn a scenario where we were using SQL server as a queue, before publishing events to external queues, we wanted the data to be processed only once and in order, even with multiple processors for failover. When reading from the table we wanted to lock the records and block other processors from reading those records, while being processed. This is called Pessimistic Concurrency, unfortunately Entity Framework Core does not support this out of the box. To realize Pessimistic Concurrency you need to write your own SQL queries directly on the database (The solution is database type bound, in this case Microsoft SQL server). This blog post will show how it can be accomplished.

The basic flow I wanted to have in my code is to query the oldest x records from a table, process the records and then delete them. In between the read and delete nobody else may process the records. Technically you can accomplish that with the following pseudo code:

Start Transaction
  Select x records with exclusive lock
  For Each record in records
     Process record
     Delete record
  End For Each
Commit Transaction

In Entity Framework Core reading with a XLOCK is not supported out of the box, so you need to write your own query. This can be done with FromSql. FromSql creates a query from raw sql. The query has a WITH (XLOCK) to prevent other reads on the same data.

using(IDbContextTransaction transaction = _dbContext.Database.BeginTransaction())
{
    string sqlQuery = "SELECT * FROM myData WITH (XLOCK) ORDER BY Created";
    var records = _dbcontext.MyData.
                  .FromSql(sqlQuery)
                  .ToList();
    foreach (var r in records)
    {
        Process(r);
        Remove(r);
    }
}

In a cloud scenario, where you have configured a EnableRetryOnFailure on your database connection, this will fail. The EnableRetryOnFailure will not allow multiple queries within one transaction. You can fix that by encapsulating the code within a IExecutionStrategy => Execute which disables the retry for the Execute scope:

IExecutionStrategy strategy = _dbContext.Database.CreateExecutionStrategy();

strategy.Execute(()=>
{
... the process ...
}

By applying this pattern you can safely run multiple processors for failover to process the messages in order from the database.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.