Move from non-locking to locking reads while processing a batch in background thread.

[ironhide0396]

Currently, when processing backlogged or stale items, what we do is

1. Create a new txn (say time T1)
2. Select a batch for processing (leveraging the batch size, current timestamp, next attempt etc)
3. Update the lastProcessedTime for all of these
4. Commit txn (say time T2)

Now in this duration between [T1, T2), if there is any other node having the same Outbox configured ( quite an everyday use-case, thanks to HA), the exact same set of rows will be picked up, lastProcessedTime updated with respect to this new transaction.

However, because during the actual processing we take a explicit lock (leveraging FOR UPDATE SKIP LOCKED), the work started by T2 will always be wasted.

My proposal is to move the entire thing in a single locked transaction, something like

1. Start txn. (say T1)
2. Read rows using FOR UPDATE SKIP LOCKED
3. Process the rows. Internally, we can delegate the processing of each row to the executor so we maintain the parallelism we currently are achieving with a single lock per row processing.
4. Commit the transaction once all rows are processed (usual cleanup process of deleting on success, updating attemptCount, blocked, nextAttemptTime etc as usual).

The only possible drawback I see in this is the waiting time for DB locks is increased and depends on the time it takes for the slowest consumer to process a message.

Alternative : Add a randomness to the select query so that chances of the same batch or overlapping rows picked up are greatly reduced by two nodes.

[badgerwithagun]

he work started by T2 will always be wasted.

I'm unclear on this bit. The update to lastProcessedTime is designed to prevent reads overlapping (via a code check if necessary) and the SKIP LOCKED is designed to prevent wasted work entirely by not allowing both threads to select (and lock) the same records.

Could you clarify? It sounds like something the code is specifically designed to avoid but that it's not working as intended.

[ironhide0396]

Apologies. It was an oversight on my end. The rows are always read in an SKIP LOCKED manner is something I missed. Closing the discussion.