My group and i have made a cinema booking system for a school project where the subject was cuncurrency control. It is made with c# and entity framework in an n-tier arcitecture where the presentationtier consisted of an mvc project.
We chose to use pessimistic locking (IsolationLevel.ReadCommited) during the "select seats for reservation" phase so that the database is locked while someone is checking if seats are available and when the seats are added to the reservation. I am currently looking if for example optimistic concurrency could have been an option an if so how it would work.
Here is a picture of the database diagram:
When you click on a specific show it will automatically create an empty reservation for you and then build the screen(cinema hall) with it seats and information wheather they are available or not.
Here is the method for creating a list of the seats linked to a screen(cinema hall):
public static List<SeatReservationInfo> GetSeatInfoForShow(Guid reservationId)
{
using (EntityContext db = new EntityContext())
{
//Retrieve a reservation on its id
var reservation = db.Reservations.FirstOrDefault(r => r.Id == reservationId);
//Retrieve the show linked to the reservation
var show = db.Shows.First(i => i.Id == reservation.ShowId);
//Used to check if the reservation is expired
var expired = DateTime.Now.Subtract(new TimeSpan(0, 0, 15, 0));
//Henter sæder ud tilhørende en specifik sal og laver Seat modellen om til en SeatReservationInfo model
//Retrieve all seats linked to a specific screen and turn the Seat model into a SeatReservationModel which containt availablity status of the seat
return
db.Seats.Where(s => s.ScreenId == show.ScreenId)
.OrderBy(s => s.RowNumber)
.ThenBy(s => s.SeatNumber)
.Select(s => new SeatReservationInfo
{
Id = s.Id,
Type = s.Type,
RowNumber = s.RowNumber,
SeatNumber = s.SeatNumber,
Availability = s.ReservationSeats.Any(a => a.ReservationId == reservationId)
? SeatAvailability.ReservedSelf
: s.ReservationSeats.Any(
a =>
a.Reservation.ShowId == reservation.ShowId &&
(a.Reservation.Status == ReservationStatus.Completed ||
(a.Reservation.Status == ReservationStatus.Started &&
DateTime.Compare(a.Reservation.Created, expired) >= 0)))
? SeatAvailability.Reserved
: SeatAvailability.Available
}).ToList();
}
}
From this method, the screen(Cinema hall) is built with seats as checkboxes where you can select multible seats at once. Your selected seats is then sent with your reservationId via an AJAX call to the TryBookSeats method which will first see if anyone have tried to reserve your seats and if not then reserve them by placing them in the ReservationSeats table.
Here is the TryBookSeats method:
public static bool TryBookSeats(List<Guid> seatIds, Guid reservationId)
{
//bool who will become true if seats are available
bool success;
//Starts a database connection
using (var db = new EntityContext())
//Starts a transaction where the isolationlevel is set to ReadCommitted (pessimistic concurrency)
using (var scope = db.Database.BeginTransaction(IsolationLevel.ReadCommitted))
{
//Used to check if the reservation is expired
var expired = DateTime.Now.Subtract(new TimeSpan(0, 0, 15, 0));
//Retrieve a reservation that hasnt expired
var reservation = db.Reservations.First(x => x.Id == reservationId && DateTime.Compare(x.Created, expired) >= 0);
//Checks if the selected seats are available. if so set success to true
success = !db.ReservationSeats.Any(
i =>
i.ReservationId != reservationId && i.Reservation.ShowId == reservation.ShowId &&
seatIds.Contains(i.SeatId) &&
(i.Reservation.Status == ReservationStatus.Completed ||
(i.Reservation.Status == ReservationStatus.Started &&
DateTime.Compare(i.Reservation.Created, expired) >= 0)));
if (success)
{
//Remove last selected seats connected to the current reservation
db.ReservationSeats.RemoveRange(db.ReservationSeats.Where(i => i.ReservationId == reservationId));
//Add seats to the database
foreach (var id in seatIds)
{
db.ReservationSeats.Add(new ReservationSeat
{
Id = Guid.NewGuid(),
ReservationId = reservationId,
SeatId = id
});
}
}
db.SaveChanges();
scope.Commit();
}
return success;
}
As you can sse have we used the isolationLevel ReadCommited which we presume is pessimistic locking and will ensure that no conflicts ocure while adding the seats to ReservationSeats.
And i presume that we wouldt encounter any deadlocks since we only lock one table.
i understand that one way optimistic cuncurrency works is before you commit an update to the database you will check if the database has been altered since you retrieved the record. Could we have chosen to, before adding the seats(and reservationId) to the reservationSeats table do a check to see if they have been reserved for the show.
