Lock table before inserting in EF Core 5

Question

I have the following scenario:

• A user can create orders having a given amount (e.g.: 500$)
• There is a limit for the total orders' amount that can be added for a single day (e.g.: max 2000$/day)

At the moment, when creating a new order, this requirement is implemented as follows:

var newOrder = /* logic for creating the new order */;

var orders = _ordersRepository.GetAllBy(userId, date); // get the orders from the db
var totalAmount = orders.Sum(o => o.Amount); 
if(totalAmount < MaximumAmount) {
  newOrder.IsApproved = true;
}
else {
  newOrder.IsApproved = false;
}

_ordersRepository.Add(newOrder);
_ordersRepository.SaveChanges(); // insert into the db

The problem with this approach is that it doesn't handle concurrent insertions properly in scenarios like:

• Maximum orders limit: 2000$
• In a single second, a user sends 10 requests for creating 10 new orders of 500$ each

The requests are handled concurrently and because of the short timeframe, the currently implemented check is executed before the new orders are saved in the database and therefore allows creating all of them. In the end, the user ends exceeding the maximum limit.

How could I solve this issue, ideally without having to call SaveChanges multiple times? I'm using Entity Framework Core 5 and SQL Server.

Answer 1

Thank you for the suggestions from the comments!

I've tried using transactions with the IsolationLevel set to Serializable, but then I've realized this would lock too many tables (the example from the question is a dummy one, the actual implementation is more complex).

I agree that sometimes it might be easier to have this kind of logic in the database, but adding a stored procedure for this will kind of break the current consistency and most probably leave the door open for other stored procedures. I'm not saying stored procedures are bad, just that in my current situation, even if it is a bit harder/more complex to achieve this without them, I believe it's worth it for consistency reasons.

The solution I've ended with

I've ended splitting the flow in 2 steps as follows:

// step 1
var newOrder = /* logic for creating the new order */;
_ordersRepository.Add(newOrder);
_ordersRepository.SaveChanges(); // insert into the db

// step 2
var orders = _ordersRepository.GetAllBy(userId, date); // get the orders from the db
var totalAmount = orders.Sum(o => o.Amount); 
if(totalAmount < MaximumAmount) {
  newOrder.IsApproved = true;
}
_ordersRepository.Update(newOrder); 
_ordersRepository.SaveChanges(); // update the new order

• Step 1 just creates the new order and inserts it into the database, the IsApproved flag being left to the default which is false.
• Step 2 performs the daily limit validation and if the check passes, the IsApproved flag to true.

I know it's not an actual solution, but a workaround. Locking a table might have a too big performance impact, especially if the given table is used by multiple app features. With this solution, even if there was an issue in the Step 2, the order will be left with IsApproved=false so it won't have any impact and the user can either try again later, or somebody from support can handle it.