efficient way to poll database?

Question

I have a Linux CentOS server hosting an Oracle 11.2 database, which communicates with an application server, which communicates with a client (a Flex application). I'll try to describe the pseudo-code I want to implement as a process as follows:

The application server has a Java program that acts like a database front end for clients. Basically, a client wishes to do some number crunching, submits some data to the Java tier, and the Java tier saves this data to the database, along with a flag used to instruct a C program that data is waiting.

The application server also has this C program on it. The C program polls the database for this flag every 1 second or so, and when the C program detects this flag to be set, the C program downloads the data using OCILIB (e.g. OCI) from the database, crunches the math, and saves a result back into the database. The C program then sets a different flag to instruct the Java tier this processing is done.

Another program in the Java tier is watching this flag that the C program will set. When it detects the flag is set, the Java tier sends a message to the client telling the client that the data is available in the database.

The client then downloads the data from the database by sending an appropriate request to the first Java program discussed above.

[A simpler approach may be to have the java tier call the C program directly (to avoid all of this polling the database, etc.), but the required JNI programming along with java queuing and multi-threading is beyond my capability as a programmer.]

QUESTIONS:

Is having 2 processes poll the database fairly common in the industry (and nothing to worry about), or is it a bad practice?

Is there a way, for example (in SQL or PL/SQL), to avoid polling by having the database call a function (e.g. C or Java) when the contents of a cell change to become a certain value (e.g. 0 = wait, 1 = call the function)?

Is the simplistic approach above reasonable or flawed somehow? Is there a better, although still straightforward, way of achieving the same result? Anything to watch out for?

Answer 1

If you must poll, see this article by a former colleague of mine.

Most places I’ve worked at have had some sort of requirement for picking up new or changed rows from a table in the database. It’s only recently that I’ve found, what I would consider to be, a clean way of doing this.

He is particularly interested in capturing a stream of changes, but your use case sounds like you want to do a form of cross-language IPC via the database. I personally do not like polling; it's inelegant. There are a few approaches for this:

• AQ. I link to the 9i docs so you can see that this is a well-established feature of Oracle. This is my own favourite technique for this kind of application. One interesting thing about AQ is that you might not even need to tell your app to wake up and fetch the data; you could send the data in the payload!

• DCN. This is very straightforward to use from OCI, but I have never tried it with Java so can't comment.

• DBMS_ALERT, as Leigh mentions
• DBMS_PIPE a similar mechanism

• Direct calls from PL/SQL into C using EXTPROC. From C you can call any other language, including Java. From that link there is some sample code on Github for doing this; it's actually quite straightforward.

• If you want to get really exotic, you could do this with Oracle Coherence - I do this via C in my example, there is also a Java binding.

Answer 2

Take a look at the DBMS_ALERT package.

DBMS_ALERT supports asynchronous notification of database events (alerts). By appropriate use of this package and database triggers, an application can notify itself whenever values of interest in the database are changed.

Suppose a graphics tool is displaying a graph of some data from a database table. The graphics tool can, after reading and graphing the data, wait on a database alert (WAITONE) covering the data just read. The tool automatically wakes up when the data is changed by any other user. All that is required is that a trigger be placed on the database table, which performs a signal (SIGNAL) whenever the trigger is fired.

If you must do polling, you should index the field being polled and make sure that all all values are NULL except for the ones that should be picked up. This will make for a very small index and very efficient lookups. If multiple criteria determine when a record should be processed consider creating a function based index that returns NULL for any record not of interest.