Re: DBMS_AQ.REMOVE issue

On May 31, 4:58 pm, n..._at_familyjames.com wrote:
> 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

That was indeed helpful. Thank you.

Regards,
z1hou1 Received on Thu May 31 2007 - 23:11:15 CDT