Re: Advanced Queue ordering?
- From: Martic Zoran <zoran_martic@xxxxxxxxx>
- To: srospo@xxxxxxxxxxx, oracle-l@xxxxxxxxxxxxx
- Date: Wed, 8 Jun 2005 00:08:02 -0700 (PDT)
Hi Steve,
There is not a magic behind AQ beahviour.
From what you said I assumed it is the proper
behaviour.
Both sessions at the beggining of dequeue are using
this kind of SQL (easy to find out if you turned on
tracing 10046 with binds):
select .... from <queue_tab>
order by enq_time, priority
for update skip locked;
the undocumented "skip locked" will skip all locked
rows by somebody else, so it is easy to find out that
two simultanious sessions will read different messages
in the general order by enq_time, priority as stated
by you.
What you showed us is that both sessions were equally
fast to process rows one by one.
I am not seeing any problem there.
What did you want to achieve?
Oh, I get it now. Enqueue time is the time when the
messages were enqueue (insert) into the queue.
You probably misread what is the enqueue time.
Is that true?
I believe that enqueue time is in the order as showed
with the one session run.
You can check this by doing direct select on the queue
table.
Regards,
Zoran
--- Steve Rospo <srospo@xxxxxxxxxxx> wrote:
>
> I'm helping out a colleague here with some strange
> AQ behaviour. We create
> a queue with a sort_list of 'ENQ_TIME,PRIORITY'.
> What we're seeing is
> that if we have two concurrent sessions enqueuing to
> the same queue with
> the PRIORITY value coming from a sequence.
>
> The enqueue timeline looks like:
>
> Session 1: Session 2:
>
> Enqueue priority 10
> Enqueue priority 11
> Enqueue priority 12
> COMMIT
>
> Enqueue priority 13
> Enqueue priority 14
> Enqueue priority 15
> COMMIT
>
> We do both of these quick succession such that the
> ENQ_TIME is the
> same for both of the sessions. After the two
> sessions run, we dequeue
> from the queue using a single session and the
> messages come out like this:
>
> Priority 10
> Priority 13
> Priority 11
> Priority 14
> Priority 12
> Priority 15
>
> We've tried to use the navigation of FIRST_MESSAGE
> and NEXT_MESSAGE and
> the behavior doesn't change. Has anyone seen this
> or know how to fix it?
>
>
> --
> Stephen Rospo Principal Software Architect
__________________________________
Do you Yahoo!?
Yahoo! Mail - You care about security. So do we.
http://promotions.yahoo.com/new_mail
--
//www.freelists.org/webpage/oracle-l
Other related posts:
