Re: Infinite loop, autonomous transactions, jobs...

From: Tiago <diariodastrilhas_at_gmail.com>
Date: Tue, 12 Oct 2010 07:14:00 -0700 (PDT)
Message-ID: <ff3c4cdc-2836-465c-bfaf-9bc2dbb391ce_at_e14g2000yqe.googlegroups.com>



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...

> 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.

>
> 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...

Thanks very much for you help and insight!

best regards,
Tiago Received on Tue Oct 12 2010 - 09:14:00 CDT

Original text of this message