Query returns 0 rows after successful row insert/message enqueue when using Tibco Application in a RAC environment (Doc ID 1412774.1)

Last updated on FEBRUARY 13, 2012

Applies to:

Oracle Server - Enterprise Edition - Version: 11.2.0.1 to 11.2.0.3 - Release: 11.2 to 11.2
Information in this document applies to any platform.

Symptoms

Intermittently a query of the following form

SELECT /*+ index (staffo pk_staffo) */ o_reqid FROM tibco_bpmpro.staffo WHERE o_queuename=:1 AND o_reqid=:2 AND o_reqidhost=:3

executed by a Tibco WIS process does not return rows which have previously been successfully inserted into the staffo table.

The Tibco application does the following

A BS process in customer application performs the following operations

1 - Insert unique row in a table called STAFFO.
2 - Enqueue a message to an AQ, queue name of WISMBOXQUEUE*, the message contains a reference to the unique record in the staffo table.
3 - Inserts a unique row in a table called OUTSTANDING_ADDR
4 - Commits

Another process WISMDB dequeues messages from WISMBOXQUEUE*, when it dequeues the message it relays the message contents to another process (via RPC), this process uses the information in the message to select the unique row from the STAFFO table, in the following way :-

1 - Process (WISMBD) dequeues the message from the AQ.
2 - The process (WISMBD) sends the message to another process (WIS) using RPC.
3 - The process (WISMBD) commits (or rollbacks) the dequeue.
4 - The WIS process decodes the unique reference from the message.
5 - The WIS process queries the STAFFO table using the unique reference
6 - The WIS process caches in memory the information from the STAFFO table.

Step 5 intermittently fails in a RAC cluster in 11.2. The issue never arises when the Tibco
application runs on a single instance or a single node in a RAC cluster or when the WISMDB and WIS processes run on the same node.

Ad-hoc LogMiner analysis indicates that the inserts and messages were enqueued and committed and the dequeue of the associated messages occurred very close to (in scn terms) but after the message enqueue.

Changes

Upgrade from 10.2 to 11.2.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms