Re: Infinite loop, autonomous transactions, jobs...
Date: Tue, 12 Oct 2010 10:09:04 -0700 (PDT)
Message-ID: <d8794fe8-e223-437c-9cea-701197252559_at_p19g2000pra.googlegroups.com>
On Oct 12, 7:14 am, Tiago <diariodastril..._at_gmail.com> wrote:
> On Oct 12, 10:01 am, Mark D Powell <Mark.Powe..._at_hp.com> wrote:
>
>
>
> > Vendors often have no idea of how to properly design the database
> > usage portion of their application.
>
> Even worse is when they are most used to MS Sql Server...
My crystal balls are cracking together, they know what will happen...
>
> > How are the inserts done, single row at a time or bulk? You would not
> > want to use row level triggers if direct load operations are used to
> > insert the data but if single insert statements are used the trigger
> > should probably not be an issue though I guess if would depend on
> > exactly what the trigger does.
>
> Now even the guy who is here to implement the thing knows. What I know
> is that the application is multi-db enabled. It doesn't look java-ish,
> but I can be wrong. .NET perhaps? Have no idea. It connects using a
> string similar to the jdbc one.
>
> The trigger would parse the rows and treat them as "messages". The
> first column is the "message identifier" and upon the identifier, the
> Oracle app will have to do different things, but in essence, it will
> validate the data and if correct, insert it on the application
> tables.
>
>
>
> > How does the infinite loop process the data? That is, does it use
> > select for update? Does it delete the rows when it processes them?
> > How does it know the data is new or already processed?
>
> There is a column that the external app sets as "0". My job is setting
> it as "1" when I pick the record and set as "2" when I finish. If
> there are finished records with "1", that means that the record wasn't
> properly processed. Vendor says it is best if I delete them "improve
> processing speed" he says. There is no select for update.
As a fellow who used to post here used to say, your application is going to hell.
You need to understand how oracle handles multiversioning and optimistic writes. I can predict without seeing your code that you will have severe performance problems, as recursive undo tries to keep all this straight, and your object segments swiss cheese from your delete craziness. Not to even mention strangeness from the tool, since which tool is unknown - but some of them do extremely poor things, like make a new connect for each statement, or oh-so-helpfully try to undo Oracle's transaction model.
On the other hand, I wrote an app that does similar things with a couple of flags, it works most of the time, but still has some curious bugs having to do with when things commit. Fortunately, those (losing a few seconds here or there) are far overshadowed by human errors (letting things go on for hours that they should have stopped, scanning wrong barcode, odd requirements, etc.). It's volume isn't particularly high anyhow, and it doesn't delete (so far). Not deleting has saved some peoples butts, and allowed for some analysis not originally envisioned.
>
>
>
> > Since the logic is getting into what appear to be hung situations have
> > you ran a trace on the process? This may provide you will a clue to
> > what is going wrong and how to fix it.
>
> I didn't try the trigger, because vendor said another if his customers
> tried it, on Oracle, and there were problems because while the trigger
> was being processed, the external app wasn't able to insert new rows
> and that caused them many problems, then I suggested that the trigger
> to call different procedures with pragma autonomous transaction, one
> procedure for each message type... What I am afraid is that on test
> environment I won't be able to reproduce a high load usage and may not
> face the locks the vendor mentions. When this thing go to production
> it can't go wrong, this is a critical operation and theoretically
> should go live without issues...
You need to go buy a Tom Kyte book, understand how to do autonomous transactions correctly, and maybe google on the things Tom Kyte wishes had never been added to Oracle because nearly everyone does it wrong. Theoretically, it could work. http://www.oracle-base.com/blog/2006/02/28/autonomous-transactions/ http://www.orafaq.com/node/1915 http://lmgtfy.com/?q=autonomous+transactions+tom+kyte
You can't even theoretically go live without issues without serious load testing. Come on!
jg
-- _at_home.com is bogus. http://www.montrealgazette.com/news/technologies+confuse+reality+fiction+Pope/3643380/story.htmlReceived on Tue Oct 12 2010 - 12:09:04 CDT