Re: Lock with dbms_alert

From: Gjlinker <gjlinker_at_aol.com>
Date: 21 Jan 1999 23:05:05 GMT
Message-ID: <19990121180505.10468.00000325@ngol05.aol.com>

Dear Olivier,

Please be aware of the following with dbms_alert:

Session1 : fire the trigger which sends the alert Session2 : fire the trigger which sends the alert

                    Since it was already send in Session1, Session2 locks until
                    Session1 commits

Session1 : Commit. The alert it now send. Session2 : Since the alert is already sent, the alert is discarded and the lock is released.

Please remember that if 2 sessions are using the same database package/trigger they will instantiate it for their own use. So 2 sessions calling it will cause it to be in memory twice. Therefore you can get in the situation above.

Hope this helps,

Gerrit-Jan Linker
Developer of Oraxcel, OraWeb, OraSQL and OraCodes

In article <369A1E18.63CFA32_at_steria.fr>, Olivier Bercovitz <olivier.bercovitz_at_steria.fr> writes:

>Subject: Lock with dbms_alert
>From: Olivier Bercovitz <olivier.bercovitz_at_steria.fr>
>Date: Mon, 11 Jan 1999 15:51:54 GMT
>
>Hello everybody,
>
>We have a problem with dbms_alert functionnality :
>
>A batch program on 1 db do a distributed insert on a table (tab_vol)
>located on a 2nd db (we us db links)
>A trigger is firing when rows are inserted in this table (tab_vol)
>and this trigger insert into another table (tab_tcf) always on the 2n
>db.
>Another trigger is firing on this table (tab_tcf) and do a dbms_alert to
>
>notify
>a process.
>
>Sometime we have an error for the first transaction on the first db :
>ORA-2049: timeout: distributed transaction waiting for lock
>ORA-6512: at "SYS.DBMS_ALERT", line 396
>It seems like the dbms_alert on the 2nd db is waiting for a lock
>but we don't see why ?
>
>Can someone explain me how to use dbms_alert and the role
>of the commit with this use ?
>
>Can we used dbms_alert in a trigger ?
>
>Can we replace dbms_alert by dbms_pipe, the goal is only to
>advertise a process to do something ?
>
>What the difference between dbms_pipe and dbms_alert in this case ??
>
>Thanks for your answers
>
>Regards
>Olivier
>
>
Received on Thu Jan 21 1999 - 17:05:05 CST