Re: Changing isolation level?

While stranded on information super highway Howard J. Rogers wrote:
:)
:)"mcstock" <mcstock @ enquery .com> wrote in message
:)news:OYydnYF8d4uguVCiRVn-sA_at_comcast.com...
:)> "Hemant Shah" <shah_at_typhoon.xnet.com> wrote in message
:)> news:bqj76t$f47$1_at_flood.xnet.com...
:)> | While stranded on information super highway mcstock wrote:
:)> | :)refer to the SET TRANSACTION statement in the SQL Reference manual
:)> | :)
:)> | :)relevant options are: ISOLATION LEVEL SERIALIZABLE and ISOLATION LEVEL
:)> READ
:)> | :)COMMITTED (default)
:)> | :)
:)> | :)doesn't look like ISOLATION LEVEL SERIALIZABLE does what you want
:)> | :)
:)> | :)
:)> | :)you'll also want to carefully review the sections on Data Concurrency
:)> and
:)> | :)Consistency in the Concepts manual
:)> | :)
:)> |
:)> |
:)> | As others have mentioned, none of the isolation level will block
:)readers.
:)> | I will have to find some other way.
:)> |
:)>
:)> why do you need to block readers?
:)> what is the business benefit?
:)> is it just to make oracle look like DB2?
:)>
:)> if you can describe a business benefit and the required behavior from a
:)> application functionality standpoint, i'm sure a number of folks can come
:)up
:)> with suggested approaches.
:)
:)
:)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
That was a good example. We are running into similar situation. Several processes are working simultaneously, they find out what to work on by reading a row from a table and deleting it.

First transaction reads a row and deletes it, the changes has not yet been commited.

Meanwhile the second transaction tries to read a row and get same row again, it has not yet deleted the row.

First transaction commits the changes.

When second transaction tries to delete the row it gets error.

This is a part of big application and has worked with VSAM and DB2 where they lock the rows once someone updates or deletes it but has not yet commited.

I may be wrong here, but if the row has been (marked for) updated or deleted and another transaction get old value then I consider it a dirty read.

This may be O.K. in data warehouse but is not acceptable in online transaction.

Imagine in a banking environment, you are withdrawing money from an account at the same time your spouse at another ATM is also trying to withdraw the money and he/she gets wrong balance.

:)
:)
:)
:)
:)

-- 
Hemant Shah                           /"\  ASCII ribbon campaign
E-mail: NoJunkMailshah_at_xnet.com       \ /  --------------------- 
                                       X     against HTML mail
TO REPLY, REMOVE NoJunkMail           / \      and postings      
FROM MY E-MAIL ADDRESS.           
-----------------[DO NOT SEND UNSOLICITED BULK E-MAIL]------------------
I haven't lost my mind,                Above opinions are mine only.
it's backed up on tape somewhere.      Others can have their own.