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 Received on Sat Oct 16 2004 - 23:56:03 CDT