EF core concurrency when using multiple instances-CodePudding

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.

CodePudding user response：

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.

CodePudding user response：

Yes, it will, and it may not be so easy to prevent it.

Imagine these services are tasked to update account balances. They read the balance, and add deposit amount on it:

service 1: on a busy machine (slow)
service 2: on a not so busy machine (fast)
"john" has £100 on his account
service 1: read
service 2: read
service 1: balance = balance   100;
service 2: balance = balance   100;
service 2: set processing = true
service 2: set processing = true
service 2: update record
service 1: update record.
john ends up with £200 instead of £300.

You will need to introduce a transaction scope, manage it with redis maybe, so that each instance has a point of truth to check if it's ok to get in and out of update scope. A "IsProcessing? or SetIsProcessing" over the network if you will.