Task processor deadlock issues

[matthewelwell]

Recently we have seen instances of deadlock errors causing the task processor queue to grow beyond normal levels.

It seems as though this is caused by a flood of requests for environments within the same project.

Next steps:

1. Attempt to reproduce this by running the task processor locally
2. If (1) fails, attempt to reproduce in the staging environment
3. Investigate adding rate limits to all dashboard endpoints, scoped per token

[gagantrivedi]

Why do we get deadlocks:
whenever this function gets called for audit log on project level, it generates a SQL that looks something like this

UPDATE "environments_environment" SET "updated_at" = '2023-09-15T09:46:24.508997+00:00'::timestamptz WHERE ("environments_environment"."deleted_at" IS NULL AND "environments_environment"."project_id" = <>)

Here's the corrected text:

"Now, let's assume the project has two environments—1 and 2. In order to update the updated_at timestamp, it needs to lock both rows one by one, which is fine so far. The problem arises when another transaction attempts to do the same concurrently.

For example, the first transaction locks the first row, and at the same time, the second transaction locks the second row. Now, to complete the update, the first transaction will need a lock on the second row. However, it can't acquire the lock because the second transaction won't release the lock until it can lock the first row and commit. Voilà, we have a deadlock!"

[matthewelwell]

Following changes by Gagan we still saw a spike in Deadlocks on 8th October at ~23:15 UTC. I have added a Cloudwatch alarm. @gagantrivedi to review task processor ECS logs.

[gagantrivedi]

We only have logs till 14th October. Will have to wait for db to run into this again

[gagantrivedi]

Fixed by #3339