Enforcing business rules in entity framework core

Question

Let's assume that I have a controller's action which does the following:

1. checks if there is a calendar slot at a particular time
2. checks if there are no appointments already booked that overlap with that slot
3. if both conditions are satisfied it creates a new appointment at the given time

The trivial implementation presents multiple problems:

• what if the calendar slot fetched in 1 is removed before step 3?
• what if another appointment is booked after step 2 but before step 3?

The solution to these problems seems to be using the SERIALIZABLE transaction isolation level. The problem is that everybody seems to consider this transaction isolation level to be extremely dangerous as it may lead to deadlocks.

Given the following trivial solution:

public class AController
{
    // ...
    public async Task Fn(..., CancellationToken cancellationToken)
    {
        var calendarSlotExists = dbContext.Slots.Where(...).AnyAsync(cancellationToken);
        var appointmentsAreOverlapping = dbContext.Appointments.Where(...).AnyAsync(cancellationToken);
        if (calendarSlotExists && !appointmentsAreOverlapping)
            dbContext.Appointments.Add(...);
        dbContext.SaveChangesAsync(cancellationToken);
    }
}

what would be the best way to always prevent concurrency problems and how should I handle eventual deadlocks?

Answer 1

Database integrity check is your best friend

Based on your description your appointments are based on slots. This made the problem a lot simpler since you can efficiently define a unique constraint for SlotId on the Appointments table. And then you would need a foreign key for Appointments.SlotId references Slot.Id

what if the calendar slot fetched in 1 is removed before step 3?

DB would throw foreign key violation exception

what if another appointment is booked after step 2 but before step 3?

DB would throw duplicated key exception

What you need to do next is to catch those two exceptions and redirect user back to the booking page. Reload data from DB again and check for any invalid entries, notify the user to make modification and try again.

For the deadlock part it really depends on your table structure. The way you access data, the way you index them, and DB's query plan. Theres no definitive answer to that.