Re: DBMS_AQ.REMOVE issue

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;
>
> > > declare
> > > l_queue_options DBMS_AQ.ENQUEUE_OPTIONS_T;
> > > l_message_properties DBMS_AQ.MESSAGE_PROPERTIES_T;
> > > l_message_id RAW(16);
> > > l_type test_typ;
>
> > > l_recipient_list DBMS_AQ.AQ$_RECIPIENT_LIST_T;
> > > l_agent sys.aq$_agent := sys.aq$_agent('agent1', null, null);
> > > begin
> > > l_recipient_list(0) := l_agent;
> > > l_message_properties.recipient_list := l_recipient_list;
>
> > > l_type := test_typ('Help');
> > > dbms_aq.enqueue('test_queue', l_queue_options, l_message_properties,
> > > l_type, l_message_id);
> > > end;
>
> > > declare
> > > l_type test_typ;
> > > l_queue_options DBMS_AQ.DEQUEUE_OPTIONS_T;
> > > l_message_properties DBMS_AQ.MESSAGE_PROPERTIES_T;
> > > l_message_id RAW(16);
> > > begin
> > > l_queue_options.wait := 3;
> > > l_queue_options.dequeue_mode := DBMS_AQ.REMOVE;
> > > l_queue_options.consumer_name := 'agent1';
> > > dbms_aq.dequeue('test_queue', l_queue_options, l_message_properties,
> > > l_type, l_message_id);
> > > DBMS_OUTPUT.put_line(l_type.msg);
> > > end;
>
> > > Regards,
> > >z1hou1
>
> > The purpose of the queue table includes an audit trail of messages. If
> > .REMOVE removed the messages your audit trail would evaporate. The
> > product is doing precisely what the documentation indicates that it
> > should.
>
> > If you want a DELETE statement then you should write that into your
> > application code.
> > --
> > Daniel A. Morgan
> > University of Washington
> > damor..._at_x.washington.edu
> > (replace x with u to respond)
> > Puget Sound Oracle Users Groupwww.psoug.org-Hide quoted text -
>
> > - Show quoted text -
>
> Thanks Daniel for your reply. Why is it then that when we have a
> single consumer queue, that the dequeue operation physically deletes
> the row from the queue table? It is this characteristic that confused
> me and prompted me to post the earlier question. Why does the same
> operation on a multiple consumer queue not result in an empty queue
> table after the dequeue operation.
>
> Regards,z1hou1- Hide quoted text -
>
> - Show quoted text -

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 Received on Thu May 31 2007 - 09:13:31 CDT