Search This Blog

28 June 2007

Quick musing on the "Queue" engine.

Brian postulates the idea of a "Queue" engine and why it is impractical. I agree that a queue storage engine doesn't really work too well. What would work better would be either a special 'queue' object type which may use any user accessible storage engine or perhaps a non-standard extension to the SQL syntax:


DEQUEUE message FROM queue_table LIMIT 1;


This can be done in (perhaps) the MySQL Proxy, where it is translated into a SELECT/DELETE combination or it can be added to the server, and should not cause much parser complications.


Then any storage engine may be used for a queue, purely dependent upon the user's requirements as some people will be satisfied with a non-persistent MEMORY based queue, MYISAM queues will be adequate for many and for a few who need transactions, there is INNODB and FALCON.

3 comments:

Unknown said...

Look at what was done in SQL 2005. A dedicated enqueue and dequeue command should be provided to properly handle in sequence FIFO operations. The queue should provide a view to allow selecting against the contents to preview the pending entries. The value of the queue is to guarantee in-sequence delivery of queued entries. The more complex conversation layer in SQL 2005 is not a necessity. Ideally, queues should be able to transactional or not depending on the users needs.

Unknown said...

I was wondering... why people don't just have a queue with a "read flag" which is a UUID.

eg you would be able to UPDATE queue SET read="UUID" WHERE read is NULL ORDER BY id LIMIT 1. (On InnoDB it would be in a transaction so no double reads)

Then you can read the queue entry via SELECT (since you still remember the UUID you passed to the update the first place).

And delete it (if you want to) when done.

Peeking could also just be a straight SELECT * WHERE read is NULL ORDER BY id LIMIT 1

A little messier than DEQUEUE but still pretty straight forward. Isn't it? Or am I missing something?

Antony said...

I personally think it is a bit redundant as users are quite able to write a set of stored procedures to queue and dequeue for themselves.

The most recent specifications I have here are the SQL 2003 specifications so I cannot comment about the contents of SQL 2005.