EF core concurrency when using multiple instances

Question

I have code similar to this:

var records = db.Records.Where(r => r.IsProcessing == false).Take(100).ToList();
records.IsProcessing = true;
await db.SaveChangesAsync()
...further work with recods

If this code runs on several instances of a microsevice, can a concurrency problem happen? (I.e. two services will get same set of records). And if yes - how do I prevent it?

I want to prevent my services from getting same records from DB if they call this method simultaneously.

Answer 1

You could retrieve the rows with a stored procedure, running in a serializable transaction. You might want other attributes in the table, something like AssignedTo which records the service assigned to that row, and another attribute indicating the processing has been completed. Otherwise, if the service retrieves some rows but fails before completing all processing, those rows remain unprocessed. When a service retrieves rows, it can use a condition like AssignedTo = self Or AssignedTo Is Null. Presumably there is also a timestamp or some other way to prioritize the selected rows.