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

> > 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?

2PC is great and everything - but it doesn't guarantee atomicity. It is possible to successfully first commit to a queue and before the database is committed the message can be pulled off the queue by the consumer. At this point the message is useless because the database transaction hasn't been committed.

If the AQ + database commit is under the same SCN, then when the consumer gets the message there is a guarantee that the database will have committed at that point.  

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

That is fantastic, thanks. We are going to do a prototype of this architecture. Received on Mon Oct 18 2004 - 00:41:40 CDT