Possible conflict between Chancy._push_job_sql and Worker._maintain_updates

[AudeCstg]

Hey !

I've been using Chancy in production for a few months and it scales really well but lately we've had a few crashes and we suspect a deadlock between Chancy._push_job_sql() and Worker._maintain_updates() that crashes the worker.

The issue we encounter is that we periodically push many jobs with a unique key, and when those pushes overlap with the worker's internal update batch, we get a PostgreSQL deadlock.

I have this error when the push_many fails

DeadlockDetected
deadlock detected
DETAIL:  Process 22641 waits for ShareLock on transaction 1421562613; blocked by process 27210.
Process 27210 waits for ShareLock on transaction 1421562873; blocked by process 22641.
HINT:  See server log for query details.
CONTEXT:  while inserting index tuple (489660,1) in relation "chancy_jobs"

Here is the scenario I have identified :

1. _maintain_updates() starts a transaction and runs executemany to update N jobs in the chancy_jobs table (state, attempts, errors, etc.) locking rows in insertion order of the outgoing queue
2. _push_job_sql() (called via push / push_many) does INSERT ... ON CONFLICT (unique_key) DO UPDATE SET state = state which takes a row-level lock on the conflicting row (code)
3. If both transactions target overlapping rows in different order → PostgreSQL detects a deadlock and kills one of them
4. The killed transaction sometimes is the one in _maintain_updates, which raises an exception and re-queues the updates, but the exception also propagates and crashes the worker

For the fix I wanted to take 2 actions :

• In the _maintain_updates() specifically catch the deadlock exceptions and retry them once without raising => should avoid the crash
• In _maintain_updates() and _push_job_sql() add an ordering by unique_key (since jobs without unique_key don't have conflict no issue for their order) before inserting to avoid deadlocks

I plan to try them out on a fork first and then if they work well I can open a PR.

Did you already encounter this issue or is there something I miss ?

Thanks !

[TkTech]

(Related: #88)

I have not noticed this issue, thanks for the report. I'm working on a path release for #88 which will make the various _maintain_* tasks a bit more forgiving of transient issues with retries and a backoff. I guess we may want to expand that pattern to all operations so _push* is safer as well.

Let me know how your fork works out - if we can avoid deadlock detection entirely that'll be great.