TOracleEvent loosing pipe messages

[manninga]

We are currently using the latest DOA package in our Delphi 7 environment. Over the past few months we have seen an increase in pipe messages being lost.

Our main Oracle table fires a trigger when a new record is inserted. This trigger first calls dbms_pipe.pack_message() and then inserts the pipe message into a second table for debugging purposes.

When the TOracleEvent receives a message the content of the message is first written to a log file and then the necessary processing is performed.

The number of records inserted into the main table has been steadily increasing over the past few months. As the inserts increase (400+ a day) so have the number of messages being lost (average 1 per day.)

From the additional debugging table and log file we know that the lost message is inserted into the pipe but the TOracleEvent component never receives it. All messages sent before and after the lost message appear in both the debug table and the log file.

Do we need to move to advanced queuing or is there something we can change to not loose any pipe messages?

 

[Marco Kalter]

I would expect that either there is another application reading the same pipe, or the sender ignores an error situation and the message is not actually sent. Otherwise you should not loose messages.

 

[manninga]

I know there isn't anything else reading the pipe. Based on the content of the message added to the pipe, I don't see how any error could occur on sending. I would suspect that any error sending the message would cause the trigger to fail and not continue inserting the message into the debugging table.

There have been other times (though less frequent) when the TOracleEvent would stop receiving messages all together. The application would still be running fine, but no messages would be read. Calling the Stop procedure would then hang the application. After killing and restarting the program, all the messages then waiting in the pipe are retrieved successfully. (Including the previously issued 'Stop' command which raises an exception)

Due to these issues we have to stop and start our service every night at midnight. This has helped, but we still have the occasional lost message or occasional event failure.

 

[Marco Kalter]

I would suspect that any error sending the message would cause the trigger to fail and not continue inserting the message into the debugging table.

Not necessarily. The dbms_pipe.send_message function returns 0 (success) or 1 (time out) or 3 (interrupted). Ignoring this return value could result in missing messages.

 

[manninga]

I will update the trigger to check for the return value.

 

[manninga]

We lost another message this morning. I checked our debug table, and the return status of the pipe message insert was 0.

The pipe and application were both sitting idle for some time before and after the lost message.

Here are the results of the trigger's debug table and the application log file:

TIME_SENT - SEQ - RESULT
08:42:53 - 74428 - 0
08:44:05 - 74429 - 0 (Lost Message)
08:53:49 - 74430 - 0

TIME_RECEIVED - SEQ
08:42:53:378 - 74428
// Missing Message
08:53:49:316 - 74430

Any other ideas?