Re: Changing isolation level? ++ scenarios

vslabs_at_onwe.co.za (Billy Verreynne) wrote in message news:<1a75df45.0312100137.6c04ac23_at_posting.google.com>...
> rgaffuri_at_cox.net (Ryan Gaffuri) wrote in message
>
> > > The bottom line IMO is information. Provide the business with
> > > information in order for them to make their decisions. Isolation
> > > levels are not an issue - not if you use Oracle correctly and not if
> > > you design your app & database correctly.
> >
> >
> > oh wait, when you query the data, cant you also query the transaction
> > number on the rows involved? then check that against the transaction
> > number at the time of performing DML? This will probably involve
> > hitting v$ views, so you need to test for latch contes ntion. Not sure
> > how else forms would manage this... and if forms does it, then its
> > doable.
>
> Whoa. I never mentioned v$ views or anything remotely like that.
>
> What I am saying is that this thread is jumping the gun about
> technical issues and perceived problems WITHOUT knowing *what*
> business requirements to meet (as sketched in the scenario).
>
> Fact. If two or more users want to access the exact same piece of
> data, there will be serialisation.
>
> Business requirements will state the when and why this is needed. The
> technical challenge is how to do it - using minimal serialisation.
> That challenge is met by database design and app design. Not hacking
> Oracle internals like v$ views to make it work.
>
> Some more rambling on this issue...
>
> Rigid db design and strict adherance to formal design methodologies is
> IMO stupid. You make it fit your needs instead of trying to fit your
> needs to it.

Not so much stupid, as expensive. If you don't reengineer (or at least reexamine) your needs, you can wind up doing some incredibly stupid automation of outdated procedures. If you do reengineer, you can get bogged down forever. Finding a proper middle ground is a strategic planning matter.

>
> Simple example. Rewind. 80's. Transactions on inserting & selecting
> invoices from a database, given the business volume, exceeds the i/o
> capability of the mainframe platform. Upgrading the mainframe is not
> an option due to cost. What do you? You denormalise the invoice header
> & invoice line tables into a single 2nd normal form table and change
> an average of 18 logical i/o's per invoice into a single logical i/o.
>
> Not purist? Right. But a real world solution for real world problem.
> Ditto for the technical problems raised in this
> sales-product-inventory scenario.

And Daniel is completely right here, as the denormalization is carried into the Oracle port. Seen that one, many times.

>
> But *before* going down the merry technical path and isolation levels,
> a *clear* problem definition is needed with a *clear* requirement
> specification. And IMO hacking isolation levels in context of the
> scenario given is not needed - you design the db and app to cater for
> these challenges instead.

And that is the proper answer, but incomplete. Think of the difference in cost between "clean-sheet properly designed large Oracle project" and "hack this thing so it works in Oracle." As has been pointed out, it _could_ be much more expensive to hack, but in general it isn't, and quick and cheap is good enough from managements viewpoint. I've seen some fairly ingenious methods for these hacks, too, sometimes it actually works.

If you were moving tomorrow, would you buy a kit trailer or rent a beat-up truck?

And sometimes business silliness is actually a good answer. Think of the person refinancing their house to pay off credit card bills. They don't know exactly when the refinance will happen, so they have to keep paying the bills and mortgage and insurance and taxes. Some of those will be double paid. So is it incumbent on any of the payees not to be double paid? Hence you get the OP's mortgage payment application question issue when two different payments arrive at the same time for the same thing.

jg

--
@home.com 
"I've got a very simple rule. You can let it in. There's your
simplicity." - Justice Sandra Day O'Connor on fruit of the poison tree
evidence.