Optimistic locking vs separate lock store for background workers

Question

I need to compute the value of a row R on a table based on rows queried on another table B. I need to do it in a queue worker because the computation of the value can be slow. A message is queued everytime B change.

If the two workers start the computation of row R almost at the same time, and the data on table B changed the final value of row R may be wrong. I just want the final value to be the right, I don't care if I jump a computation.

To do that I can :

• Do optimistic lock on row R. Before the computation I read the version of R and when I update I do where version = ?. It ensures there are an independent computations n times if table B change n times (not 100% sure about that ^^). And I re-queue if a transaction fails.
• Store a lock in etcd or something like this, the advantage is to avoid useless computations... And I re-queue if the row is locked
• Use a FIFO queue (but I would like to avoid that)
• maybe something else?

What do you think is the best option?

Answer 1

A message is queued everytime B change.

Arrange so that the message is monotonically versioned (e.g. after the first message is assigned generation: 1, the next message can only use a greater number).

Arrange so that a message is only consumed by at most one worker.

Workers can disappear in the middle of computation, so a retry mechanism is needed: periodically inject dummy messages from a watchdog thread/threads.

I don't care if I jump a computation.

How worker commits its work:

• wait to acquire mutex named TableR
• ensure their result is newer than the latest stored result (> generation)
• store their result (or drop it altogether if outdated)
• release mutex TableR

Workers can disappear when holding a mutex - your mutex implementation must be smart enough to detect that (e.g. mutex backed by keepalives). Let's say SELECT ... FOR UPDATE is a simplistic but good-enough implementation of such mutex.

This works for both distributed and local workers.

---

Optional, but handy: arrange for workers to drop computation quickly as soon as it becomes useless.

Most of the time the computation is done before [the next message about B arrives]

This is a crucial assumption. For example if instead you expect 1 message per second and a computation needs 2 seconds you cannot do what follows below.

Arrange a pub/sub, which sends all the requests to all the workers. (This is an optional addition to the message queue above, not a replacement of it!) A worker drops ongoing computation if it receives a request with a greater generation.

Alternatively to a pub/sub, keep at most one worker at any time, just get it restarted after it disappears. It seems that multiple workers are not strictly required for this question.

Answer 2

Optimistic lock should be the default approach although using different isolation levels (serialisable, repeatable reads, read committed, read uncommitted) for database transaction or locking reads (select ... for update, select ... for share) may represent a viable solution.