Re: Changing isolation level? ++ scenarios

| >No matter what isolation level you
| > use, you will still need to solve the problem of over allocation of
| > finate resources with a suitable business practice.
|

lots of good discussion, but no one has yet addressed the basic issue: no matter what isolation level is available, data can change immediately after the read

if we're talking OLTP, which i think we all are, the data change continually, so it's really a matter of timing -- one isolation level tells me i've got 30, the other tells me i've got 50, but neither figure is reliable after it is read unless i decide to lock something:

consider the following scenarios:

1. read-dirty scenario

01:01:30 30 ixixs shown available (20 are pending commit) 01:01:31 i'm trying to sell 25, pending order commits while i'm negotiating
01:01:32 bob sells 15 ixixs
01:01:33 i try to sell 25, but my stock level figures are no longer accurate, so my sale fails

2) read-committed scenario

01:01:30 50 ixixs shown available (20 are pending commit) 01:01:31 i'm trying to sell 25, pending order commits while i'm negotiating
01:01:32 bob sells 15 ixixs
01:01:33 i try to sell 25, but my stock level figures are no longer accurate, so my sale fails

3) writers block readers scenario

01:01:30 ?? ixixs shown available (20 are pending commit -- i can't see anything)
01:01:31 pending order commits, i get a true value of 30, which i try to sell
01:01:32 bob sells 15 ixixs
01:01:33 i try to sell 25, but my stock level figures are no longer accurate, so my sale fails

4) readers issue for-update locks while checking levels

01:01:30 ?? ixixs shown available (20 are pending commit -- i can't see anything)
01:01:31 pending order commits, i get a true value of 30 and release my lock, and then try to sell
01:01:32 bob sells 15 ixixs
01:01:33 i try to sell 25, but my stock level figures are no longer accurate, so my sale fails

5) readers issue and hold for-update locks

01:01:30 ?? ixixs shown available (20 are pending commit -- i can't see anything)
01:01:31 pending order commits, i get a true value of 30 and hold my lock (in a stateless web-app?), and then try to sell 01:01:32 bob tries to sell 15 ixixs, but can't because i've got the inventory locked
01:01:33 i sell 25, but everybody in the universe had to wait

more realistically, regardless of the isolation level, we have functionality to

1. check inventory levels ('i show we currently have 30 available')
2. reserve inventory ('if you'd like i can reserve those for you until 2:00')
3. sell inventory ('these are going fast, you'll need to place your order now to guarantee it')

-- 
Mark C. Stock
mcstock -> enquery(dot)com
www.enquery.com training & consulting