SemaphoreSlim vs Hangfire

Question

I have a solution that receives requests to process orders. The condition is to process the order if the status of the order is Paid. Other order statuses include "Fulfilled" and "Pending".

Sample code here

var orderDetails = await _dbContext.Orders.FirstOrDefaultAsync(s => s.Id == orderId);

string errMsg = string.Empty;

if (orderDetails == null)
{
errMsg = "Invalid order";
//Handle invalid order id case
return new ServiceTypeResponse<SimpleOrderDetailsResponseModel>
  {
    Data = null,
    Errors = new List<string> { errMsg },
    ResponseMessage = errMsg,
    StatusCode = 400,
    Success = false
  };
}

if (orderDetails.Status == OrderStatus.Fullfilled)
{

//handle already fulfilled order case
errMsg = "This order has already been fulfilled";
return new ServiceTypeResponse<SimpleOrderDetailsResponseModel>
{
    Data = new SimpleOrderDetailsResponseModel(orderDetails),
    Errors = null,
    ResponseMessage = errMsg,
    StatusCode = 200,
    Success = true
};
}

if (orderDetails.Status == OrderStatus.Paid)
{
 //1. process the order here and give value to the customer
 //2. Change the status of the order to Fulfilled
}
return;
}

I am trying to avoid a race condition where multiple threads send a request to this solution to process the order. To avoid processing the order multiple times from the multiple threads, I am considering either implementing SemaphoreSlim or Queuing the request using Hangfire.

Though someone suggested in the comments about using optimistic concurrency locking on the database, the problem with Optimistic concurrency in this case is that optimistic concurrency does not prevent the record from being read by another thread. Therefore, after reading the record from the database by one thread and reading the status of the order as "Paid", while still processing the order and probably calling a 3rd party service to fulfill the order, before updating the record as "Fulfilled", another thread might as well read the record and call the 3rd party service and fulfill the order the second time before trying to update the record. I this cae, though the second thread would not be able to update the status of the order, it would have fulfilled the order twice.

What is the best approach to avoid processing the order multiple times considering performance and best practices

Regards

Answer 1

Inside a transaction with desired isolation level run this (instead of the _dbContext.Orders.FirstOrDefaultAsync(...)):

UPDATE orders
SET Status = 'Processing'
OUTPUT deleted.*
WHERE id = @orderId AND Status = 'Paid'

deleted.* will return the entity before the update and will have status Paid, and you map that to your orderDetails class. And then continue the rest of the code. If the specified order does not have status paid an empty set till be returned.