Comment by asah
9 years ago
(possibly dumb q, pls be gentle)
Hmmm... What happens if the application crashes immediately after removing the work item but before it can do anything else? Doesn't this break exactly-once semantics... ?
i.e. wouldn't a complete implementation include a second table with "who's working on what" and a transaction that moves the records from one table to the either... and come to think of it, why not store both tables in the same place? I.e. don't delete records, just mark them as taken and include a timestamp so we can timeout and give the work to another worker?
UPDATE queue_table SET taken_by = :myId, time_taken = now() WHERE id = ( SELECT id FROM queue_table WHERE taken_by IS NULL LIMIT 1 FOR UPDATE)
Note: completion can be signalled by setting taken_by to NULL, or but adding another column e.g. completion time, which then enables computing stats on completion times.
For high volume systems, we eventually want to garbage collect but that's easy since we have timestamps, i.e. put a partial index on timestamp (WHERE time_taken IS NOT NULL) and scan the oldest ones...
> What happens if the application crashes immediately after removing the work item but before it can do anything else? Doesn't this break exactly-once semantics... ?
If the connection is broken the transaction would be aborted and the lock released. If the worker hit an infinite loop or something like that you'd use something like `idle_in_transaction_session_timeout` to set a transaction timeout and/or have a worker monitoring system in place to kill long running jobs.
The important thing to note is that a single DB instance and a client still comprises a distributed system. You still have almost all the same problems with 'exactly-once' semantics you would with a distributed queue. You should make all processing jobs idempotent and support retry regardless of the tech backing the queue if you want a system that provides effectively exactly-once semantics.
ah! you're assuming the work is performed inside the same transaction as the dequeue operation, and locks held for the duration ?
If so...
While I suppose row level locking technically solves contention, it still feels like we're "asking for trouble" in holding databases locks while clients perform arbitrarily long work operations. There's also practical issues when the work itself is distributed and the original client can't itself keep state around, i.e. it has to end the low level transaction.
Hence my poor-man's question/proposal using worker IDs and timeouts...
> ah! you're assuming the work is performed inside the same transaction as the dequeue operation, and locks held for the duration ?
Yes that is the model the linked post is proposing, see the Example.
> While I suppose row level locking technically solves contention, it still feels like we're "asking for trouble" in holding databases locks while clients perform arbitrarily long work operations. There's also practical issues when the work itself is distributed and the original client can't itself keep state around, i.e. it has to end the low level transaction.
Not that I recommend using PG as a queue, but you have most/all those problems with any queuing backend. A problem you may have that is PG specific is that the # of open connections/transaction could become quite large with a lot of workers and PG doesn't play well with a lot of connections, it uses a process-per-connection model.
The row lock is held by the RDBMS itself on behalf of the client. When the client goes away, times out (configuration) etc. -- the transaction aborts -- then the RDBMS releases it. You don't get exactly once here, because between performing some action on the work item and marking the item as finished and committing you can still crash. This is an at least once solution in the general case.
sorry!! I'm not sure I understand your response? when you say 'You don't get exactly once here' what is does 'here' refer to?
Upon reflection, true exactly-once semantics requires multi-phase commit, transitively through the system - this sort of tight coupling is tricky in practice.
Hence my question/proposal, which is practical/poor-man's solution using timeouts.