Parallel push rule deletes causing deadlock on main process

[Fizzadar]

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: #16052

I also spotted two explicit locks on the entire push rules table that may be causing issues:

synapse/synapse/storage/databases/main/push_rule.py

Lines 399 to 401 in 01a4586

	# Lock the table since otherwise we'll have annoying races between the
	# SELECT here and the UPSERT below.
	self.database_engine.lock_table(txn, "push_rules")

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