Do I need a SQL lock or Transaction in a ticketing system?
Posted on 2009-07-02
I have the following questions for an ASP.Net web app w/ MS SQL Server 2005 Express db:
Q1. What is the best SQL (to be in a stored Proc) to verify that the Qty of tickets being ordered are in fact available at the time the order request is being added?
Q2. In Q1 would I use a transaction, locking, or both.
I am creating an ASP.Net (web based) ticketing system. I have a Table "ShowEvents" where each row in this table is a different Date/Time of a Show that people will order tickets for. There is a field that keeps track of the maxium number of tickets available (MaxQty) and a field that keeps track of the number of tickets ordered (OrderedQty).
The "ShowEvents" table will be accessed in three different ways:
1. To list available "ShowEvents" for ticket order. Meaning display those events where the "OrderedQty < MaxQty" as well as some other column values.
2. Increase/Decrease the "OrderedQty" each time a ticket is ordered/cancelled.
3. Management Reports for each ShowEvent.
A.) There is a column in the "ShowEvents" table called "ShowEventId" that is the type Integer and is the PK and an auto-incrementing identity.
B.) There is a table called "TicketRequests" that stores the completed order.