The PR fixes Postgres deadlocks caused by SELECT FOR UPDATE statements that can be blocked by transactions that modify child tables. The LOCKING contributing guide is updated to include this caveat:

If you SELECT FOR UPDATE from a table that is referenced in a child table via a foreign key, it can lead to deadlocks if the child table is updated because Postgres will issue a FOR KEY SHARE lock on the parent table rows to ensure valid foreign keys. For this reason, you should always do SELECT FOR NO KEY UPDATE (.with_for_update(key_share=True)) if primary key columns are not modified. SELECT FOR NO KEY UPDATE is not blocked by a FOR KEY SHARE lock, so no deadlock.

For example, deadlocks could happen between a stop_runs transaction that takes explicit runs and then jobs FOR UPDATE locks and a job background processing transaction that updates jobs, which causes Postgres to take first jobs lock and then FOR KEY SHARE runs lock.

Also fixed taking lock on joined runs table when processing jobs.