Home » Server Options » Streams & AQ » Advanced queue don't dequeue - v$session v$lock
Advanced queue don't dequeue - v$session v$lock [message #175808] Mon, 05 June 2006 08:00 Go to next message
alvisemion
Messages: 4
Registered: June 2006
Junior Member
I've 2 queues in my schema; Something happened, i don't know what and when but now (since 3 days)
the dequeueing doesn't works for any qeueue (even if i restart db); All the messagges stay always
in ready state, never expire and never be processed!

I try to find out any lock with this query:
select * from v$session where sid in (select distinct sid from v$lock);
and i get 2 sessions for my schema with sys user, with the event "queue messages" #605, Blocking_session_status = UNKNOWN,
Wait_class = Idle, seconds in wait: 21777 (6 h) exactly the time since i started up my database.

For 1 queue, in the stored used by agent for dequeing i use the workaround dicussed
below "AQ/PLSQL Notifications cannot be serialized"

What should i do to restart dequeueing?

Thanks, Alvise


AQ/PLSQL Notifications cannot be serialized

SCOPE & APPLICATION
-------------------

This article is intended for anyone interested in maintaining
FIFO ordering of callbacks when using AQ PL/SQL notifications.


Serialization of AQ PL/SQL Notifications
----------------------------------------

When a callback is detected a message is placed in SYS.AQ_SRVNTFN_TABLE_Q
waiting to be dequeued, and a job is submitted through the Oracle job queue to
execute the callback function. Each job is submitted in the order that the
callback was fired, i.e., the next date for the job is set to be the time that
the callback was raised for the message.

When messages are enqueued within the same second, i.e., the enqueue time is the
same for both messages, a step number is implemented to ensure that the order
of the messages is maintained on dequeue.

However, when the jobs for the callback are submitted on the job queue, the
step number for those messages that were enqueued in the same second is effectively
lost. This is further exacerbated when messages are enqueued in the same
transaction.

Therefore, when a job queue process is allocated a job from the job queue list,
it may run the callback functions for messages that were enqueued in
the same second, but will not maintain FIFO.

However, there is a work around that can be implemented in the callback
function itself; it is based on following premises:

1. A callback function processes all current messages on a queue, not just the
message that the callback function was invoked for, i.e., dequeue the messages,
and not use the information that is passed into the callback function.

2. A callback function is a point of serialisation, i.e., the process body of
the callback function is mutually exclusive for all other invocations of that
callback function.

3. No other process is to dequeue messages from the queue.

Point 1 ensures that the messages are dequeued in the correct order. Point 2
ensures that no other callback function invocation can dequeue messages from a
queue whilst the current invocation is dequeuing messages.

Below is a pseudo-code example for a callback function. For more
information on the use of DBMS_LOCK, see the Supplied PL/SQL Packages and Types Reference Manual.

Pseudo-Code
-----------

create or replace procedure my_callback(context RAW,
reginfo sys.aq$_reg_info,
descr sys.aq$_descriptor,
payload VARCHAR2,
payloadl NUMBER)
as

my_payload some_q_payload;
exc_payload exception_payload;
queue some_q_mgr := new some_q_mgr;
exc_queue some_exec_queue;
lock_name varchar2(30) := 'SOME_Q_CALLBACK_LOCK';
lock_handle varchar2(128);
dummy pls_integer;
begin

-- start the MEP (mutually exclusive part)

dbms_lock.allocate_unique(lock_name,lock_handle);
dummy := dbms_lock.request(lock_handle,dbms_lock.x_mode,dbms_lock.maxwait,true);

-- Separate block to handle exceptions, and raised exception queue messages

begin

-- Get the first message on the queue, and loop

my_payload := queue.dequeue;

while my_payload is not null
loop

-- Perform the processing of the messages here (commit in here will release the -- MEP). If a commit after each message is required, change the mode of the lock -- request not to release the lock after commit/rollback. Then get the next
-- message on the queue (if the queue is empty, this returns NULL).

my_payload := queue.dequeue;

end loop;

exception

-- Raise exception message if error occurs during processing when others then

exc_payload := exception_payload (sqlcode, sqlerrm, 'Failed to do
my callback',
queue.q_name, queue.payload);
exc_queue.enqueue (exc_payload);

end;

-- End the MEP

dummy := dbms_lock.release(lock_handle);

end my_callback;

Re: Advanced queue don't dequeue - v$session v$lock [message #176417 is a reply to message #175808] Thu, 08 June 2006 04:35 Go to previous message
alvisemion
Messages: 4
Registered: June 2006
Junior Member
The problem was dued to dequeue_options.wait. In my callback procedure i didn't set any wait option.

After setting dequeue_options.wait:=dbms_aq.no_wait queues starts dequeuing correctly.

Alvise
Previous Topic: Message in processed state not deleted from queue
Next Topic: WaitTime for Browse messages
Goto Forum:
  


Current Time: Thu Mar 28 17:21:43 CDT 2024