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
Registered: June 2006
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?
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
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
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.
create or replace procedure my_callback(context RAW,
queue some_q_mgr := new some_q_mgr;
lock_name varchar2(30) := 'SOME_Q_CALLBACK_LOCK';
-- start the MEP (mutually exclusive part)
dummy := dbms_lock.request(lock_handle,dbms_lock.x_mode,dbms_lock.maxwait,true);
-- Separate block to handle exceptions, and raised exception queue messages
-- Get the first message on the queue, and loop
my_payload := queue.dequeue;
while my_payload is not null
-- 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;
-- Raise exception message if error occurs during processing when others then
exc_payload := exception_payload (sqlcode, sqlerrm, 'Failed to do
-- End the MEP
dummy := dbms_lock.release(lock_handle);
Current Time: Fri Jun 02 21:41:54 CDT 2023