Re: Calling DBMS_ALERT Blocking Inserts

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 Fri Aug 07 1998 - 07:36:06 CDT