java - 如何以事务方式轮询带回滚的数据库队列表？

Question

我想设置一个数据库表，用作消息队列。该表将插入具有唯一 ID 和时间戳以及“待定”状态的消息。

假设插入到该表中的操作得到正确处理，我想知道使用简单的 HSQLDB 2.0 数据库事务处理来自该表的消息的最佳方法是什么(尽管这个问题应该适用于所有支持事务的数据库)。

我希望读取状态为“待定”的下一条消息，并确保没有其他队列处理器也可以处理相同的记录，然后提交或回滚。

我包含了一些代码片段，说明我计划如何使用普通的旧 JDBC 实现此目的。

• 这行得通吗？
• 有更好的选择吗？

数据链接:

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)

这是我的队列读取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

这是我的连接代码片段:

    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

在这些情况下使用的一般模式(跨数据库、无锁、无等待 - 准确地说是乐观锁):

1. 多个工作人员中的一个从状态为“待定”的队列表中进行选择。表格包括一个版本号列(整数)。
2. 工作人员查看它选择的项目列表，并尝试将每个项目更新为“进行中”(或其他)状态。更新语句的 where 子句包括“where version_number =”，显然还包括项目的主键。
3. 如果更新语句返回 1 作为更新的行数，则该工作人员成功保留了该项目，并且可以继续工作。如果更新语句返回 0 行已更新 - 此项目已被其他工作人员选择，因此该工作人员应该直接跳过它。