Re: Trigger Timeout Loop Issue

From: joel garry <>
Date: Wed, 27 Aug 2008 13:54:34 -0700 (PDT)
Message-ID: <>

On Aug 27, 11:08 am, Julian Mensch <> wrote:
>   Hi, folks,
>   We've developing a interface between our Oracle database
> and some third-party applications, and we've run into a locking
> issue I was hoping someone here might be able to help with.
>   In our database code, we have an AFTER trigger on a specific
> table that uses the UTL_HTTP library to call a method of a web
> service (written in C#). The web service then in turn calls a
> stored procedure in the database which modifies the same
> record in the same table (albiet a different column). However,
> since the record is locked by the trigger, the stored procedure
> delays until the trigger is complete, creating a deadlock-like
> feedback loop that lasts until UTL_HTTP times out waiting for
> the SOAP response message -- only when the trigger finishes
> processing will the stored procedure (and thus the web service)
> return.
>   One possible solution to this issue is to use multithreading in
> the web service -- fork the thread, return from the method to send
> the SOAP response and /then/ call the stored procedure in a
> thread that can afford to wait for it to finish. But this is awkward,
> indirect and inelegant coding, and adds explicit multithreading to
> a project that doesn't otherwise need it.
>   We (myself, the C# guy, and our senior dev, the Oracle guy)
> were wondering if there was any way to solve this more "cleanly"
> in terms of database best practices. Specifically, is there any
> way we can set the AFTER trigger to execute only once the
> table update is truly complete and the table has been fully
> unlocked? (There is no chance that the trigger will have to
> rollback the transaction at all). Or is there a way to manually
> unlock the locked part of the table within the trigger, before
> calling the web service?
>   Any insight offered is appreciated!
> -- Julian Mensch

Well, I don't think there is "no chance" (what if the network/node/db crashes? what if a performance consultant runs a script that pisses off your web call timeout handling?), but I have a vague notion that you could spawn an autonomous transaction that attempts to select for update on the stuff you updated, looping for a while if no data found (that is, while it tries before you commit). You'd need logic to account for the various errors you'd get at the various times. Come to think of it, that sounds like reinventing multi-threading. Maybe your project needs it.


-- is bogus.,com_remository/Itemid,53/func,select/id,1/
Received on Wed Aug 27 2008 - 15:54:34 CDT

Original text of this message