Re: Queues
- From: Adam Musch <ahmusch@xxxxxxxxx>
- To: joe_dba@xxxxxxxxxxx
- Date: Thu, 26 Aug 2010 08:20:49 -0500
Well, there's less of a difference in 11g now that Oracle's exposed
the SKIP LOCKED clause, but before, how would you do FIFO processing
or have multiple consumers of a heap table efficiently each be the
only consumer of any given able record while still maintaining
appropriate transactional integrity?
You'd have your queue table (QT), which you'd have to select from
where rownum = 1. However, you'd have to select for update, which
blocks. So you're serialized at that point for the duration of the
entire process you dequeued the record for.
So to minimize that serialization time, you create an in-flight queue
table (IFQT), which are the records that some consumer is processing
right now. So now the process is select from QT for update where
rownum = 1, insert into IFQT, delete from QT where rowid = :qt_rowid,
commit. And if consumer crashes, you have figure out some cleanup
processing to reprocess the record it had in flight.
Or you can set up a queue, and simply use DBMS_AQ.DEQUEUE and get
queue transactionality "for free."
Doing publish-subscribe is even harder. You've paid for this
functionality in Oracle already. If you have a need to use it, why
not use it instead of rolling your own?
On Wed, Aug 25, 2010 at 10:33 AM, Joe Smith <joe_dba@xxxxxxxxxxx> wrote:
> I have been reading the PDFs and documents on MOS, but can someone tell me
> the difference or advantage of using Queues instead of writing messages to a
> regular heap-organized Oracle table?
>
> Or what is the real advantage of queues that I am missing?
>
--
Adam Musch
ahmusch@xxxxxxxxx
--
//www.freelists.org/webpage/oracle-l
Other related posts:
