Re: AQ vs. PL/SQL for implementing a messaging queue

Steven Garcia wrote:

> Hi, for our application we need to write a message to a queue and also
> commit a database transaction at the same time. Basically calls for a
> 2PC solution. We are looking into either 1) using AQ for our
> messaging queue or 2) just creating a table and writing SQL that
> simulates a FIFO queue. So I have a few questions about both
> strategies. Our application is written in Java.
>
> 1) AQ is a true messaging queue product (from what I've read.) Also
> in Java we can put messages onto Oracle AQ by providing a
> java.sql.Connection object based on the sample applications I've seen.
> Given that we also commit DML to the database, it implies that with a
> single java.sql.Connection object we can put a message onto AQ and
> commit DML as one transaction. Specifically this means that the
> message and the DML are commited at the *exact same time*, with the
> same Oracle SCN. This is not the case with 2PC.
>
> Is this assumption true? If the same java.sql.Connection mediates
> both the message queue and any DML transactions, are they committed at
> the same time? If so, then it's not really 2PC and that is extremely
> attractive.
>
> We can use AQ natively or via their JMS port but it actually seems
> easier to use it natively.
>
> 2) We could also design our own tables and use SQL or PL/SQL to
> simulate a messaging queue. I have been playing around with the
> "SELECT ... FOR UPDATE (NOWAIT)" statement where we would get the next
> record in a database based on a sequence. Our tests show that it
> works (although they are not heavy duty tests), I'm concerned this
> fake implementation of a messaging queue system will break down under
> heavy load. Has anyone tried to simulate a messaging queue using SQL?
> What kind of results did you have?
>
> Thanks, Steve

I was with you right up to the point where you made the assumption that the SCN numbers would be the same. Do you really care? And if so why?

But yes I think AQ would likely meet your needs if not the exact letter of your requirements.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)