title: How do I transactionally poll a database queue table with rollback?
body: <p>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'.</p>
  
  <p>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).</p>
  
  <p>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.</p>
  
  <p>I include some code snippets for how I plan to achieve this using plain old JDBC.</p>
  
  <ul>
  <li>Will this work?</li>
  <li>Are there better alternatives?</li>
  </ul>
  
  <p>DDL:</p>
  
  <pre><code>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)
  </code></pre>
  
  <p>Here is my queue reading SQL:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>Here is my connection code fragments:</p>
  
  <pre><code>    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
      }
  </code></pre>