I need to implement pessimistic concurrency control.
Basically, I want to wait for an action to finish before allowing it to be executed the second time because I want only 1 row with a specific value to exist at a time.
Example:
// I would like to block concurrent execution of this method (lock it until it's finished)
[HttpPost("open")]
public async Task<IActionResult> Open(ReportCashDrawerStateRequest request)
{
...
// CONCURRENCY PROBLEM HERE:
// This check for uniqueness will pass if Open will be executed concurrently
// before SaveChangesAsync is called which will result in duplicate rows
if (await _db.CashDrawerStates.AnyAsync(s => s.CashDrawerId == request.CashDrawerId && s.EndTime == null))
return UnprocessableEntity("Already open");
var cashDrawerState = new CashDrawerState
{
CashDrawerId = request.CashDrawerId,
StartTime = DateTime.UtcNow,
StartedById = User.GetUserId(),
StartingCashAmount = request.CashAmount
};
// because check above will pass this will result in having 2 rows with EndTime==null
// which is unwanted.
_db.CashDrawerStates.Add(cashDrawerState);
await _db.SaveChangesAsync();
...
}
This is a business logic requirement, I think that adding a unique constraint (index) would solve this.
But is there any way to solve this by implementing some kind of lock in the Open method without adding a unique constraint on the database column?
I read https://learn.microsoft.com/en-us/ef/core/saving/concurrency but it only describes handling the conflict for updates and deletes and not for inserts.
