[ReAct] [meta] Deadlock Prevention

The problem: Currently actions can dead lock each other when two queries take postgres locks in an unfortunate manner. For example: two KEY SHARE locks due to a write on foreign keys would lock the same row of a table. SKIP LOCKED and CREATE INDEX CONCURRENTLY seem out of question as their behaviour would add caveats that we don’t want to deal with.

Strategies (Non-exclusive):

lock more earlier:

• everything that cannot be locked by other actions (no matter whether it is something updated by this action, or just something that ought to stay the same throughout), should be locked all together, right at the beginning.
• this should be a convention
• Since postgres seems to automatically lock the primary key of the other side of the relation when it is written as a foreign key somewhere else, we’ll need to pay attention that it is manually locked as well

locking order:

• to make deadlocks less likely, it might be a good idea to have an order in which collections are locked wherever possible. I.e. if there’s a choice in what to lock next, lock the collection that is first in f.e. the alphabet before the others.
• This won’t exactly be all-encompassing, since the order is also partially determined by the model, the action is supposed to change, but it still will keep some actions out of each others ways.
• A second option would be to find a metric of how often a certain foreign key is used and therefore the model should be requested first. More common ones first.

lock for sub-actions in top-action:

• Since this would be a bit hard to coordinate between the actions, the prefetch of the top-action could be made to call the prefetch of the sub-action. Maybe by pre-calculating the data the sub-action would be called with to create custom action_data.
• The execute_other action method could then be given a parameter telling the sub-action to not call prefetch because all prefetching for this action is guaranteed to already have happened in the top-action.
• We might have to turn prefetch into a class method, because otherwise we will have to create a class instance by hand every time we do this. We’re already doing that during execute_other_action, we don’t need that in the prefetch as well.

lock via automatic join queries:

• Specifying a dictionary of requested models and some of its fields and relations should automatically create a JOIN query.
• This should also specify if the models should be locked for update, having the advantage that models/rows are locked at once.
• filter data structure or client autoupdate subscription data structure might be an inspiration on how to do it

Start by tracing the action prefetch and execute_other_action paths described in the issue, then review how PostgreSQL locks are acquired for foreign-key writes. The issue lists several possible strategies but names no files or tests; done would require agreeing on a locking approach and validating it against the described concurrent-action deadlocks.