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?

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

> 
> 
> That is fantastic, thanks.  We are going to do a prototype of this
> architecture.

Last time I looked (back in 8i) AQ was implemented by writing the messages to a table, and also involves some internal polling of that table. The JMS wrapper to AQ was also pretty buggy and had portions that were not implemented - hopefully they have finished it. You are probably better off using the PL/SQL AQ interface.

--Peter Received on Mon Oct 18 2004 - 15:27:24 CDT