Alerting a UNIX process of each committed change to a table
Date: Thu, 9 Jun 1994 00:34:56 GMT
Message-ID: <navisCr3tM8.5ps_at_netcom.com>
Background: We need to tranmit each change to two critical tables to another
(non UNIX, non-RDMS) system in real time, with the following design objectives;
- modularity
- great performance
- application should not wait for the interface process to accept and process and then forward the message.
- messages should be delivered in the order performed.
- delivery should be guaranteed.
Triggers seemed like the way to go meet the 1st objective. For the 2nd objective it seemed reasonable that the trigger could just pass the 'new' variables onto the other process to avoid dublicate database I/O. The operative word here is "pass" since the trigger's access to the outside world is limited. At first we thought dbms_pipes was the way to go. Then we realized that the listener would be notified of, and act upon, the changes before they were actually committed!
Next, we considered dbms_alerts (the lister isn't notified until the signaler commits!), until we realized that a transaction that did multiple changes to a table would ultimately only generate ONE alert and that all the other sessions which generated the same alert would hang in the next trigger they executed that issued that alert until the first session completed all of its changes and finally committed!. In other words, the posting process of our entire application was essentially reduced to a single thread (like the company had one giant commit key and all the clerks had to stand in line for their turn to press it!). Am I making this clear?
What I want to know, are we stuck with the "interface" table solution where
the trigger(s) insert a row into a table and a listener polls the table
for "new" rows to process and then delete (or mark as processed)? We're very
concerned that the contention over this table could be fierce. The two
critical tables have about 250,000 1200 byte rows each. They are modified by
almost every transaction in the application at a rate of only 5-10 changes per
minute by our on-line. But the our batch can hit these tables pretty hard
(1200 consecutive changes).
Received on Thu Jun 09 1994 - 02:34:56 CEST