Synchronising c#/asp.net booking application

Question

I'm developing an event booking application and am having difficulty figuring out how to manage the booking process. I know about db transactions and a little bit about locking but I have a lot of business rules to validate before a booking can be committed and I'm worried about performance bottlenecks.

Here's a summary of what's going on:

• An Event has a maximum number of slots
• A user can book one spot in the Event
• Each user has an account with money in and each event costs a certain amount

Given the above parameters, the following business rules are what I need to validate for a booking to take place:

• The user hasn't already booked a spot for this Event
• The user has enough funds to book the Event
• The Event has at least one spot available
• The Event does not conflict with other events the user has booked (not so much of an issue as I can check this when displaying the page and hide this Event from the user)

My main worry is that if I pull all the information from the db up front (i.e. Event, User, Account, and existing Bookings) that by the time I run all the validation and come to commit the new booking, the state of the system will have possibly changed (i.e. someone else has booked the last spot, money has left my account, etc).

If I was to lock the code/database tables around this booking process, then I've potentially (??) got the lock for quite a while affecting other operations in the system and causing performance issues at peak times.

Can anyone suggest an approach whereby I can manage or at least limit these concerns.

I'm building an asp.net app in c# and using sql server 2005.

Answer 1

I think a good example to look at is how Ticketmaster reserves seats for tickets that MAY get purchased. They tell you that you have so many minutes until the seats are put back into inventory. It pushes the purchaser to make a decision or someone else will have a chance at the seats. This is really your biggest hurdle. As for checking the business rules, you'll have to do that. There is no magic around what needs to be done there. If you need the data to validate a rule then that's what you need to do. With some proper mapping and outlining you can find the efficiencies. I hope that answered your question.

Good luck!

Answer 2

One solution:

1. Pre-emptively book the spot (with a status of "hold").
2. Validate.
3. If the booking can't be kept due to business rules, delete it. If not, change status to "booked"

Answer 3

If you go back to the 80s and read literature published on the topic of transaction processing you'll find that one of the most discussed example was the airline reservation systems. And for good reason, as it was one of the OLTP topics that exposed all the issues around transaction processing: correctness, troughput, contention, deadlocks. What you describe is a very similar problem, but instead of air flight seats you have event slots. So yes, you will have all those issues.

There is no magic pixie dust. This is a hard problem. But there are some guiding lines:

• Forgetful Fred cannot lock a slot for ever. Forgetful Fred is the user that opens the reservation screen, picks a seat, then goes to lunch without finishing the transaction. If this is allowed, then the system will slowly 'leak' slots that aren't used
• Database locks are too expensive to be held while waiting for user input.
• Throughput can only be achieved with granular locks.
• The business logic should not attempt concurent updates on correlated items.
• Everything displayed to the user should be treated as 'tentative'.
• The user interface should be prepared to handle update conflicts.
• The update logic should always follow the same hierachy (eg. if the agreed update logic is Account->User->Event->Booking then a rogue transaction trying to update Booking->Event->User will cause deadlocks).

And as a matter of fact there is an approach that limits these concerns: workflow processing backed by transactional queues that leverage correlated items exclusive lock out. Not your everyday ASP task for sure, so I'd recommend you stick with what you know.