| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Calling DBMS_ALERT Blocking Inserts
A copy of this was sent to mwagoner_at_no.spam.iac.net (Mark Wagoner)
(if that email address didn't require changing)
On Fri, 07 Aug 1998 12:36:06 GMT, you wrote:
>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.
>
Well alerts are transactional. The waiting process for an alert is waiting to be notified that an alert has occurred. It is not polling the alert table looking for new rows. All signallers of the same NAMED alert will serialize -- only one can signal that alert at a time.
dbms_pipes are not transactional, the message goes out right away. That might be another way depending on your circumstances (that and you don't 'lose' pipe messages like you can 'lose' alerts. If lots of people signal an event while you -- the process waiting on the event -- go off and do something, you can 'miss' those signals.
>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.
Another way to implement this (not by postponing to right before the commit) is to execute:
dbms_job.submit( l_jobno, 'dbms_alert.signal( ''MyAlert'', ... );' );
That way
- alerts are still transactional
- they don't serialize your transactions
The drawback is
- jobs are not necessarily run *right* away, it might be a little while before
the alert gets out. In many cases I have found this to be acceptable (its
important to notify the waiting process that something has occurred but a short
lag time was OK)
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA
http://govt.us.oracle.com/ -- downloadable utilities
Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Fri Aug 07 1998 - 09:21:43 CDT
 |
 |