Do I need a SQL lock or Transaction in a ticketing system?

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.

FYI:

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.

Thanks, Jeff
JEFFCECCHINIAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

momi_sabagCommented:
even if you use transactions, by default, sql server will use an isolation level that will not lock the rows that are returned to you, so the tickets won't be unavailable for other users
to change this behavior you need to change the isolation level of the transaction to repeatable read (or read stability - i don't remember the name in asp)

but, i don't this will be a good design since you are making a web application, locking the tickets for one user is not good, since if that user goes to the bathroom for 5 minutes, these tickets will be unavailable to all other users
0
ErezMorCommented:
all you need to do (and you mentioned thats your intention anyway) is to make the actual ticketing proccess inside a storedprocedure. this sp shouldnt use any counters for checking availability, right? it checks the actual base tables.
assuming that, your sp should return a Success or Fail value to the asp application (so even in cases where the user (and the asp application) "thought" there are available tickets, the sqlserver whose in charge decides if it is still so.
since your user's requests are stacked in que by the server, the return values can be trusted and no additional locks are needed (beside the one inside the sp!)
0
JEFFCECCHINIAuthor Commented:
Hi guys,

Thanks for your reply.

Let me clarify the steps that I plan to take.

1. User looks at a list of available events that is generated from an SQL query in a StoredProc Select * FROM ShowEvents WHERE OrderedQty < MaxQty. (No locking)

2. The user selects the event and enters in the Quantity of tickets, name, etc. then Clicks Order. At this point and SQL StoredProc deternines if there are really that quanty of tickets still available and if so adds that quantity to the OrderQty column of the event and also ads a row into the TicketRequests table that stores that order's particulars (ticketholder, EventId, Qty, etc.)

My question is what is the SQL for the StoredProc to insure that this Order takes place properly without a simultaneous request for tickets for the same Event colliding creating a messed up deal?

Thanks, Jeff
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

JEFFCECCHINIAuthor Commented:
ErezMor,

Pls read my post above and then these are specific answers to your specific questions.

No counters. The SQL column "OrderedQty" in the table "ShowEvents" is the running total.

In an update, I normally have the Stored Proc return the number of rows affected, so I was plannin gto do the same here. This way I know 0 would be failure and 1 would be sucess.

The queue you are referring to is something automatically done by SQL server? So there is no such thing as simultaneous requests?

You mentioned no other locks besides the one inside the SP. That's what I no nothing about. What should I done specifically in the SP?

Should there be a transaction that wraps the select to make sure there's enough tickets for the request and the update of the new running total of OrderedQty and the insert of the TicketRequest Row?

I don't have any experience in these area, so please give me some SQL. :-)

Thanks, Jeff
0
ErezMorCommented:
hi again
what i mean is: it's nice to have the application show a summarized count of available tickets, and tell the user how many tickets he may buy.
but inside your sp, whatever was told to the user is irrelevant. count the tickets themselves in the TicketRequests table again, then decide if it's still possible to buy new ones
and if the check results in enough tickets are still available, then add the tickets to TicketRequests  and only after a successful addition, update your counters (and not the other way around, as you suggested)
so true, there might be times when your application will say there are enough tickets, and the user will try to order them, but eventually will be denied (cause the sp will fail)
there's no problem with using the rows affected value as a measure for success
for the concurrency: since you'll be updating the base table itself, sql server can take care of concurrency (if request 1 got an ok and added new rows (in TicketRequests) then request 2 will get the new number of rows and act accordingly...)
so for the same single table, yes, sql server will take care of the "que" (what cannot be said for summary columns like OrderedQty)
hope i covered all your questions
0
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
If you don't have a need for data integrity then simply use NOLOCK or READ_UNCOMMITTED on all of your queries. If you do have a need for data integrity then use READ_COMMITED_SNAPSHOT isolation mode.
0
JEFFCECCHINIAuthor Commented:
Hi guys,

Thanks for the responses. I'm still not clear

ErezMor:
1. We're in agreement that the qty ordered has to be validated at time of updating the OrderedQty value in the ShowEvents table to insure that we're only allowing tickets to be ordered if there are sufficent quantity at time of execution.

2. Your statement wasn't clear to me if updating the OrderQty was a reliable thing with regards to concurrent requests or not.

3. Without an SQL example were communicating kind of loose and I need to sink my teeth into SQL that wil be a reliable approach that I can code.

Racimo: Yes, data integreity is important. It appears that you are referring a transaction.

I have no experience with transactions, but I did some further reseach, it seems that locks happen automatically inside of a transaction. Is that the case?

What do you both think of the approach I adapted from an online article?...please see code snippet.

Does it accomplish what I want to accomplish? If not what do I need to do specifically...SQL please.

Thanks, Jeff
(
       @ShowEventId int, 
       @CustomerEmail varchar(100), 
       @Qty int
)
 
BEGIN TRANSACTION
    INSERT TicketRequest (
       ShowEventId, 
       CustomerEmail, 
       Qty)
    VALUES (
       @ShowEventId, 
       @CustomerName, 
       @Qty)
 
    IF @@ERROR <> 0
       BEGIN
           /* Can I return 0 instead of raising an error and still 
           rollback? If so, how?*/
           RAISERROR('error occured while recording TicketRequest', 16, 1)
           ROLLBACK
       END
 
    UPDATE ShowEvents  
    SET QtyOrdered = QtyOrdered + @Qty  
    WHERE QtyOrdered <= MaxQty - @Qty  
 
    IF @@ERROR <> 0
       BEGIN
           /* Can I return 0 instead of raising an error and still 
           rollback? If so, how? */
           RAISERROR('error occured while adjusting QtyOrdered', 16, 1)
           ROLLBACK
       END
 
    /* if the update doesn't happen because there are not suffient qty, 
       then I want to rollback everything done so far and return a 0 
       value. Does this accomplish that?*/
 
    IF @@ROWCOUNT = 0 
       BEGIN
           /* Can I return 0 instead of raising an error and still 
           rollback? If so, how? */
 
           ROLLBACK
       END
    
COMMIT TRANSACTION

Open in new window

0
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
<<Racimo: Yes, data integreity is important. It appears that you are referring a transaction.>>
I am refering to database integrity.  Transactions is just a unit of measurement of database activity.

You need to understand what traditional isolation levels are.  Please read the following to understand better how they work

http://bartdesmet.net/blogs/bart/archive/2005/07/25/3077.aspx
0
JEFFCECCHINIAuthor Commented:
Hi Racimo,

I just read the article and it went into a bit greater deatil than the one I read to create the SQL above. It was informative in how lock affect things...thank you.

Here's the simple bottom line, I want to accomplish two things:

1.) I want to reliably update the QtyOrdered value if there's enough tickets available. From the article, I understand that an exclusive lock happens automatically on an update statement. Since determining if there's enough tickets available and updating the OrderedQty happens within that one UPDATE statement:

UPDATE ShowEvents  
    SET QtyOrdered = QtyOrdered + @Qty  
    WHERE (ShowEventId = @ShowEventId) AND (QtyOrdered <= MaxQty - @Qty)

Can I assume that this UPDATE statement will reliably run in sequency so that there is never a concurrency problem?

Meaning another request can't somehow update the QtyOrderd while this request is doing it's thing thus causing an incorrect value or a value that is greater than MaxQty?

2. Doesn't testing for error and rowcount as show in the original SQL (as corrected above in this post) account for if there's no tickets available or some other problem that my TicketRequest is not inserted, therefore maintaining data integrity?

Thanks, Jeff
0
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
<<Meaning another request can't somehow update the QtyOrderd while this request is doing it's thing thus causing an incorrect value or a value that is greater than MaxQty?>>
No.  The default isolation mode does not allow that.  A select statement always collects the last COMMITED version of the data.

<<2. Doesn't testing for error and rowcount as show in the original SQL (as corrected above in this post) account for if there's no tickets available or some other problem that my TicketRequest is not inserted, therefore maintaining data integrity?>>
I am not sure I understand your question.  Please rephrase.
0
JEFFCECCHINIAuthor Commented:
Ho Racimo,

I want to start out by thanking you for your help and persisting with me; I appreciate it.

<<Meaning another request can't somehow update the QtyOrderd while this request is doing it's thing thus causing an incorrect value or a value that is greater than MaxQty?>>
No.  The default isolation mode does not allow that.  A select statement always collects the last COMMITED version of the data
[JC] I need clarity: Your "no" means that I'm safe to assume that my UPDATE statement will process without fear of another simultaneous request causing the value to be incorrect or greater than the MaxQty? YES/NO

<<2. Doesn't testing for error and rowcount as show in the original SQL (as corrected above in this post) account for if there's no tickets available or some other problem that my TicketRequest is not inserted, therefore maintaining data integrity?>>
I am not sure I understand your question.  Please rephrase.
[JC] If the update statement erros for some reason or does not get updated because there are not enough tickets available, does my code properly deal with rolling back the TicketRequest?

Thanks, Jeff
0
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
<<Your "no" means that I'm safe to assume that my UPDATE statement will process without fear of another simultaneous request causing the value to be incorrect or greater than the MaxQty? YES/NO>>
My *No* means that UNTIL a COMMIT statement is issued on the transaction that includes your UPDATE statement, there will be an exclusive lock on the QtyOrdered and MaxQty columns.

<<If the update statement erros for some reason or does not get updated because there are not enough tickets available, does my code properly deal with rolling back the TicketRequest?>>
Yes.  The entire point of a transaction is to be rolled back, if not commited at some point in time or being explictely rolledback

hth.
0
JEFFCECCHINIAuthor Commented:
Hi Racimo,

Ok thanks for the claifications on the lock. That lock will prevent an incorrect value or a value that is greater than MaxQty from ever occuring right? YES/NO

It sounds like this SQL does do what I need it to do. Those two statements should excecute quickly so the lock duration should be very short.

Is my S.P. SQL the way that you would do it? If not how would you suggest?

Thanks, Jeff
0
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
<<Ok thanks for the claifications on the lock. That lock will prevent an incorrect value or a value that is greater than MaxQty from ever occuring right? YES/NO>>
YES.  Any update on the MaxQty value will either occur BEFORE the transaction begins or AFTER the transaction ends.  In-between there is no possible change.

<<Is my S.P. SQL the way that you would do it? If not how would you suggest?>>
I do not know enough about the application logic you are trying to achieve to suggest a better course of action.  Keepin mind that, appart from the logical aspect of it, isolation is a mechanism that involve resource overhead consumption and concurrency performance issues.  The best solution must take in consideration all these aspects to work. The only *safe* recommendation is to use the READ_COMMITED_SNAPSHOT isolation mode but that requires overhead on TEMPDB.  That is a complex topic.

HTH
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JEFFCECCHINIAuthor Commented:
Thank you for your help. I was looking for an SQL example of what to do which I did not receive, so therefore the B grade. Racimo's persistance in my questions to clarify his responses gave me just enough information to help me develop a solution for that I am grateful.
0
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
<<Racimo's persistance in my questions to clarify his responses gave me just enough information to help me develop a solution for that I am grateful.>>
Thanks.  I shall always be a promoter of the following idea

*Don't give people fish but tell them how to fish*
0
JEFFCECCHINIAuthor Commented:
HI Racimo,

I understand that concept, however an example is worth a thousand words and save a lot of time.

Thank you very much for the help, Jeff
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.