Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> 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:
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
-- Mark C. Stock mcstock -> enquery(dot)com www.enquery.com training & consultingReceived on Thu Dec 04 2003 - 07:54:11 CST