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

stevengarcia_at_yahoo.com (Steven Garcia) wrote in message news:<7f430eb1.0410180844.3be8a055_at_posting.google.com>...
> > >Has anyone tried to simulate a messaging queue using SQL?
> >
> > Yes, I was a project where we implemented a message queue in PL/SQL
> > using a method similar do what you described. Two systems have been in
> > production for about 4 years now.
>
> I'm interested in hearing more about the details of how you
> implemented your queue. If you have a minute could you describe how
> (via SQL) you enqueue and dequeue from your database table?

Create a table for your queue data: one number column as a PK and one varchar/clob column for data. Create another table with two columns keeping track of the head and tail of the queue (FKs to the queue data's PK).

The enqueue/dequeue procedures inserts/reads records from the queue data table based on the tail/head pointers. Use "SELECT for UPDATE" on the tail and head pointers if you have multiple consumers/producers.

Something I forgot to mention is that in our system we only had one data producer, although I can't see why multiple producers would cause a problem. We usually had two or three consumers.

Even though this solution works (at least it did for us), is there a reason for not using AQ? Received on Mon Oct 18 2004 - 23:21:08 CDT