How do I transactionally poll a database queue table with rollback?

Question

I wish to setup a database table that will be used as a message queue. The table will have messages inserted into it with a unique id and a timestamp and a status of 'PENDING'.

Assuming that the insertion into this table is properly handled, I wish to know what is the best way to transactionally process messages from this table using a simple HSQLDB 2.0 database (although this question should apply to all transaction supporting databases).

I wish to read the next message with a status of 'pending' and make sure that no other queue processor can also process the same record then either commit or rollback.

I include some code snippets for how I plan to achieve this using plain old JDBC.

• Will this work?
• Are there better alternatives?

DDL:

create table message_queue (
    qidx integer,
    message varchar(120),
    status varchar(20),
    inserted_date timestamp,
    inserted_by varchar(20),
    processed_date timestamp,
    processed_by varchar(20),
)

insert into message_queue values (1,'Important message here','PENDING','2010-08-10 00:01:00', 'BOB', null,null)

Here is my queue reading SQL:

SET AUTOCOMMIT FALSE
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
START TRANSACTION
    DECLARE nextID INTEGER DEFAULT 0
    SET nextID = select max(qidx) from message_queue where status = 'PENDING' 
    update message_queue set status = 'CONSUMED' where QIDX = nextID
    select * from message_queue where QIDX = nextID
ROLLBACK

Here is my connection code fragments:

    conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
    try {
        String message = getNextMessage(conn); // uses sql in snippet
        processMessage(message);
        conn.commit(); // should commit
    } catch (Exception e) {
        conn.rollback(); // should rollback update
    }

Answer 1

Queueing is definitely a non-trivial architecture when you have to consider the multi-user nature of databases. Generally speaking, you don't want to implement these kinds of things yourself. The problem, as I'm sure you quickly discovered, is that you only want one process to get a single message when there are multiple readers attempting to read from the same queue. That is, you have to ensure that a message is processed only once. So then you start thinking "ok, well, I'll update that record so the other processes won't get it", but then you realize that until you commit the other processes won't see your change. If you lock the record, then you can't have another process read from the queue until you have finished processing the first one, essentially serializing the whole thing.

Queue transactions, by design, have to be somewhat orthogonal to the transactions of the processes reading from the queue. Unfortunately, this is one area where the implementation is going to be significantly different for each database.

Perhaps a better approach would be to use another library (not necessarily the database) for doing messaging. Since HSQLDB doesn't have built-in support for queueing, a different (java) implementation of queues may be a tool for this situation.