March 16, 2012 at 8:41 am
I have a flight reservation program use mssql ,For reserving flights i want to be sure should i use isolation level or locks?
(this is a sample code,my problem is Isolation Level for this situation not do the reservation)
My Database has a table for inventory like:
Inventory Table
------------------------
id (Pk),
FlightNumber,
Total,
Sold
now if some want to reserve a flight,i use this code in transaction
Decalre @total int;
Decalre @sold int;
Select @total=Total,@sold=Sold From Inventory where FlightNumber='F3241b';
IF @total-@sold > 0
BEGIN
Update inventory set Sold=Sold+1 where FlightNumber='F3241b';
PRINT 'Reserve Complete'
END
ELSE
PRINT 'this flight is full'
i have these Question:
Q1: Should I use Locks or Isolation Levels?does it have any benefit for perfomance to use one?
Q2: according to Q1 Which Isolation Level or Lock should i use
March 16, 2012 at 9:09 am
You could use a an UPDLOCK table hint on the select statement or you could use SERIALIZABLE as the Isolation LEVEL. I honestly don't know which one will work best. I think the UPDLOCK hint is better for concurrency, but would still keep you from over-booking. See this for table hints, http://msdn.microsoft.com/en-us/library/ms187373.aspx and this for isolation levels http://msdn.microsoft.com/en-us/library/ms173763.aspx
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 16, 2012 at 9:21 am
I have couple other recommendations that might help as well. I'd code this something like this:
IF EXISTS(Select 1 From Inventory where FlightNumber='F3241b' AND total >= sold + 1)
BEGIN
UPDATE
inventory
SET
Sold = Sold + 1
WHERE
FlightNumber = 'F3241b';
PRINT 'Reserve Complete'
END
ELSE
PRINT 'this flight is full'
I'd also recommend adding a CHECK CONSTRAINT on the Sold column that enforces that Sold <= Total. Then even ifyou get a situation where concurrent transactions are updating the same flight, the last one in will get a constraint violation which you could then handle either in the stored procedure that does the update or in the application.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 19, 2012 at 8:38 am
Hi, Jack.
Great suggestions there!
But why do you need to use an IF block?
Wouldn't this work as well?
UPDATE inventory
SET Sold = Sold + 1
WHERE FlightNumber = 'F3241b'
AND total >= sold + 1;
Best regards,
Andre Guerreiro Neto
Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA
March 19, 2012 at 9:26 am
codebyo (3/19/2012)
Hi, Jack.Great suggestions there!
But why do you need to use an IF block?
Wouldn't this work as well?
UPDATE inventory
SET Sold = Sold + 1
WHERE FlightNumber = 'F3241b'
AND total >= sold + 1;
You have to have an IF block somewhere to let the calling application know that either the flight was booked or not. So with just and update you'd have to do an IF @@ROWCOUNT = 0 then 'Filght Full' Else 'Flight Booked' type of thing.
If the CHECK CONSTRAINT I mention is added then your application could "just" handle the constraint error and present a message when that error is returned as well. I think the preference here is to not get an error, but verify the business logic prior to do the update.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply