Duplicate Spawning of Jobs

[thevaibhav-dixit]

Due to concurrent polling there were duplicate jobs being spawned. This behaviour is caused because there is no row locking mechanism in place. So when multiple instance ran concurrently they could end up selecting the same messages leading to duplicates
this is the query which i have identified is causing the issue.
Potential Fix:
Use FOR UPDATE to lock the selected rows to prevent concurrent access.

[Diggsey]

UPDATE queries automatically take row-level locks, but perhaps the locks from the outer query happen too late.

Do you have a test-case that can reproduce the issue?

[bodymindarts]

The issue is not in the update query - it's in the inner SELECT query. The SELECT runs in its own context and just because the outer UPDATE locks the rows does not prevent the inner SELECT to run concurrently.

We observed duplicate job_ids running after spawning ~ 200 jobs in short succession and having them worked off by 2 workers in parallel.

The PR fixed the behaviour.