Trigger Timeout Loop Issue
- From: Julian Mensch <jmensch@xxxxxxx>
- Date: Wed, 27 Aug 2008 11:08:00 -0700 (PDT)
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
.
- Prev by Date: Re: ORACLE 11g DB Control hugs CPU 100%
- Next by Date: Re: about "cursor: pin S wait on X" wait event.
- Previous by thread: What are the advantages of using an alias for local_listener
- Next by thread: Re: Trigger Timeout Loop Issue
- Index(es):
Relevant Pages
|
