Parallel push rule deletes causing deadlock on main process

[matrixbot]

This issue has been migrated from #16053.

---

Yesterday our main process locked up after receiving a few parallel deletes to the same push rule:

{
    "exc_type": "DeadlockDetected",
    "exc_value": "deadlock detected\nDETAIL:  Process 13307 waits for ShareLock on transaction 2644686651; blocked by process 13320.\nProcess 13320 waits for ShareLock on transaction 2644691260; blocked by process 13307.\nHINT:  See server log for query details.\nCONTEXT:  while deleting tuple (38048,43) in relation \"push_rules\"\n",
    "level": "error",
    "method": "DELETE",
    "url": "/_matrix/client/v3/pushrules/global/room/!QE9hkmS...",
}

I had a quick look and there doesn't appear to be any linearization of push rule requests allowing for this to happen in rare cases. Quick fix is probably just linearize all push rule requests by user ID, the volume should be sufficiently low to not cause issues. PR for this: matrix-org/synapse#16052

I also spotted two explicit locks on the entire push rules table that may be causing issues: https://github.com/matrix-org/synapse/blob/01a45869f034265b9757992aa1a5eb7a0923351c/synapse/storage/databases/main/push_rule.py#L399-L401

I don't think this are required (anymore, at least) since the select & upsert are already part of a transaction, PR for this:matrix-org/synapse#16051

[Fizzadar]

This can be closed! The PRs were merged I just forgot to close out the original issue: matrix-org/synapse#16053