Re: DBMS_AQ.REMOVE issue

On May 31, 4:15 pm, z1h..._at_gmail.com wrote:
> On May 31, 10:13 am, z1h..._at_gmail.com wrote:
>
>
>
> > On May 30, 5:21 pm, z1h..._at_gmail.com wrote:
>
> > > On May 30, 4:12 pm, DA Morgan <damor..._at_psoug.org> wrote:
>
> > > > z1h..._at_gmail.com wrote:
> > > > > Hi,
> > > > > When I dequeue a message from the queue using the DBMS_AQ.REMOVE
> > > > > option, the message still stays in the queue table (test_qtbl) long
> > > > > after the dequeue is complete. I am under the impression that this
> > > > > option will remove the message from the queue table when the message
> > > > > is dequeued. I have also specified the retention time while creating
> > > > > the queue to zero (not to retain any messages after dequeue). After
> > > > > the dequeue, if I try to dequeue the same message again it is not
> > > > > available for dequeuing.
> > > > > Although this behavior is not affecting my application, it is very
> > > > > annoying to see the messages in the queue long after it has been
> > > > > processed.
>
> > > > > I have given a sample code below which demonstrates this. Am I missing
> > > > > something here?
>
> > > > > create or replace type test_typ as object (msg varchar2(5))
>
> > > > > begin
> > > > > dbms_aqadm.create_queue_table(queue_table => 'test_qtbl',
> > > > > queue_payload_type => 'test_typ', multiple_consumers => true);
> > > > > dbms_aqadm.create_queue(queue_name => 'test_queue', queue_table =>
> > > > > 'test_qtbl', retention_time => 0);
> > > > > dbms_aqadm.start_queue('test_queue');
> > > > > end;
>

[Snip]
> > I finally found the answer to this issue and thought that I will post
> > the reply. I would like to assure anyone concerned that I am not
> > annoyed anymore with the fact that the dequeued messages are not
> > getting deleted in multi-consumer queues. :).
>
> > In release 8.0.x when two or more processes/threads that are using
> > different consumer_names are dequeuing from a queue, only one process/
> > thread can dequeue a given message in the LOCKED or REMOVE mode at any
> > time. What this means is that other consumers that need to the dequeue
> > the same message will have to wait until the consumer that has locked
> > the message commits or aborts the transaction and releases the lock on
> > the message. However, while release 8.0.x did not support concurrency
> > among different consumers for the same message., with release 8.1.6
> > all consumers can access the same message concurrently. The result is
> > that two processes/threads that are using different consumer_name to
> > dequeue the same message do not block each other. AQ achieves this
> > improvement by decoupling the task of dequeuing a message and the
> > process of removing the message from the queue. In release 8.1.6 only
> > the queue monitor removes messages from multi-consumer queues. This
> > allows dequeuers to complete the dequeue operation by not locking the
> > message in the queue table. Since the queue monitor performs the task
> > of removing messages that have been processed by all consumers from
> > multi-consumer queues approximately once every minute, users may see a
> > delay when the messages have been completely processed and when they
> > are physically removed from the queue
>
> > dbms_aqadm.start_time_manager will start the monitor.
>
> > Regards,z1hou1- Hide quoted text -
>
> > - Show quoted text -
>
> For anyone interested the solution was found
> athttp://www.mscd.edu/~ittsdba/oradoc817/appdev.817/a76938/adq08qa5.htm...

Since the REMOVE process has logically removed the message, I assume that you
are reading the table directly to know that it is still there.

You have written why the messages remain there for a time. The note refers to 8.1.6.
I use 10.1. It may apply to other versions as well.

If you want to know whether the message has been processed or not, you will
need to look at another AQ table. You created a table called 'TEST_QTBL'.
There is another one generated called AQ$_TEST_QTBL_H where the individual processes write that they have processed the message.

Unprocessed messages are those where
SELECT *
FROM test_qtbl tab
WHERE state = 0 -- Ready
AND EXISTS

       (SELECT NULL
        FROM   aq$_test_qtbl_h h
        WHERE  h.msgid = tab.msgid
        AND    h.dequeue_time IS NULL)   -- Not dequeued yet

When all subscribers have dequeued the message, then it can be deleted.

Hope this helps,
Neil Received on Thu May 31 2007 - 15:58:01 CDT