Alroy Mascranghe wrote:
>
> Hi All
>
> We have a Pro C daemon which does a DBMS_aLERT.'waitone' for a specific
> alert. From the database side applications do a signal to start the daemon.
> So we have a locking situation where we found out that the rows in sql area
> for two different users were the same
> UPDATE DBMS_ALERT_INFO SET CHANGED='Y',MESSAGE=:b1 WHERE NAME =
> UPPER(:b2)
> And when we queried the rowid which was locked by the users they were all
> waiting on this row
> select * from DBMS_ALERT_INFO where
> rowid= 'AAAAZwAABAAAAVUAAB';
> What is the reason for this to caused locks ?
>
> Thanks
> Alroy
I am not sure if this is really related to your problem, but when you
send an alert you take a lock on this alert which is not released before
you commit. As a result, if you send an alert in a trigger which is
fired at the beginning of a long transaction (made of say several
statements) any other attempt to fire the trigger will block until the
transaction commits. A workaround is to signal in an autonomous
transaction. The snag is that then you lose the transactional aspect of
things and (if you have a single process waiting) you could as well use
DBMS_PIPE - ie you may be awaken before the signalling transaction has
committed and must be prepared to handle the case.
--
HTH,
Stephane Faroult
Oriole Software
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Stephane Faroult
INET: sfaroult_at_oriole.com
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Received on Wed Apr 10 2002 - 14:58:20 CDT
