Brian "Krow" Aker's Idle Thoughts - Queue servers, temporal, stab at it...

As mentioned before, since FooCamp I've been having ideas around
queue services:

http://krow.livejournal.com/531369.html
http://krow.livejournal.com/530752.html

I've been thinking about this a bit more, and instead of working on
the concept of a straight queue mechanism (like what Oracle has),
I've been thinking more about how web services handle this, in
particular services like Amazon's.

Instead of a flat queue structure, shoot for a temporal queue.

A range select should force rows to go away for a set period of time,
until the timer run's out. This gives the processing application time
to deal with the row, and if it doesn't make it back in time, the row
should reappear to go back in the queue. The semantics for this tend
to lend them to SQL with these axioms:

A key lookup should always return a row.

A key delete/update should always puncture the temporal nature
of the queue. A flat scan on the queue should not puncture temporal
nature.

Scans pick up whatever rows they can, and flag rows they see as
hidden for a certain period of time.

I've hacked up an engine to do exactly this. Caveat's:

Its not durable at this point.

Right now it is thread safe, but it is possible to see phantom
rows on SMP hardware.

Very little testing has been done.

A primary index must be created, and it must be a number.

Timer is set to 60 seconds.

What follows is a bunch of examples. You can grab the latest copy of
it from here:
http://hg.tangent.org/queue_engine/archive/tip.tar.gz

I've not bundled this up for a release (and may never, this is just a
practical prototype for me... but I do need something like this...)

CREATE TABLE `foo` (
`a` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`b` varchar(200) DEFAULT NULL,
`c` varchar(200) DEFAULT NULL,
UNIQUE KEY `a` (`a`)
) ENGINE=QUEUE DEFAULT CHARSET=latin1

mysql> insert into foo value ("", "burger", "nod"), ("", "This is
mine", "this is fred's");
Query OK, 2 rows affected, 2 warnings (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0

mysql> select * from foo;
+---+--------------+----------------+
| a | b | c |
+---+--------------+----------------+
| 1 | burger | nod |
| 2 | This is mine | this is fred's |
| 3 | burger | nod |
| 4 | This is mine | this is fred's |
| 5 | burger | nod |
| 6 | This is mine | this is fred's |
| 7 | burger | nod |
| 8 | This is mine | this is fred's |
+---+--------------+----------------+
8 rows in set (0.00 sec)

mysql> select * from foo;
Empty set (0.00 sec)

mysql> select * from foo WHERE a=3;
+---+--------+------+
| a | b | c |
+---+--------+------+
| 3 | burger | nod |
+---+--------+------+
1 row in set (0.00 sec)

mysql> select * from foo;
Empty set (0.00 sec)

mysql> delete from foo WHERE a=3;
Query OK, 1 row affected (0.02 sec)

mysql> select * from foo;
+---+--------------+----------------+
| a | b | c |
+---+--------------+----------------+
| 1 | burger | nod |
| 2 | This is mine | this is fred's |
| 4 | This is mine | this is fred's |
| 5 | burger | nod |
| 6 | This is mine | this is fred's |
| 7 | burger | nod |
| 8 | This is mine | this is fred's |
+---+--------------+----------------+
7 rows in set (0.00 sec)