Re: Changing isolation level?

If the business need is to provide real-time inventory (or real-time whatever) when the customer intent is to purchase, you don't have much choice but to serialize the access. SELECT... FOR UPDATE does this. Obviously all accesses will have to use the same mechanism and you would want to use FOR UPDATE with discretion. You also don't have to block forever, since the WAIT clause allows you to specify an optional wait interval or none to raise an exception immediately

The app. design decisions will be driven by the business. If the business promises its' customers accurate, up to date, real-time inventory, than that's how you design the app. You could use SELECT FOR UPDATE with no wait so that an error is immediately raised. The app could then perform a SELECT without FOR UPDATE and inform the customer that although we currently show n turbo-widgets in stock we can't promise availability but would be happy to take your order and confirm shortly afterwards.

If the business promises near real-time inventory, you'll design the app the way most web sales works. You place an order, receive an order number immediately and some time later receive either a confirmation of your order or an apology (hopefully with a discount coupon for their trouble.)

If Mrs. Miggins is about to embark on a long journey to pick up her turbo-widget, I'd rather lose the sale by informing her that I can't promise her the item than lose the cusomter by telling her that we do have it only to have her show up two hours later and find that in fact we don't

That said, I fear that I've strayed way, way off the OP's original question!

     David

On Thu, 4 Dec 2003 15:01:34 +1100, "Howard J. Rogers" <hjr_at_dizwell.com> wrote:

>
>"D Alpern" <ivorykeyer88ns_at_nshotmail.com> wrote in message
>news:o2issvovpglsvpkbh0h0fj7alr4qoaq4g9_at_4ax.com...
>> SELECT... FOR UPDATE {WAIT | NOWAIT} springs to mind...
>
>I suppose that could help. I select for Mrs. Miggins, and then hang because
>Miss Marple's updates are pending. At least I could tell Mrs. Miggins that I
>couldn't tell her the stock state.
>
>One sale lost, I think.
>
>Regards
>HJR
>
>
>
>
>
>> David
>>
>> (rm ns beside @ in return email address)
>>
>> On Thu, 4 Dec 2003 05:59:46 +1100, "Howard J. Rogers"
>> <hjr_at_dizwell.com> wrote:
>>
>> >
>> >Time to don the Devil's Advocate costume, I think.
>> >
>> >I can think of lots of cases where it would be rather unfortuate to see
>an
>> >old piece of data just prior to it being changed. Rather than see the old
>> >data, I could well imagine that it would be more helpful to see nothing
>> >until the situation stabilises.
>> >
>> >If I have a customer demanding to know whether I have a turbo-widget in
>> >stock, I can select from my products table to check. It tells me I have
>one
>> >such item left. Mrs. Miggens therefore, on the strength of that,
>undertakes
>> >the arduous two-hour journey from remote rural New South Wales into the
>City
>> >Centre to pick up said turbo-widget. Unbeknownst to her, however, the
>> >information I gave her was wrong, because at the time I did my query,
>Fred
>> >in the next cubicle was selling the same product to Miss Marple. He
>hadn't
>> >committed the transaction, which is why I saw a stock level of 1. But it
>> >would have been nice not to give the wrong info out to Mrs. Miggens. I
>> >suppose I could have badgered her into placing an order, because then my
>> >attempt to update the stock level myself would have failed. But Mrs.
>Miggens
>> >ran into a bit of credit rating trouble a while back, and doesn't have a
>> >credit card. The most she can do on the phone is to enquire.
>> >
>> >There are two choices there, I suppose: see nothing at all (the blocking
>> >idea). Or see a stock level of 0 (the dirty reads issue beloved of, er,
>> >certain other products). Oracle's long-time answer has been: you'll see
>1,
>> >and only 1, and nothing else, ever... which some might consider to be a
>lack
>> >of choice in answering technology.
>> >
>> >Regards
>> >HJR
>> >
>> >
>> >
>> >
>>
>
Received on Wed Dec 03 2003 - 22:47:58 CST