| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Lock with dbms_alert
Olivier Bercovitz wrote:
>
> 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
The time between when you send the alert and commit can cause blocking..since dbms_alert uses an underlying table...
dbms_pipe does not use a table but is not transactional...
For example:
if you send an alert, and then roll back - the alert never happened.. if you send a pipe, and roll back - a pipe listener will still get it..
Cheers
--
 |
 |