Why do messages remain visible after being dequeued?

A while back, I had an email asking why when a message has been removed from a Workflow-related advanced queue, it still remained visible:

We have a requirement to delete the future planned event from the WF_DEFERRED table. I am executing the below code to delete / remove a record from WF_DEFERRED table, however it is not deleting.

  WF_QUEUE.purgeevent (wf_queue.deferredqueue,'<Msg ID>', TRUE);

Looking at the example code that they gave me, there is nothing particularly wrong with what they were doing – personally, I’m wary of using the WF_QUEUE APIs to manipulate messages on queues because I prefer to use DBMS_AQ and DBMS_AQADM when working with queues.  The reason I’m so reluctant to rely on the Workflow package stems from when Workflow 2.6 first shipped and introduced BES.  Working for a large banking client in London, we were the first implementation of Workflow which really pushed what BES was designed to do – however, dealing with Workflow product development regularly made me shudder with some of their suggestions on how to work with queues.  At the time, the team leader that I was working with was one of the leading lights of the Oracle integration space, and between us we concluded that we were better steering clear of the Workflow manipulation of queues (anything that relies on polling to check something which could / should have been written as a blocking dequeue was not a good sign, nor was the idea that the product was only shipping ready to work with single consumer queues…)

Anyway, that’s my personal preference – you can use the WF_QUEUE package if you want, but you won’t find anything that I work with that uses that package in preference to the DBMS_AQ package.

The first thing that I said to check was what the retention period for the queue was.  When a queue is defined in Oracle, you can define how long a message can remain visible once it has been dequeued (i.e. the retention period), and Oracle Workflow defines a retention period for the seeded queues as standard.  The retention period can be checked by running the following SQL:

SELECT owner, name, retention FROM all_queues WHERE name LIKE 'WF%';

If the retention time is not zero, then the message will remain visible for that length of time after it has been processed.  Assuming that the message is still visible, we can then check the status of the message by running the following SQL:

SELECT msg_state, count(*) FROM applsys.aq$wf_deferred GROUP BY msg_state;

SELECT msg_state FROM applsys.aq$wf_deferred WHERE msg_id = <your message ID>;

If the message has been removed then the status would show as PROCESSED.  All that the purgeevent API does is to dequeue and discard the message.  If the message has not been processed, then is may well be in a READY state, which indicates that the API has not worked.

As it turned out, the message had been dequeued correctly (the message state was PROCESSED), but the user was still seeing the message because the retention period had not passed completely when they were checking in the queue for the message.

So – that’s why the messages were still visible, despite them being removed from the queue.

By continuing to use the site, you agree to the use of cookies. more information

In common with almost all professionally run websites, this website logs the IP address of each visitor in order to keep it running reliably. This is also essential for protecting the website and its visitors from malicious attacks, including infection with malware.

This website provides information as a service to visitors such as yourself, and to do this reliably and efficiently, it sometimes places small amounts of information on your computer or device (e.g. mobile phone). This includes small files known as cookies. The cookies stored by this website cannot be used to identify you personally.

We use cookies to understand what pages and information visitors find useful, and to detect problems such as broken links, or pages which are taking a long time to load.

We sometimes use cookies to remember a choice you make on one page, when you have moved to another page if that information can be used to make the website work better. For example:
- avoiding the need to ask for the same information several times during a session (e.g. when filling in forms), or
- remembering that you have logged in, so that you don’t have to re-enter your username and password on every page.

You can prevent the setting of cookies by adjusting the settings on your browser (see your browser Help for how to do this). Be aware that disabling cookies will affect the functionality of this and many other websites that you visit.