Solved

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

Posted on 2009-07-02
17
421 Views
Last Modified: 2012-05-07
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
0
Comment
Question by:JEFFCECCHINI
  • 8
  • 6
  • 2
  • +1
17 Comments
 
LVL 37

Expert Comment

by:momi_sabag
ID: 24769818
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
 
LVL 12

Expert Comment

by:ErezMor
ID: 24770176
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
 

Author Comment

by:JEFFCECCHINI
ID: 24770224
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
 

Author Comment

by:JEFFCECCHINI
ID: 24770277
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
 
LVL 12

Expert Comment

by:ErezMor
ID: 24770776
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
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 24771825
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
 

Author Comment

by:JEFFCECCHINI
ID: 24773474
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
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 24773518
<<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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

Author Comment

by:JEFFCECCHINI
ID: 24773737
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
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 24773768
<<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
 

Author Comment

by:JEFFCECCHINI
ID: 24773811
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
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 24773879
<<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
 

Author Comment

by:JEFFCECCHINI
ID: 24773979
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
 
LVL 23

Accepted Solution

by:
Racim BOUDJAKDJI earned 500 total points
ID: 24774029
<<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
 

Author Closing Comment

by:JEFFCECCHINI
ID: 31599456
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
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 24774126
<<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
 

Author Comment

by:JEFFCECCHINI
ID: 24774334
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

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now