Calling DBMS_ALERT Blocking Inserts

From: Rob Edgar <robedgar_at_hkstar.com>
Date: Fri, 7 Aug 1998 16:32:36 +0800
Message-ID: <6qee35$r735@news.hk.linkage.net>

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 Received on Fri Aug 07 1998 - 03:32:36 CDT