# Optimistic locking vs separate lock store for background workers

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?

• It depends on the odds that values in B change during a computation, and how much you value stale data in R. At one extreme (low rate of change in B, stale value worthless) you'd choose a very different method than at the other (high rate of change in B, recent values of R could be used). Can you elaborate on this?
– user44761
Mar 31, 2018 at 16:59
• @Tibo Each value on table B is associated with an user. Most of the time the value only change when this user does something. And the row R is based on a custom formula for this user. So most of the time the computation is done before the user do something else causing table B to change. But sometimes row in B are massively updated. So everything can change very quickly. The only thing I care about row R is that, at the end, the value is correct. It's better if R get a new value for most change in B but I don't care if it jumps values Apr 1, 2018 at 7:48

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.

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.

• Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center.
– Community Bot
Sep 3, 2023 at 15:37