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
etcdor 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?