What level of isolation should I use to book a flight?

Question

I have a mssql flight reservation program. For reserving flights I want to be sure that I have to use an isolation or blocking level?

(this is sample code, my problem is the isolation level for this situation, not the redundancy)

My database has a table for inventory, for example:

Inventory Table ------------------------ id (Pk), FlightNumber, Total, Sold 

now, if someone wants to reserve a flight, I use this code in a 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 a question:

Q1: Should I use locks or isolation levels? Is there any advantage to being able to use one?

Q2: according to Q1 What insulation level or interlock should be used

Answer 1

If you want to see what level of isolation will make the sample code work in its current form, and not as the best way to solve the problem discussed in the sample code, you will need guarantees at least REPEATABLE READ.

Databases that use strict two-phase locking (S2PL) for concurrency allow READ COMMITTED transactions to drop shared locks at the end of each statement, or even earlier, therefore, between time transaction A, the availability and time it declares places are checked, someone else can go through transaction B and read again without causing a single transaction. Transaction A may temporarily block transaction B, but both will be updated and you may be resold.

In databases that use concurrency version control (MVCC) for concurrency, reading does not block writes, and records do not block reading. In READ COMMITTED, each statement uses a new database snapshot based on what is committed, and at least in some (I know this is true in PostgreSQL), simultaneous writing is allowed without errors. Thus, even if transaction A was in the process of updating the sold account or did not complete it, transaction B will see the old counter and continue updating. When he tried to perform the update, he could block waiting for the previous update, but after that he would find a new version of the line, check if it meets the selection criteria, update if it does, and ignore the line if not, and proceed to commit without errors. So again, you are oversold.

I assume that the answers are Q2 if you decide to use transaction isolation. The problem can be resolved at a lower isolation level by modifying the example code to accept explicit locks, but this usually results in a larger lock using an isolation level that is strict enough to handle it automatically.

Answer 2

You complicate things too much. All your inquiries can be replaced by:

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

If the flight is full, UPDATE will not be fulfilled (the second condition is not fulfilled), and it will return 0 changed rows. If so, then flight is flight. Otherwise, the request changes the value of Sold and returns the modified row 1 .

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

By the way, this request can be easily configured for arbitrary arbitrary reservation:

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

Answer 3

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

They are talking about a car rental application.

If you need a more restrictive isolation level, you can go to IsolationLevel Serializable. But be warned that this is subject to blocking and may affect your performance.