作业队列作为具有多个使用者的SQL表（PostgreSQL） [英] Job queue as SQL table with multiple consumers (PostgreSQL)

问题描述

我有一个典型的生产者-消费者问题：

I have a typical producer-consumer problem:

多个生产者应用程序将作业请求写入PostgreSQL数据库上的作业表。

Multiple producer applications write job requests to a job-table on a PostgreSQL database.

作业请求的状态字段开始包含创建时的队列。

The job requests have a state field that starts contains QUEUED on creation.

有多个消费者应用程序当生产者插入新记录时，将通过一条规则通知：

There are multiple consumer applications that are notified by a rule when a producer inserts a new record:

CREATE OR REPLACE RULE "jobrecord.added" AS
  ON INSERT TO jobrecord DO 
  NOTIFY "jobrecordAdded";

他们将尝试通过将状态设置为RESERVED来保留新记录。当然，只有依靠消费者才能成功。所有其他消费者都不能保留相同的记录。他们应该保留状态= QUEUED的其他记录。

They will try to reserve a new record by setting its state to RESERVED. Of course, only on consumer should succeed. All other consumers should not be able to reserve the same record. They should instead reserve other records with state=QUEUED.

示例：
一些生产者将以下记录添加到表 jobrecord 中： / p>

Example: some producer added the following records to table jobrecord:

id state  owner  payload
------------------------
1 QUEUED null   <data>
2 QUEUED null   <data>
3 QUEUED null   <data>
4 QUEUED null   <data>

现在，两个消费者 A ， B 想要处理它们。它们同时开始运行。
一个人应该保留id 1，另一个应该保留id 2，然后第一个完成的人应该保留id 3，依此类推。……

now, two consumers A, B want to process them. They start running at the same time. One should reserve id 1, the other one should reserve id 2, then the first one who finishes should reserve id 3 and so on..

在纯多线程世界中，我将使用互斥锁来控制对作业队列的访问，但使用者是可以在不同计算机上运行的不同进程。他们只访问同一个数据库，因此所有同步都必须通过数据库进行。

In a pure multithreaded world, I would use a mutex to control access to the job queue, but the consumers are different processes that may run on different machines. They only access the same database, so all synchronization must happen through the database.

我阅读了很多有关PostgreSQL中并发访问和锁定的文档，例如 http://www.postgresql.org/docs/9.0/interactive/explicit -locking.html
在Postgresql中选择未锁定的行
PostgreSQL和锁定

I read a lot of documentation about concurrent access and locking in PostgreSQL, e.g. http://www.postgresql.org/docs/9.0/interactive/explicit-locking.html Select unlocked row in Postgresql PostgreSQL and locking

从这些主题中，我了解到，以下SQL语句应满足我的需要：

From these topics, I learned, that the following SQL statement should do what I need:

UPDATE jobrecord
  SET owner= :owner, state = :reserved 
  WHERE id = ( 
     SELECT id from jobrecord WHERE state = :queued 
        ORDER BY id  LIMIT 1 
     ) 
  RETURNING id;  // will only return an id when they reserved it successfully

不幸的是，当我在多个使用者中运行该ID时，在大约50％的时间中，它们仍然保留相同的记录，既处理它又覆盖另一个的更改。

Unfortunately, when I run this in multiple consumer processes, in about 50% of the time, they still reserve the same record, both processing it and one overwriting the changes of the other.

我缺少什么？我该如何编写SQL语句，以便多个使用者不会保留同一记录？

What am I missing? How do I have to write the SQL statement so that multiple consumers will not reserve the same record?

推荐答案

在此处阅读我的文章：

带有锁定的PostgreSQL一致性并选择更新

如果使用事务处理和LOCK TABLE，则不会有任何问题。

If you use transaction and LOCK TABLE you will have no problems.

这篇关于作业队列作为具有多个使用者的SQL表（PostgreSQL）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！