问题描述
我想了解如何使用 postgres 和 PDO （php）实施工作伫列。
I would like to find out a good way to go about implementing a jobs queue using postgres and PDO (php).
基本上我有一个 events 表，其中记录了应用程序的事件和某种形式的预定处理器c $ c> proc ），它将定期处理一次检索一个事件并执行某些例程以响应它（并且取决于事件的性质）。
Basically I have an events table where the app's events are logged and some form of scheduled processor (say proc) that will regularly take care of retrieving an event at a time and execute certain routines in response to it (and depending on the nature of the event it self).
显然，一旦 proc 的实例开始处理一个事件，我需要将该行标记为正在进行，例如：
Clearly, as soon as an instance of proc starts working on an event, I need to mark the row as ongoing, like that:
UPDATE events SET status = "ongoing" WHERE id = 3; -- >> QUERY 1 <<
好！ proc 现在可以根据事件的类型和它的有效负载做它的业务，没有其他线程将处理id = 3的事件，因为它现在正在进行。
Fine! proc can now do its business according to the type of event and its payload and no other thread will deal with the event of id = 3 as it is now ongoing.
当事件3完成 proc 解决，所以再次，没有其他线程将来，将来，照顾事件3.这里我们去：
When proc is done with event 3 it marks it as 'resolved' so that, again, no other thread will, in the future, take care of event 3. Here we go:
UPDATE events SET status = "resolved" WHERE id = 3; -- >> QUERY 2 <<
现在我关心的是这必须在一个事务内完成，所以我会有：
Now my concern is that this must be done inside a transaction, so I would have:
BEGIN;
-- QUERY 1
-- VARIOUS OTHER QUERIES TAKING A LOT OF TIME
-- QUERY 2
COMMIT;
据我所知，在事务中，由QUERY 1操作的更改只有其他线程当整个事务提交时。这意味着虽然 proc （实例1）正在做耗时的工作（QUERY 1和QUERY 2之间的代码），但是其他一些实例可能会读取 events 表，并认为没有人在关心事件3，并继续做它的东西。显然，会扰乱整个事情，破坏队列的状态。
As far as I know, when inside a transaction, the change operated by QUERY 1 is only visible to other threads when the whole transaction is committed. That implies that while proc (instance 1) is doing the time consuming work (the code between QUERY 1 and QUERY 2) some other instance of it might read the events table and think that no one is taking care of event 3 and move on doing stuff with it. Clearly that would mess up the whole thing and corrupt the state of the queue.
所以我的问题是：如何保留 proc 的事务样式，同时使事件3的状态（从自由到正在进行）立即改变
So my question is: how do I preserve the transactional style of proc and, at the same time, make the change of state of event 3 (from free to ongoing) immediately visible from outside the transaction?
推荐答案
在提交时，这是不可能的。 PostgreSQL没有， QUERY1 是无意义的，因为它的效果将被 QUERY2 覆盖，之后才可见。
As it is presented, this is not possible. PostgreSQL doesn't have dirty reads, and QUERY1 is pointless since its effect will be overrided by QUERY2 before ever being visible.
但即使它是立即提交和可见（如果独立提交），这也不会令人满意。在高并发环境中，队列中的行的SELECT和其具有正在进行的状态的UPDATE之间的时间足以让另一个工作者选择它并创建混乱你想避免。
But even if it was committed and visible immediately (if committed independantly), this wouldn't be satisfying anyway. In a high concurrency environment, the time between the SELECT of a row in the queue and its UPDATE with the ongoing state is enough for another worker to SELECT it too and create the confusion you want to avoid.
我认为应该工作的一个接近的替代设计，可以通过替换你的 QUERY1 在上队列ID。
I think a close alternative to your design that should work can be achieved by replacing your QUERY1 with an advisory lock on the queue ID.
伪代码：
BEGIN;
SELECT pg_try_advisory_xact_lock(3) INTO result;
IF result=true THEN
-- grabbed the exclusive right to process this entry
-- recheck the status now that the lock is taken
SELECT status INTO var_status FROM events WHERE id=3;
IF var_status='needs-to-be-done' THEN
-- do the work...
-- work is done
UPDATE events SET status = 'resolved' WHERE id = 3;
END IF;
ELSE
-- nothing to do, another worker is on it
END IF;
COMMIT;
这种锁在事务结束时自动释放。
与SELECT后跟UPDATE相反，锁保证被原子地授予或拒绝。
This kind of lock is automatically released at the end of the transaction.Contrary to the SELECT followed by UPDATE, the lock is guaranteed to be granted or denied atomically.
这篇关于队列在php和postgres的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！