[Bug]: deadlock in worker cleanup leads to zombie workers

[parkan]

Description

still investigating this but we are observing thousands of deadlocks in worker cleanup calls:

 blocked_pid | blocking_pid | blocked_user | blocking_user |    blocking_duration    |                           blocked_query                           |                  
    blocking_query                       
-------------+--------------+--------------+---------------+-------------------------+-------------------------------------------------------------------+------------------
-----------------------------------------
      193590 |       190674 | singularity  | singularity   | 3 days 13:38:47.888695  | DELETE FROM "workers" WHERE last_heartbeat < $1                   | DELETE FROM "work
ers" WHERE last_heartbeat < $1
      184265 |       190674 | singularity  | singularity   | 3 days 13:38:47.888695  | DELETE FROM "workers" WHERE last_heartbeat < $1                   | DELETE FROM "work
ers" WHERE last_heartbeat < $1
      216642 |       190674 | singularity  | singularity   | 3 days 13:38:47.888695  | DELETE FROM "workers" WHERE last_heartbeat < $1                   | DELETE FROM "work
ers" WHERE last_heartbeat < $1
      187480 |       190674 | singularity  | singularity   | 3 days 13:38:47.888695  | DELETE FROM "workers" WHERE last_heartbeat < $1                   | DELETE FROM "work
ers" WHERE last_heartbeat < $1
      191185 |       190674 | singularity  | singularity   | 3 days 13:38:47.888695  | DELETE FROM "workers" WHERE last_heartbeat < $1                   | DELETE FROM "work
ers" WHERE last_heartbeat < $1

Steps to Reproduce

appears to happen when a larger number of workers is enabled, especially across multiple parent processes

Version

tbd

Operating System

Linux

Database Backend

PostgreSQL

Additional context

No response

[parkan]

ok the issue appears to be related to prep remove, which we know is a buggy codepath as per yurko's report

one deadlock had 5000+ queries waiting due to recursive locks:

blocking_pid | blocking_user |    blocking_duration    |                      blocking_query                       
--------------+---------------+-------------------------+-----------------------------------------------------------
       131576 | singularity   | 21 days 19:39:29.670189 | DELETE FROM "preparations" WHERE "preparations"."id" = $1
       155274 | singularity   | 15 days 20:19:33.888462 | DELETE FROM "workers" WHERE last_heartbeat < $1
       155279 | singularity   | 15 days 20:19:33.885599 | DELETE FROM "workers" WHERE last_heartbeat < $1
       155287 | singularity   | 15 days 20:19:33.846765 | DELETE FROM "workers" WHERE last_heartbeat < $1
       155288 | singularity   | 15 days 20:19:33.837723 | DELETE FROM "workers" WHERE last_heartbeat < $1

second example, much less egregious but still deadlocked:

 blocking_pid | blocking_user |   blocking_duration   |                          blocking_query                           
--------------+---------------+-----------------------+-------------------------------------------------------------------
       210426 | singularity   | 1 day 17:23:38.72573  | DELETE FROM "preparations" WHERE "preparations"."id" = $1
       210427 | singularity   | 1 day 17:23:09.309279 | UPDATE "preparations" SET "name"=$1,"updated_at"=$2 WHERE id = $3
       216642 | singularity   | 00:30:42.052659       | DELETE FROM "workers" WHERE last_heartbeat < $1

I was able to clear both by terminating the lynchpin query (the DELETE)

reviewing the remove codepath and changing the worker cleanup code to remove table-wide locks is going to be needed to fix this