EF Core ideal way on how to handle concurrency for multiple reservation requests

Question

Overview

Hi, I'm developing a small application that uses ASP.NET Core as backend and Vue.js as frontend. As database access I chose Entity Framework Core for SQL Server. My doubt is about the ideal way to handle this case: Given 2 or more requests at the same time from multiple clients how to make the office seat reservation succeed only for one request.

---

DB Structure

The context is the following:

• I have a reservation table structured like this <UserId, OfficeId, Date> (Every row represents a reservation, who, where, when)
• Before to insert a new row I need to check if there is place for it. A raw SQL query could look like this SELECT COUNT(*) FROM Reservations WHERE OfficeId = x and Date = 'y'; (with x and y being the two request parameters)
• I would compare the value returned by the previous query with the maximum capacity of the office for which I want to make the reservation val_query < office_capacity. In this way I know there is room at least for one reservation.

---

My doubts

Given a single available seat, if two requests to book an office seat on the same date are processed at the same time, and both read that there is room for the reservation, they will insert the row in the Reservations table even if in theory there would only be room for one.

Here are a couple solutions I've been thinking about:

• A customized lock-based mechanism that would ensure the success of a single booking.
• 1.) Read the number of places available with the query described above 2.) If the comparison with the office capacity is successful carry out the insertion 3.) Carry out a further reading and comparison to verify that everything is in order

As for the second option, when a request inserts successfully a reservation, the readings of subsequent requests will take it into consideration and if there are no more places, they will not perform the insertion. If, on the other hand, more requests read the same data at the same time, they will perform the insertion, but at the second reading they will be able to check the violation of a constraint and remove the row in Reservations. In this last scenario, the available seat has not been assigned to any request, but at least the constraint of the maximum number of seats in an office is respected.

---

So what's my question?

My question is whether there is a design pattern or best practice to solve the problem described above.

Answer 1

Database transactions are your friend here. However, the default transaction isolation level of ReadCommitted will not work for what you need. It does not guarantee that the data you queried - whether there is a reservation for the room - will not change until your work is done; it only guarantees that the data you received is from committed transactions. By using the Serializable isolation level, you can guarantee that no competing reservations will be added until the process with the transaction completes its work (of checking availability and then adding a reservation).

This documentation contains examples of how to use a transaction with your Entity Framework context. This code should give you the general idea:

// start a transaction with the higher isolation level
using (var dbContextTransaction = context.Database.BeginTransaction(System.Data.IsolationLevel.Serializable))
{
    try
    {
        // run your query to ensure no competing reservation exists
        // use AsNoTracking to ensure the query runs against your database in the transaction context
        context.Reservations.AsNoTracking()...;

        // if it is valid, insert your reservations
        context.Reservations.Add(...);

        // run the insert statement
        context.SaveChanges();

        // commit the transaction
        dbContextTransaction.Commit();
    }
    catch
    {
        // roll back the transaction if anything went wrong so that your database isn't locked
        dbContextTransaction.Rollback();

        throw;
    }
}