In 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.
{ Think Rethink } 