Trigger Timeout Loop Issue

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

On Wed, 27 Aug 2008 11:08:00 -0700 (PDT), 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

One would question what you are doing in the web service, which can
not be accomplished by
- (preferably) PL/SQL
or
- calling the code in an external procedure.

What you have set up now is extreemly inflexible, extreemly
unscalable, and extreemly unrobust.
One would wonder why your senior dev didn't recommend to keep it in
Oracle as much as possible without this cr**p.


The remedy you propose is even worser than the problem at hand.

--

Sybrand Bakker
Senior Oracle DBA

On Aug 27, 2:08 pm, 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

Hi Julian,

I think the problem is you are trying to shake your own hand.

My guess is you have

app1 --> update table1 --> trigger1 fires (transaction outstanding) --
> call webService1 via utl_http to do something in a faraway land -->
if last step succeeds --> update table1 status column to
"partner_processed" or whatever.

If so, I think you have design issues, as Sybrand mentioned.

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

The issue is you have an outstanding transaction on the row you are
trying to update, but the trigger is running in a different session
(with a transaction blocking your second session). Can't you get a
return code from the web service and update the (what I will call)
status column from within the same session (rather than invoking the
stored procedure from another session in the middle tier)?

Regards,

Steve

> One would question what you are doing in the web service, which can
> not be accomplished by
> - (preferably) PL/SQL
> or
> - calling the code in an external procedure.

We're calling a third-party web service, among several other
things, that has a much more complex calling structure. We
work with a 3rd-party app from a large company that isn't
directly compatable with Oracle, but has interface libraries
(only) for .NET.

We're not calling our web service just to go back and edit
our own database; that's a necessary secondary function
for the purpose of logging the results.

The code can't be called from an external procedure (AFAIK)
because it isn't required to be in the same physical location,
and because the one web service might be called by multiple
databases, or multiple schemas within one database.

> What you have set up now is extreemly inflexible, extreemly
> unscalable, and extreemly unrobust.
> One would wonder why your senior dev didn't recommend to keep it in
> Oracle as much as possible without this cr**p.
>
> The remedy you propose is even worser than the problem at hand.

I will take this under advisement. Seriously. I don't think we're
looking to redesign or remove the web service at this point, however.

-- Julian Mensch

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

jg
--
@home.com is bogus.
http://www.scaleabilities.co.uk/comp...c,select/id,1/