Enforcing business rules in entity framework core

.net .net-core c# entity-framework-core transactions


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)

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

3/25/2019 5:42:34 PM

Accepted Answer

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.

4/1/2019 7:13:00 PM

Popular Answer

Sometimes, in high availability scenarios, it is advisable to trade off immediate consistency (obtained with transactions) for eventual consistency (obtained with workflows/sagas).

In your example, you could consider an approach which uses an intermediate state for storing a "pending" appointment, followed by a new check for its consistency.

public async Task Fn(..., CancellationToken cancellationToken)
    // suppose "appointment" is our entity, we will store it as "pending" using
    // PendingUntil property (which is Nullable<DateTimeOffset>).
    // an appointment is in "pending" state if the PendingUntil property is set
    // (not null), and its value is >= UtcNow
    var utcNow = DateTimeOffset.UtcNow;
    appointment.PendingUntil = utcNow.AddSeconds(5);

    // we will then use this property to find out if there are other pending appointments

    var calendarSlotExists = await dbContext.Slots.Where(...).AnyAsync(cancellationToken);
    var appointmentsAreOverlapping = await dbContext.Appointments
                                                    .Where(a => a.PendingUntil == null || 
                                                                a.PendingUntil >= now)

    if (calendarSlotExists && !appointmentsAreOverlapping)
        return BadRequest(); // whatever you what to return

    await dbContext.SaveChangesAsync(cancellationToken); // save the pending appointment

    // now check if the pending appointment is still valid

    var calendarSlotStillExists = await dbContext.Slots.Where(...).AnyAsync(cancellationToken); // same query as before

    // a note on the calendar slot existance: you should of course negate any
    // slot deletion for (pending or not) appointments.

    // we will then check if there is any other appointment in pending state that was
    // stored inside the database "before" this one.
    // this query is up to you, below you'll find just an example

    var overlappingAppointments = await dbContext.Appointments.Where(...)
                                                 .Where(a => a.Id != appointment.Id &&
                                                             a.PendingUntil == null || 
                                                             a.PendingUntil >= now)

    // we are checking if other appointments (pending or not) have been written to the DB
    // of course we need to exclude the appointment we just added

    if (!calendarSlotStillExists || overlappingAppointments.Any(a => a.PendingUntil == null || a.PendingUntil < appointment.PendingUntil)
        // concurrency check failed
        // this means that another appointment was added after our first check, but before our appointment.
        // we have to remove the our appointment
        await dbContext.SaveChangesAsync(cancellationToken); // restore DB
        return BadRequest(); // same response as before

    // ok, we can remove the pending state
    appointment.PendingUntil = null;

    await dbContext.SaveChangesAsync(cancellationToken); // insert completed
    return Ok();

This, of course, will double hits to the database, but will avoid transactions (with deadlocks and locking latency) completely.

You just need to evaluate which aspect is more important for you: scalability or immediate consistency.

Related Questions


Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow