Which Isolation level should i use for booking flight

Question

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

Answer 1

If you're looking to see what isolation level will make the sample code work as it stands, rather than what is the best way to solve the problem addressed by the sample code, you would need the guarantees of at least REPEATABLE READ.

Databases which use strict two-phase locking (S2PL) for concurrency allow READ COMMITTED transactions to drop shared locks at the completion of each statement, or even earlier, so between the time transaction A checks availability and the time it claims the seats, someone else could come through with transaction B and read again, without causing either transaction to fail. Transaction A might block transaction B briefly, but both would update, and you could be over-sold.

In databases using multi-version concurrency control (MVCC) for concurrency, reads don't block writes and writes don't block reads. At READ COMMITTED, each statement uses a new snapshot of the database based on what has committed, and in at least some (I know this is true in PostgreSQL), concurrent writes are resolved without error. So even if transaction A was in the process of updating the sold count, or had done so and not committed, transaction B would see the old count and proceed to update. When it attempted the update, it could block waiting for the previous update, but once that committed, it would find the new version of the row, check whether it meets the selection criteria, update if it does and ignore the row if not, and proceed to commit without error. So, again, you are over-sold.

I guess that answers Q2, if you choose to use transaction isolation. The problem can be solved at a lower isolation level by modifying the example code to take explicit locks, but that will usually cause more blocking that using an isolation level which is strict enough to handle it automatically.

Answer 2

You are overly complicating things. All your queries can be replaced with:

Update inventory
set Sold = Sold + 1
where FlightNumber = 'F3241b'
AND Total - Sold > 0  -- Important!

If the flight is full, the UPDATE won't take place (the second condition is not met) and it will return 0 modified rows. If this is the case it means the flight is full. Otherwise the query modifies the Sold value and returns 1 modified row.

In this case any isolation level is fine because a single query is always atomic. This is somewhat similar to optimistic-locking.

BTW this query can be easily tuned to allow arbitrary number of reservations to be made atomically:

Update inventory
set Sold = Sold + @seats
where FlightNumber = 'F3241b'
AND Total - Sold >= @seats

Answer 3

See this link that explains SNAPSHOT ISOLATION level in SQL Server. http://msdn.microsoft.com/en-us/library/ms345124(v=sql.90).aspx

They talk about a car rental application.

If you need a more restrictive isolation level you could move to IsolationLevel Serializable. But be warned that this is prone to locking and might affect your performance.