Re: Calling DBMS_ALERT Blocking Inserts

Thanks,
Seems almost unbeleivable its was implemented in this way....

Anyway weve decide to use DBMS_PIPE instead to try and achieve what we want.. hope ther are no "hidden" surprises there <g>

Rob

Mark Wagoner wrote in message <35caf422.572597091_at_news.iac.net>...
>On Fri, 7 Aug 1998 16:32:36 +0800, "Rob Edgar" <robedgar_at_hkstar.com>
>wrote:
>
>>We have a trigger on table WIDGET that calls a routine in a package MMSTD
to
>>insert a record into a table called MSG_QUEUE. The MSG_QUEUE table also
has
>>a trigger that calls the MMSTD package to run a routine that call
>>DBMS_ALERT. Another session is waiting on this signal and just reads the
msg
>>attached to the signal, it does no other activity in oracle.
>>
>>This appears to work OK >>EXCEPT<< at times everyone gets hung up in the
>>middle of doing inserts/updates and it wont clear until we disable the
>>trigger on the MSG_QUEUE table.
>>
>>It sounds like there is some sort of lock contention but I dont see
how/why
>>.
>>
>>Could anyone shed some light on this problem
>>
>>Rob
>>
>
>We recently ran into the same problem. There is a lock contention
>going on, you can tell by looking at the SYS.DBMS_ALERT_INFO table.
>Apperently, all alert signalers have to update the same row in this
>table, so if the first one does not commit, the rest block. Why
>Oracle impelented it this way is beyond me.
>
>The only way I found around this was to restructure the app to perform
>the triggering event immediatly before it commits. This way the lock
>is held for the least amount of time.
>
>
>--
>Mark Wagoner
>To reply, remove no.spam from my e-mail address
Received on Sun Aug 09 1998 - 01:36:14 CDT