Re: Advanced Queue ordering?

From: Martic Zoran <zoran_martic_at_yahoo.com>
Date: Wed, 8 Jun 2005 00:08:02 -0700 (PDT)
Message-ID: <20050608070802.82100.qmail@web52601.mail.yahoo.com>

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_at_vallent.com> 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

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Jun 08 2005 - 03:13:06 CDT