This discussion is archived
5 Replies Latest reply: May 24, 2013 4:22 PM by EJP RSS

How can I avoid polling for database alerts?

1010175 Newbie
Currently Being Moderated
I am implementing a Java package that makes processing alerts from Oracle (the dbms_alert pl/sql package) easier than raw JDBC. This happens inside a servlet. My first problem is that when the servlet container such as tomcat asks the servlet to shutdown, choosing to stop listening for Oracle alerts appears to be impossible without polling Oracle for the alert events.

Specifically, I have a thread that is blocked on a call to dbms_alert.waitany. How can I wake this thread up and have it cleanly shut down? I've tried closing the JDBCConnection in the hopes it would receive a connection-closed exception which my thread could simply ignore if being asked to shut down. But the call to JDBCConnection.close() simply hangs. I've tried a few other tricks to no avail. Documentation I've studied does not discuss the problem at all.

The only "solution" I've found is to pass the dbms_alert.waitany a one-second timeout parameter when waiting for alerts. This makes the thread wake up every second, at which time it will usually just find that the timeout occurred and start waiting again. But this creates an opportunity for it to check an exit flag and terminated itself.

Aside from wasting some CPU cycles this has the serious downside that it results in a remote call to Oracle across the network every second! That will no-doubt aggravate various people in IT as a serious load and I'm not sure how to articulate a good defense.

I'm really hoping that there's some way to wake up my thread without having it wait for a timeout. Otherwise is this not a serious defect?

There are other use cases for my package that are stumped by the same root problem. If I want to register for a new alert, I'd like to wake up my blocked thread, have it make the registration call, then call dbms_alert.waitany again. But this too requires waiting for the thread to wake up based on the timeout and the resulting trade-off between wasting resources and delaying the registration - a trade-off that has no good answer.

Is there any way I can get the call to dbms_alert.waitany to unblock with an error code or exception so this impossible decision becomes irrelevant?

Thanks
  • 1. Re: How can I avoid polling for database alerts?
    Joe Weinstein Expert
    Currently Being Moderated
    Try casting the connection to an Oracle connection, and calling abort(), which will kill the
    connection without being blocked.
  • 2. Re: How can I avoid polling for database alerts?
    rp0428 Guru
    Currently Being Moderated
    >
    Specifically, I have a thread that is blocked on a call to dbms_alert.waitany. How can I wake this thread up and have it cleanly shut down?
    >
    You wake it up the way it is designed to be woken up: signal an alert from another thread.

    That thread will 'wake up' when an alert is signaled. So when you want to shutdown set a 'shutdown' variable that the thread checks wheneve it wakes up.

    Then use another thread to signal an alert. That alert will wake up your blocked thread, the thread will check the 'shutdown' variable and will take action based on the setting of that variable.

    See the examples in the DBMS_ALERT section of the Oracle docs:
    http://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_alert.htm
    >
    WAITANY Procedure
    Call this procedure to wait for an alert to occur for any of the alerts for which the current session is registered.
    >
    You can register for your own 'JAVA SHUTDOWN' alert in addition to the alert your thread is now registered for. Then use the method I outline above to use the SIGNAL method to generate that 'JAVA SHUTDOWN' alert. That will wake your thread up so it can shut itself down.
  • 3. Re: How can I avoid polling for database alerts?
    jschellSomeoneStoleMyAlias Expert
    Currently Being Moderated
    user1881842 wrote:
    it results in a remote call to Oracle across the network every second! That will no-doubt aggravate various people in IT as a serious load and I'm not sure how to articulate a good defense.
    And there is one box doing this or 10,000?

    If the first and someone claims that it a performance problem/impact then ask them to actually prove it.
    If they prove it, and you might mention this first, then you should suggest that the company should switch to a different database since if Oracle can handle one polling call a second then it can handle anything that a reasonable business might throw at it.
  • 4. Re: How can I avoid polling for database alerts?
    1010175 Newbie
    Currently Being Moderated
    Simple and effective. I wish I'd thought of that. Thanks!
  • 5. Re: How can I avoid polling for database alerts?
    EJP Guru
    Currently Being Moderated
    if Oracle can handle one polling call a second then it can handle anything that a reasonable business might throw at it.
    Non sequitur. Did you mean "can't" in both places?

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points