Ticket reservation is done on our site.

For each ticket, a record is stored in the database, which after booking, the ticket record becomes inactive and a record is created with the ticket and individual details.

After selecting the ticket by the user, it is checked that the ticket is active and then it is checked whether a record with the details of this ticket has been registered or not?

Unfortunately, in times when many users, ie more than 3,000 people, enter the site to book tickets, the check is not done properly and duplicate tickets are sold.

My Tickets model:

class tblTicket
{
        public long BaseID { get; set; }
        public string date { get; set; }
        public string time { get; set; }
        public bool st { get; set; }
}

My Receptions model:

class tblResevation
{
        public long Id { get; set; }
        public string name { get; set; }
        public string mob { get; set; }
        public string email { get; set; }
        public string date { get; set; }
        public string time { get; set; }     
        public <int> subject_id { get; set; }
} 

There is my code to check ticket before reservation :

if (context.tblTicket.Where(w => w.subject_id == id && 
                                 w.date == dateNow && 
                                 time == TimeNow && 
                                 w.st == False).Any())
{
    j.st = false;
    j.message = "Sorry, this ticket is not available";
    return Json(j);
}

And then check this thicket in reservation table :

if (context.tblResevation
           .AsNoTracking()
           .Where(w => w.subject_id == id && 
                       w.date == dateNow && 
                       w.time == timeNow).Any())
{
    j.st = false;
    j.message = "Sorry, this ticket reserved by another user";
    return Json(JsonConvert.SerializeObject(j));
}

If ticket is available and there is no reserve record, the ticket reserved by user and we update tblTicket and add a new record by ticket information in tblResevation.

CodePudding user response：

some thoughts (sorry for a lot of letters):

1. it may be worth creating a separate temporary entity linking (blocking) the ticket and the user who is trying to buy it ... for example, called TicketReservationAttempt

2. a unique index on two columns (UserId, TicketId)...if there is not user, just TicketId would do... is defined for TicketReservationAttempt, which will not allow creating duplicates

3. when you try to buy a ticket, such an entity is created, if you get an error creating a duplicate (there are error codes for such error), then someone else is trying to buy a ticket. also in which case the record can be deleted so that someone else can buy the ticket if the original buyer still canceled the purchase

4. after paying for the ticket, already create tblResevation. There may be some time between ticket selection and payment. that is, a simple ticket selection does not oblige to create tblResevation

it makes sense to come to a more atomic operation without a separate request to check the availability of a ticket and just to avoid duplicates

so that the check is not responsible for the booking, it can only be to avoid doing a more complex operation and obviously getting an error, but still not desirable. creates a slight delay and the user may be late to block the ticket for themselves

and ... for example, I chose a ticket, went to pay for it, and if the ticket is not blocked for me, then after payment it may turn out that someone else has a ticket the other managed to pay earlier, but I can also cancel the payment and the ticket should again become available to others ... these operations will be provided by the creation and removal of a TicketReservationAttempt

CodePudding user response：

EF and SQL Server can help with this, but your current data structure makes it a bit difficult where tickets and reservations are not directly related.

The first thing would be to configure your DB and EF to work with a concurrency token. This is generally a Timestamp column in SQL Server that EF can observe for changes. This prevents you from overwriting data if that data has changed since the entity was loaded.

So as a simple example, Ticket would have a Nullable ReservationId and a RowVersionNumber to serve as the concurrency token. When a user loads available Tickets to make a reservation, you load the data and be sure that the entity has the RowVersionNumber as part of the entity, and that it is configured as the concurrency token:

The first step is to create a Reservation. This reservation would have a state of "Pending" or such meaning it hasn't been confirmed. The system should monitor for any Pending reservations that expire and remove them.

Once a reservation is created, we can search for and associate tickets:

var tickets = _context.Tickets.Where(t => !t.ReservationId.HasValue) // plus any other criteria...
    .ToList();

When they go to lock in a ticket you set the ticket's ReservationId to our pending reservation and SaveChanges(). If in that moment the ticket had been reserved by another session, the save would fail given the concurrency token will have changed. (SQL server updates it on any Update) That ticket is now reserved and the user can confirm their reservation, or cancel it. The OnDelete rule should set any Ticket.ReservationId to #null when a Reservation is deleted.

You will have to handle the rare exception that a ticket comes back as unreserved and happens to get reserved in the time the user takes to select it. That could be seconds or minutes. In these cases the users will need to get a fresh list of available tickets.