Re: changing the isolation level

Marten Lehmann wrote:
> > The data changed by a session in Oracle can never been seen by another
> > session until it is committed. Why doesn't the session making the
> > change perform the verification test to begin with?
>
> Because it are two different applications. Currently, they are both
> written in Perl, but one of them is about to change to Java. So
> session-sharing is not possible.
>
> Our current process is easy: The first process that changes data is left
> open, the transaction is neither commited nor rolled back. Then I can
> perform tests on the data (and I know by design, that noone else is
> using the "dirty" data at the same time). If I'm happy with it, I commit
> the changes. If not, I will rollback them.
>
> Think of a script, that generates invoices montly. Lets say you have a
> average volume of $1000 dollar. So if a run of the script in the next
> month just returns a volume of $200 you can be pretty sure, that
> something has gone wrong. But if you cannot see this until you confirmed
> all the stuff, then it is too late to revoke it.

Then in ORACLE use a staging table.

the load application loads the staging table and commits. (it can even exit at this point.)

the validation application reads the staging table and approves the changes by inserting them in the final tables, or disapproves (maybe generates error reports?), and finally deletes the rows from the staging table.

>
> Sybase supports all levels of isolation, so when I read that Oracle
> doesn't allow a certain isolation level it just sounds like a lame
> excuse for a missing feature. It should be up to the user which
> isolation level to use and if I want to read "dirty" data, then why not?

I have used other systems and databases that allow reading dirty data. I have not found any good to come of it in the long run. The socalled performance advantage of reading the data early, before commit, is soon lost in the synchroniztion needed when the data must be commited. So there are good reasons why not to allow dirty reads.

>
> Regards
> Marten

And you should have little trouble merging those apps together into one if the PERL code is written well. If you really want two "sessions" the staging table is the way to go and still requires very little code change.

have a good day.

   Ed Received on Mon Jan 15 2007 - 12:23:13 CST